Multiple belongs_to to the same table

Question

I have two tables:

currencies and rates

currencies: id:int, code:string, name: string

rates: id:int, top_currency_id:int, bottom_currency_id:int, rate:float

And I have two active records for them:

class Rate < ActiveRecord::Base
  attr_accessible :bottom_currency, :rate, :top_currency, :top_currency_id

  belongs_to :top_currency, :class_name => 'Currency', :foreign_key => 'top_currency_id'
  belongs_to :bottom_currency, :class_name => 'Currency', :foreign_key => 'bottom_currency_id'
end


class Currency < ActiveRecord::Base
  attr_accessible :code, :name

  has_many :rates
end

So the problem is: When I'm tring to execute following code: top_currency = Currency.find_by_id(1) @test = Rate.where(:top_currency=>top_currency)

I getting following error:

Mysql2::Error: Unknown column 'rates.top_currency' in 
'where clause': SELECT `rates`.* FROM `rates`  WHERE `rates`.`top_currency` = 1

Why Rails's magic doesn't work?

Many thanks.

Answer 1

In your two belongs_to methods, change the foreign_key option to primary_key, leaving everything else as is.

belongs_to :top_currency, :class_name => 'Currency', :primary_key => 'top_currency_id'
# ...

By default, an associated object's primary key is id. However, your currency model has three primary keys, the expected id plus two extra keys: top_currency_id and bottom_currency_id. Active Record needs to know which key to look for. Tell it with the primary_key option.

The foreign_key option is needed when a foreign key is different than the association's name (belongs_to :name) plus "_id". Since your foreign key matches the association name plus "_id," you do not need to use the foreign_key option.