# NOOB - Problem with list view and foreign keys

**URL:** <https://www.ruby-forum.com/t/noob-problem-with-list-view-and-foreign-keys/88555>\
**Category:** Rails\
**Created:** [March 12, 2007, 5:02pm UTC](https://www.ruby-forum.com/t/noob-problem-with-list-view-and-foreign-keys/88555 "2007-03-12T17:02:02Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![nullset](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/3ab097/32.png) [@nullset](https://www.ruby-forum.com/u/nullset)\
**Post date:** [March 12, 2007, 5:02pm UTC](https://www.ruby-forum.com/t/noob-problem-with-list-view-and-foreign-keys/88555/1 "2007-03-12T17:02:02Z")

</div>

Hello Group,

Ruby/Rails noob coming from PHP land please excuse the simple  
question…

BACKGROUND:

I have 2 tables ‘assets’ & ‘types’ (well, I have more, but for the  
sake of simplicity…):

CREATE TABLE `assets` (  
`id` int(11) NOT NULL auto\_increment,  
`type_id` int(11) NOT NULL,  
… [snip] …  
PRIMARY KEY (`id`),  
KEY `fk_asset_type` (`type_id`),  
… [snip] …  
CONSTRAINT `fk_asset_type` FOREIGN KEY (`type_id`) REFERENCES  
`types` (`id`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=‘Asset Records’;

CREATE TABLE `types` (  
`id` int(11) NOT NULL auto\_increment,  
`typename` varchar(20) NOT NULL default ‘’,  
PRIMARY KEY (`id`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=‘Asset Type  
Definitions’;

Here are my model definitions for the two tables:

class Asset \< ActiveRecord::Base  
set\_table\_name “assets”  
set\_primary\_key “id”  
has\_one :type, :class\_name=\>“Type”, :foreign\_key=\>“type\_id”  
end

class Type \< ActiveRecord::Base  
set\_table\_name “types”  
set\_primary\_key “id”  
belongs\_to :asset, :class\_name=\>“Asset”  
end

And finally my controller for assets:

class AssetsController \< ApplicationController  
… [snip] …  
def list  
@asset\_pages, @assets = paginate :assets, :per\_page =\> 20  
@type = Type.find(:all)  
end  
… [snip] …  
end

In my ‘list’ view for assets, this works:

\<% for asset in @assets %\>

  
\<%= asset.type\_id %\>  
\<% end %\>

However, I only get the integer value returned.

ISSUE:

The following modification fails:

\<% for asset in @assets %\>

  
\<%= asset.type.typename %\>  
\<% end %\>

With Error:

Mysql::Error: Unknown column ‘types.type\_id’ in ‘where clause’:  
SELECT \* FROM types WHERE (types.type\_id = 1) LIMIT 1

I am confused as to why Rails is constructing the query for the Types  
table assuming the primary key is ‘type\_id’ when I specify the PK as  
‘id’ in the Types model…

Can anyone set me straight?

TIA.

- Brian

---

<div class="post-metadata">

**Author:** ![nullset](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/3ab097/32.png) [@nullset](https://www.ruby-forum.com/u/nullset)\
**Post date:** [March 12, 2007, 5:33pm UTC](https://www.ruby-forum.com/t/noob-problem-with-list-view-and-foreign-keys/88555/2 "2007-03-12T17:33:06Z")

</div>

> SELECT \* FROM types WHERE (types.type\_id = 1) LIMIT 1

This is looking for a table called “types” with a column “type\_id”.

I think the offending line is:

> has\_one :type, :class\_name=\>“Type”, :foreign\_key=\>“type\_id”

You just need:  
has\_one :type

> end

You can simplify these by taking advantage of the rails naming  
convention, e.g.

class Asset \< ActiveRecord::Base  
has\_one :type  
end

---

<div class="post-metadata">

**Author:** ![nullset](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/3ab097/32.png) [@nullset](https://www.ruby-forum.com/u/nullset)\
**Post date:** [March 12, 2007, 6:01pm UTC](https://www.ruby-forum.com/t/noob-problem-with-list-view-and-foreign-keys/88555/3 "2007-03-12T18:01:10Z")

</div>

Thanks for the response. I implemented the suggested simplifications  
& now get:

Mysql::Error: Unknown column ‘types.asset\_id’ in ‘where clause’:  
SELECT \* FROM types WHERE (types.asset\_id = 1) LIMIT 1

Now, I am confused as to why rails is looking for ‘types.asset\_id’.

Very strange, maybe I need to go back to the drawing board…

- Brian