Joining the same model twice in a clean way, and making the code reusable

I have a model Painting which has a Paintingtitle in each language and a Paintingdescription in each language:

class Painting < ActiveRecord::Base
  has_many :paintingtitles, :dependent => :destroy
  has_many :paintingdescriptions, :dependent => :destroy  
end

class Paintingtitle < ActiveRecord::Base
  belongs_to :painting
  belongs_to :language
end

class Paintingdescription < ActiveRecord::Base
  belongs_to :painting
  belongs_to :language
end

class Language < ActiveRecord::Base
  has_many :paintingtitles, :dependent => :nullify
  has_many :paintingdescriptions, :dependent => :nullify
  has_many :paintings, :through => :paintingtitles
end

As you might notice, I reference the Language model from my Painting model via both the Paintingtitle model and Paintingdescription model. This works for me when getting a list of paintings with their title and description in a specific language:

cond = {"paintingdescription_languages.code" => language_code, "paintingtitle_languages.code" => language_code}
cond['paintings.publish'] = 1 unless admin

paginate(
:all,
:select => ["paintings.id, paintings.publish, paintings.photo_file_name, paintingtitles.title, paintingdescriptions.description"],
:joins => "
  INNER JOIN paintingdescriptions ON (paintings.id = paintingdescriptions.painting_id) 
  INNER JOIN paintingtitles ON (paintings.id = paintingtitles.painting_id) 
  INNER JOIN languages paintingdescription_languages ON (paintingdescription_languages.id = paintingdescriptions.language_id) 
  INNER JOIN languages paintingtitle_languages ON (paintingtitle_languages.id = paintingtitles.language_id) ",
:conditions => cond,
:page => page, 
:per_page => APP_CONFIG['per_page'],
:order => "id DESC"
)

This is pretty much the principle as explained here: http://www.sqlteam.com/article/multiple-joins-to-the-same-table-in-a-query

Now I wonder if this is a correct way of doing this. I need to fetch paintings with their title and description in different functions, but I don't want to specify this long join statement each time. Is there a cleaner way, for instance making use of the has_many through? e.g.

  has_many :paintingdescription_languages, :through => :paintingdescriptions, :source => :language
  has_many :paintingtitle_languages, :through => :paintingtitles, :source => :language

But if I implement above 2 lines together with the following ones, then only paintingtitles are filtered by language, and not the paintingdescriptions:

   cond = {"languages.code" => language_code}
   cond['paintings.publish'] = 1 unless admin

   paginate(
   :all,
   :select => ["paintings.id, paintings.publish, paintings.photo_file_name, paintingtitles.title, paintingdescriptions.description"],
   :joins => [:paintingdescription_languages, :paintingtitle_languages],
   :conditions => cond,
   :page => page, 
   :per_page => APP_CONFIG['per_page'],
   :order => "id DESC"
   )

ansaurus

tags:

views:

answers:

Joining the same model twice in a clean way, and making the code reusable

related questions