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.

Ailment

The error states that top_currency isn't a column in the rates table, are you sure you migrated your changes to the environment you're using? – Corkboard

@Corkboard I have top_currency_id column, and I supposed that Rails should look for top_currency_id column insted of top_currency. – Ailment

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.

Inescutcheon

No, unfortunately I have only one primary key. – Ailment

Answer 2

From what I see, your code should work in theory. But I do think you are being a bit redundant.

It should be enough to just do this:

class Rate < ActiveRecord::Base
  belongs_to :top_currency, class_name: 'Currency'
  belongs_to :bottom_currency, class_name: 'Currency'
end

Rails will infer that the foreign key for top_currency is top_currency_id, and bottom_currency_id for bottom_currency.

Abnegate

Are you sure that the class name shouldn't be a symbol or string? – Cheesewood

Answer 3

I don't think you can query on the relationship like that. To use your example:

top_currency = Currency.find_by_id(1)
@test = Rate.where(:top_currency=>top_currency)

You'd have to change it to this:

top_currency = Currency.find_by_id(1)
@test = Rate.where(:top_currency_id => top_currency.id)

But it might just be easier to do this:

top_currency = Currency.find_by_id(1)
@test = top_currency.rates

Menon

Yes, @test = Rate.where(:top_currency_id => top_currency.id) even @test = Rate.where(:top_currency_id => top_currency) works well. But I want to make Rate.where(:top_currency=>top_currency) works – Ailment

Just curious as to why are you so committed on having Rate.where(:top_currency=>top_currency) when the association you set up gives you the method top_currency.ratesto do the same thing? – Resh

@MollyStruve in fact I need to execute following request: Rate.where(:top_currency=>top_currency, :bottom_currency=>bottom_curency) – Ailment

To my knowledge, you can't do that. Add _id to each of the symbols and it'll work. I see what you're going for, but it's not possible in a simple query. You're looking to do a join, which is much more expensive than simply checking for the id of the related records. – Menon