多表连接的轨 [英] Multiple table joins in rails

问题描述

我怎样写下面MySQL查询到Rails的ActiveRecord

How do I write below mysql query 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

我知道如何写联接两个表，不是很有信心如何使用连接的3个表。

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

推荐答案

要重写你有你的问题的SQL查询，我想应该是这样（虽然我有一个很难完全可视化的模型关系，所以这是一个有点猜测的）：

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.

随着越来越多供参考：

如果你要加入多个关联到相同型号你可以简单地列出他们：

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

您也可以连锁的ActiveRecord查询接口调用，使得：

You can also chain ActiveRecord Query Interface calls such that:

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

如果一切都失败了，你可以随时直接传递一个SQL片段插入加入方法作为垫脚石，从您的工作查询得到的东西更ARQI为中心

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

这篇关于多表连接的轨的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！