Rails - Oct 2005 - question about model relationships

i''ve got the following tables setup (simplified)

items
-------------
id
barcode
...

locations
-------------
id
name

item_locations
-------------
id
barcode
location_id
num_items

models are as follows:

basically, items contains

class Item < ActiveRecord::Base
  has_one :item_location, :foreign_key => :barcode
end

class Location < ActiveRecord::Base
  has_many :item_locations

class ItemLocation < ActiveRecord::Base
  belongs_to :item, :foreign_key => :barcode
  belongs_to :location

i''ve opted for this setup as opposed to using a habtm because it makes
more sense for item_locations to be a model.

in my controller (again, simplified)

class ItemController < ActiveController::Base
  def view
    @item = Item.find(@params[:id])
  end
end

when i go to /item/view/22 i see the following in the development log:

Processing ItemController#view (for 127.0.0.1 at Fri Oct 14 12:08:10 EDT 2005)
  Parameters: {"action"=>"view",
"id"=>"22", "controller"=>"item"}
  Item Load (0.000534)   SELECT * FROM items WHERE itemss.id =
''22'' LIMIT 1
  ...
  ItemLocation Load (0.000473)   SELECT * FROM item_locations WHERE
item_locations.barcode = 22 LIMIT 1
Completed in 0.02343 (42 reqs/sec) | Rendering: 0.01992 (85%) | DB:
0.02906 (124%) [http://localhost/myapp/item/view/22]

everything works well, i get an item back in my view...there is only
one problem.

notice the second query...its using the item id value where it need to
use the barcode.

i''m probably misunderstanding the purpose of the :foreign_key option
within the relationships...any idea on how i can fix this?

thanks.

Chris,

Active Record associates the belongs_to and has_one relationships
based on the primary key of the parent table, which in the default
case is the id column. In your example:

class Item < ActiveRecord::Base
  has_one :item_location, :foreign_key => :barcode
end

Assume an Item object with the following properties:
id: 1
barcode: 1234

The ''foreign_key => :barcode'' option tells active record to
query the
item_locations table with the condition ''where item_locations.barcode
= 1'', instead of the default ''where item_locations.item_id =
1'' when
resolving the association from the Item side.

In this model:
class ItemLocation < ActiveRecord::Base
  belongs_to :item, :foreign_key => :barcode
end

Assume an ItemLocation object with the following properties:
id: 1
barcode: 1234
item_id: 2

The ''foreign_key => :barcode'' option tells active record to
query the
items table with the condition ''where items.id = 1234'' instead
of
using the default ''where items.id = 2'' when resolving the
association
from the ItemLocation side.

You''ll notice that in both cases that the primary key, id column, of
the parent (Item) object is used in resolving the association.  Only
the column used in the child object (ItemLocation) changes with the
foreign_key option.

Is there any particular reason that you need to associate based on the
barcode of the item? You could solve this problem with the following:

items
-------------
id
barcode

locations
-------------
id
name

item_locations
-------------
id
item_id
location_id
num_items

class Item < ActiveRecord::Base
 has_one :item_location
end

class Location < ActiveRecord::Base
 has_many :item_locations
end

class ItemLocation < ActiveRecord::Base
 belongs_to :item
 belongs_to :location
end

If you then needed the barcode when dealing with ItemLocation objects
you could simply navigate to the Item object from the ItemLocation
side of the association.


Cody