Rails 中的多个表连接

2021-12-23 00:00:00 ruby-on-rails mysql activerecord

如何将下面的 mysql 查询写入 rails activerecord

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

我知道如何在两个表上编写连接；但是，我对如何在 3 个表上使用连接不是很有信心.

I know how to write joins on two tables; however, I'm 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')

...这样 joins 方法处理两个连接以及 WHERE 子句，最后是 group调用.

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

嵌套关联，多级:

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 片段进入 joins 方法 作为从你的工作查询到更以 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

相关文章