mysql - 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