Ruby-on-rails LEFT OUTER 加入 Rails 3
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3245201/
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
LEFT OUTER joins in Rails 3
提问by Neil Middleton
I have the following code:
我有以下代码：
@posts = Post.joins(:user).joins(:blog).select
which is meant to find all posts and return them and the associated users and blogs.
However, users are optional which means that the INNER JOINthat :joinsgenerates is not returning lots of records.
这意味着查找所有帖子并返回它们以及相关的用户和博客。但是，用户是可选的，这意味着INNER JOIN该:joins生成没有返回大量记录。
How do I use this to generate a LEFT OUTER JOINinstead?
我如何使用它来生成一个LEFT OUTER JOIN？
回答by Neil Middleton
@posts = Post.joins("LEFT OUTER JOIN users ON users.id = posts.user_id").
joins(:blog).select
回答by WuTangTan
You can do with this with includesas documented in the Rails guide:
您可以includes按照 Rails 指南中的说明进行操作：
Post.includes(:comments).where(comments: {visible: true})
Results in:
结果是：
SELECT "posts"."id" AS t0_r0, ...
"comments"."updated_at" AS t1_r5
FROM "posts" LEFT OUTER JOIN "comments" ON "comments"."post_id" = "posts"."id"
WHERE (comments.visible = 1)
回答by plainjimbo
I'm a big fan of the squeel gem:
我是squeel gem 的忠实粉丝：
Post.joins{user.outer}.joins{blog}
It supports both innerand outerjoins, as well as the ability to specify a class/type for polymorphic belongs_to relationships.
它支持inner和outer连接，以及为多态的belongs_to 关系指定类/类型的能力。
回答by Ricardo
Use eager_load:
使用eager_load：
@posts = Post.eager_load(:user)
回答by DBA
By default when you pass ActiveRecord::Base#joinsa named association, it will perform an INNER JOIN. You'll have to pass a string representing your LEFT OUTER JOIN.
默认情况下，当您传递ActiveRecord::Base#joins命名关联时，它将执行 INNER JOIN。您必须传递一个表示左外连接的字符串。
From the documentation:
从文档：
:joins- Either an SQL fragment for additional joins like "LEFT JOIN comments ON comments.post_id = id" (rarely needed), named associations in the same form used for the:includeoption, which will perform an INNER JOIN on the associated table(s), or an array containing a mixture of both strings and named associations.If the value is a string, then the records will be returned read-only since they will have attributes that do not correspond to the table‘s columns. Pass
:readonly => falseto override.
:joins- 用于附加连接的 SQL 片段，如“LEFT JOIN comments ON comments.post_id = id”（很少需要）、用于:include选项的相同形式的命名关联，它将在关联的表上执行 INNER JOIN，或包含两个字符串的混合数组和命名的协会。如果值为字符串，则记录将以只读方式返回，因为它们将具有与表的列不对应的属性。通过
:readonly => false覆盖。
回答by Ahmad Hussain
There is a left_outer_joinsmethod in activerecord. You can use it like this:
activerecord 中有一个left_outer_joins方法。你可以这样使用它：
@posts = Post.left_outer_joins(:user).joins(:blog).select
回答by Dex
Good news, Rails 5 now supports LEFT OUTER JOIN. Your query would now look like:
好消息，Rails 5 现在支持LEFT OUTER JOIN. 您的查询现在看起来像：
@posts = Post.left_outer_joins(:user, :blog)
回答by Jigar Bhatt
class User < ActiveRecord::Base
has_many :friends, :foreign_key=>"u_from",:class_name=>"Friend"
end
class Friend < ActiveRecord::Base
belongs_to :user
end
friends = user.friends.where(:u_req_status=>2).joins("LEFT OUTER JOIN users ON users.u_id = friends.u_to").select("friend_id,u_from,u_to,u_first_name,u_last_name,u_email,u_fbid,u_twtid,u_picture_url,u_quote")
