Multiple table joins in rails

Question

How do I write the mysql query below into rails activerecord

select     A.*,     B.*  from     raga_contest_applicants_songs AS A      join         raga_contest_applicants AS B          ON B.contest_applicant_id = A.contest_applicant_id      join         raga_contest_rounds AS C          ON C.contest_cat_id = B.contest_cat_id  WHERE     C.contest_cat_id = contest_cat_id  GROUP BY     C.contest_cat_id     

I know how to write joins on two tables; however, I'm not very confident on how to use join on 3 tables.

Answer 1

To rewrite the SQL query you've got in your question, I think it should be like the following (though I'm having a hard time fully visualizing your model relationships, so this is a bit of guesswork):

RagaContextApplicantsSong.   joins(:raga_contest_applicants => [:raga_content_rounds], :contest_cat).   group('raga_contest_rounds.contest_cat_id') 

...such that the joins method takes care of both of the two joins as well as the WHERE clause, followed finally by the group call.

As more for reference:

If you're joining multiple associations to the same model you can simply list them:

Post.joins(:category, :comments) Returns all posts that have a category and at least one comment 

If you're joining nested tables you can list them as in a hash:

Post.joins(:comments => :guest) Returns all comments made by a guest 

Nested associations, multiple level:

Category.joins(:posts => [{:comments => :guest}, :tags]) Returns all posts with their comments where the post has at least one comment made by a guest 

You can also chain ActiveRecord Query Interface calls such that:

Post.joins(:category, :comments) ...produces the same SQL as... Post.joins(:category).joins(:comments) 

If all else fails you can always pass a SQL fragment directly into the joins method as a stepping stone to getting from your working query to something more ARQI-centric

   Client.joins('LEFT OUTER JOIN addresses ON addresses.client_id = clients.id') => SELECT clients.* FROM clients LEFT OUTER JOIN addresses ON addresses.client_id = clients.id