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