Filtering join table in has_many :through in RoR

Question

I have the following models in which I join the Language and Products table via the Translation table using the Rails has_many :through paradigm:

class Language < ActiveRecord::Base
  has_many :translations
  has_many :products, :through => :translations
end

class Translation < ActiveRecord::Base
  belongs_to :product
  belongs_to :language
end

class Product < ActiveRecord::Basehas_many :translations
  has_many :translations
  has_many :languages, :through => :translations
end

I want to find the English Language Translation for a particular Product.

I can list the associated Languages and Translations:

prod = Product.find(4)
en = Language.find(:first, :conditions => { :lang_code => 'en' })

puts prod.translations
puts prod.languages

This prints:

#<Translation:0x11022a4>
#<Translation:0x1102114>
#<Language:0x602070>
#<Language:0x602020>

(There is an English and French translation for this product.)

How can I get the Translation for prod corresponding to the en Language?

If that doesn't make sense, here is the equivalent SQL:

SELECT t.* FROM products p, translations t, languages l WHERE l.id = t.language_id AND p.id = t.product_id AND l.lang_code = 'en';

Answer 1

You'd need something along this:

product = Product.find(4)
translation = product.translations.find(:first,:joins=>:languages, :conditions=>{:language=>{:lang_code=>'en'}})

The code will produce a join of Translations with Languages and filter accordingly your lang_code.

If the brackets confuse you a bit (I know sometimes they do) you could also do something like this:

translation = product.translations.find(:first,:joins=>:languages, :conditions=>["languages.lang_code like ?",'en'])

That last bit should produce the same SQL query, joining Translations with Language and then filtering by it's lang_code.

Answer 2

Yaraher's answer works, although I found a simpler way to accomplish the same thing:

t = Translation.find(:first, 
                     :conditions => { :product_id => prod.id, 
                                      :language_id => lang.id })