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