Ruby-on-rails 如何使用 Rails ActiveRecord 执行 LEFT OUTER JOIN？

How can I do a LEFT OUTER JOIN using Rails ActiveRecord?

提问by zono

I don't have any ideas. Could you give me any clues (like reference sites). Any help will be appreciated.

我没有任何想法。你能给我任何线索吗（比如参考网站）。任何帮助将不胜感激。

Model1: GROUP(id, name)
Model2: USER_GROUP_CMB(id, user_id, group_id)

Expected SQL statement:

预期的 SQL 语句：

SELECT * 
FROM groups AS g LEFT OUTER JOIN user_group_cmbs AS cmb 
            ON g.id = cmb.group_id
WHERE cmb.user_id = 1

I tried to set up associations below but I dont know what to do after this.

我试图在下面建立关联，但我不知道在这之后该怎么做。

class Group < ActiveRecord::Base
  has_many :user_group_cmb
end

class UserGroupCmb < ActiveRecord::Base
  has_many :group
end

Rails Version: 3.1.1

Rails 版本：3.1.1

回答by Ryan Bigg

I believe an includeswill use a LEFT OUTER JOINquery if you do a condition on the table that the includesassociation uses:

我相信如果您对关联使用的表执行条件，includes则会使用LEFT OUTER JOIN查询includes：

Group.includes(:user_group_cmb).where(user_group_cmbs: { user_id: 1 })

回答by Diego Plentz

Rails 5+ allows you to do the following:

Rails 5+ 允许您执行以下操作：

Group.left_outer_joins(:user_group_cmb)

http://api.rubyonrails.org/classes/ActiveRecord/QueryMethods.html#method-i-left_joins

http://api.rubyonrails.org/classes/ActiveRecord/QueryMethods.html#method-i-left_joins

回答by knoopx

You can do this in rails 3.x regardless if you are referencing the table or not in a where clause:

无论您是否在 where 子句中引用该表，您都可以在 rails 3.x 中执行此操作：

Group.eager_load(:user_group_cmb)

...and it will perform a left outter join

...它将执行左外连接

回答by knoopx

It might be quite important to mention that using includeshas possibly unwanted side-effects.

值得一提的是，使用includes可能会产生不需要的副作用。

if the filtered association is subsequently scoped, all of the original filtering disappears

As it turns out, by scoping on the filtered association we've lost any filtering-as-side-effect that we attained from includes. And it's not because of how we searched, either.

如果过滤后的关联随后被限定，则所有原始过滤都将消失

事实证明，通过限定过滤关联，我们已经失去了从包含中获得的任何过滤作为副作用。这也不是因为我们的搜索方式。

Make sure to read the complete article and alternatively there's a gem for that.

一定要阅读完整的文章，或者有一个宝石。

回答by hammady

Use a custom joins statement:

使用自定义连接语句：

Group.joins("left outer join user_group_cmbs as cmb on groups.id = cmb.group_id")
  .where("cmb.user_id = ?", 1)

回答by KARASZI István

The problem with the accepted answer is that it will actually do a LEFT INNER JOINtechnically because it won't display entries where the user_group_cmbs. user_idis null (which would be the reason to do a LEFT OUTER JOIN).

接受的答案的问题在于它实际上会在LEFT INNER JOIN技术上执行 a ，因为它不会显示user_group_cmbs. user_id为 null 的条目（这将是执行 a 的原因LEFT OUTER JOIN）。

A working solution is to use #references:

一个可行的解决方案是使用#references：

Group.includes(:user_group_cmb).references(:user_group_cmb)

Or the even more convenient #eager_load:

或者更方便的#eager_load：

Group.eager_load(:user_group_cmb)

Read the more detailed explanation here.

在这里阅读更详细的解释。

回答by panupan

Use has_and_belongs_to_manyif you just need to link users to groups. Use has_many :throughif you need to store additional membership information.

如果您只需要将用户链接到组，请使用has_and_belongs_to_many。如果您需要存储其他会员信息，请使用has_many :through。

Example:

例子：

class User < ActiveRecord::Base
  has_and_belongs_to_many :groups
end

class Group < ActiveRecord::Base
  has_and_belongs_to_many :users      
end

回答by Raja

Couldn't comment to an earlier answer because well I don't have 50 reputation. But here's how it worked for me when I ran into below error

无法对较早的答案发表评论，因为我没有 50 声望。但是当我遇到以下错误时，它是如何对我来说有效的

includes-referenced relation raises a StatementInvalid exception, complaining it misses a FROM-clause entry. I need to add an explicit call to join, which defaults to being inner.

包含引用的关系引发 StatementInvalid 异常，抱怨它错过了一个 FROM 子句条目。我需要添加一个显式的 join 调用，默认为内部调用。

I had to use .references and mention the table name or for it get added to the FROM-clause

我不得不使用 .references 并提及表名，否则将其添加到 FROM 子句中