MySQL Rails 中的多个表连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16131201/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Multiple table joins in rails
提问by pramodtech
How do I write the mysql query below into rails activerecord
如何将下面的 mysql 查询写入 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.
我知道如何在两个表上编写连接；但是，我对如何在 3 个表上使用连接不是很有信心。
回答by jefflunt
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):
要重写您在问题中获得的 SQL 查询，我认为它应该如下所示（尽管我很难完全可视化您的模型关系，所以这有点猜测）：
RagaContextApplicantsSong.
joins(:raga_contest_applicants => [:raga_content_rounds], :contest_cat).
group('raga_contest_rounds.contest_cat_id')
...such that the joinsmethod takes care of both of the two joins as well as the WHEREclause, followed finally by the groupcall.
...使得该joins方法处理两个连接以及WHERE子句，最后是group调用。
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:
您还可以链接 ActiveRecord 查询接口调用，以便：
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 joinsmethodas a stepping stone to getting from your working query to something more ARQI-centric
如果所有其他方法都失败了，您始终可以将 SQL 片段直接传递到joins方法中，作为从工作查询到更以 ARQI 为中心的查询的垫脚石
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
