Rails Inner Join 不起作用，但 SQL 看起来正确

发布于 2024-10-30 18:52:58 字数 318 浏览 2 评论 0原文

所以我有 2 个由 ID 连接的表。我在 Rails 控制台中输入：

Programmer.all(:joins=>:assignment)

生成的 sql 是：

SELECT `programmers`.* FROM `programmers` INNER JOIN `assignments` ON `assignments`.`programmer_id` = `programmers`.`id`

生成的输出与 Programmer.all 相同。为什么它不包括作业数据？

原文

So I have 2 tables that are joined by an ID. I'm in rails console and I type:

Programmer.all(:joins=>:assignment)

the sql that is generated is:

SELECT `programmers`.* FROM `programmers` INNER JOIN `assignments` ON `assignments`.`programmer_id` = `programmers`.`id`

The output that is generated is the same as Programmer.all. Why doesn't it include the assignments data?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

孤城病女 2024-11-06 18:52:58

我相信我过度分析了你的问题。如果您只是想加入程序员的任何可用作业，那么您正在寻找：

Programmer.all(:include => :assignment)

Rails 的设计使得 :joins 用于执行诸如排序和抓取某些记录之类的操作，但仍将查询结果保留为最小大小 - 这意味着 :joins 实际上从未将连接表的结果包含在结果中。

现在，这是我之前的答案，假设您想要执行 INNER JOIN 来仅获取分配任务的程序员，但您也需要该数据。在这种情况下，您有两个选择：

#1 - 使用 :select

Programmer.all(:select => '*', :joins => :assignment)

这会将 SQL 更改为：

SELECT * FROM `programmers` INNER JOIN `assignments` ON `assignments`.`programmer_id` = `programmers`.`id`

好处：您可以获得所需的查询，并且所有数据都在某处，至少。

缺点：赋值直接分配给Programmer对象，而不是分配给Programmer.assignment的正确位置。

#2 - 使用 :joins 和 :includes 的组合，

Programmer.all(:joins => :assignment, :include => :assignment)

生成 SQL：

SELECT `programmers`.* FROM `programmers` INNER JOIN `assignments` ON `assignments`.`id` = `programmers`.`assignment_id`
SELECT `assignments`.* FROM `assignments` WHERE (`assignments`.`id` IN  (?) )

优点：现在所有数据都位于正确的位置。您可以参考programmer.assignment而无需再次查询。

缺点：您在很多情况下都会运行额外的查询。我相当确定 Rails 会在需要时尝试对此进行优化，即使没有，也不会给您带来太多开销。

I believe I majorly overanalyzed your question. If you just want to join any available assignments to programmers, you're looking for:

Programmer.all(:include => :assignment)

Rails is designed so that :joins is used to perform things like sorting and grabbing certain records but still keep the query result to a minimum size -- meaning, :joins never actually includes the results from the joined table in the result.

Now here's my previous answer that assumes you want to perform an INNER JOIN to get only the programmers with assignments, but you also want that data. In that case, you have two options:

#1 - Use :select

Programmer.all(:select => '*', :joins => :assignment)

That will change the SQL to:

SELECT * FROM `programmers` INNER JOIN `assignments` ON `assignments`.`programmer_id` = `programmers`.`id`

Upside: You get the query you want and all the data is somewhere, at least.

Downside: assignments is assigned directly to the Programmer object and not to the proper place at Programmer.assignment.

#2 - Use a combination of :joins and :includes

Programmer.all(:joins => :assignment, :include => :assignment)

Which produces the SQL:

SELECT `programmers`.* FROM `programmers` INNER JOIN `assignments` ON `assignments`.`id` = `programmers`.`assignment_id`
SELECT `assignments`.* FROM `assignments` WHERE (`assignments`.`id` IN  (?) )

Upside: All your data is in the right place now. You can refer to programmer.assignment without another query.

Downside: You are running that extra query in a lot of instances. I am fairly sure that Rails tries to optimize this when it needs to, though, and if not, it shouldn't cause you too much overhead.

回复收藏 0 原文