NOOB - Problem with list view and foreign keys

rubyonrails-talk
1

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 %>     <tr>       <td><%= asset.type_id %></td>   <% end %>

However, I only get the integer value returned.

ISSUE:

The following modification fails:

  <% for asset in @assets %>     <tr>       <td><%= asset.type.typename %></td>   <% 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

2

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

  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

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

class Asset < ActiveRecord::Base has_one :type end

3

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