SQL Rails 4 中的左外连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24358805/
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 JOIN in Rails 4
提问by Khanetor
I have 3 models:
我有 3 个模型：
class Student < ActiveRecord::Base
has_many :student_enrollments, dependent: :destroy
has_many :courses, through: :student_enrollments
end
class Course < ActiveRecord::Base
has_many :student_enrollments, dependent: :destroy
has_many :students, through: :student_enrollments
end
class StudentEnrollment < ActiveRecord::Base
belongs_to :student
belongs_to :course
end
I wish to query for a list of courses in the Courses table, that do not exist in the StudentEnrollments table that are associated with a certain student.
我希望在 Courses 表中查询与某个学生相关联的 StudentEnrollments 表中不存在的课程列表。
I found that perhaps Left Join is the way to go, but it seems that joins() in rails only accept a table as argument. The SQL query that I think would do what I want is:
我发现也许 Left Join 是要走的路，但似乎 rails 中的 joins() 只接受一个表作为参数。我认为可以执行我想要的 SQL 查询是：
SELECT *
FROM Courses c LEFT JOIN StudentEnrollment se ON c.id = se.course_id
WHERE se.id IS NULL AND se.student_id = <SOME_STUDENT_ID_VALUE> and c.active = true
How do I execute this query the Rails 4 way?
如何以 Rails 4 方式执行此查询？
Any input is appreciated.
任何输入表示赞赏。
回答by Taryn East
You can pass a string that is the join-sql too. eg joins("LEFT JOIN StudentEnrollment se ON c.id = se.course_id")
您也可以传递一个作为 join-sql 的字符串。例如joins("LEFT JOIN StudentEnrollment se ON c.id = se.course_id")
Though I'd use rails-standard table naming for clarity:
虽然为了清楚起见，我会使用 rails 标准的表命名：
joins("LEFT JOIN student_enrollments ON courses.id = student_enrollments.course_id")
回答by Blaskovicz
If anyone came here looking for a generic way to do a left outer join in Rails 5, you can use the #left_outer_joinsfunction.
如果有人来这里寻找在 Rails 5 中进行左外连接的通用方法，您可以使用该#left_outer_joins函数。
Multi-join example:
多连接示例：
Ruby:
红宝石：
Source.
select('sources.id', 'count(metrics.id)').
left_outer_joins(:metrics).
joins(:port).
where('ports.auto_delete = ?', true).
group('sources.id').
having('count(metrics.id) = 0').
all
SQL:
查询语句：
SELECT sources.id, count(metrics.id)
FROM "sources"
INNER JOIN "ports" ON "ports"."id" = "sources"."port_id"
LEFT OUTER JOIN "metrics" ON "metrics"."source_id" = "sources"."id"
WHERE (ports.auto_delete = 't')
GROUP BY sources.id
HAVING (count(metrics.id) = 0)
ORDER BY "sources"."id" ASC
回答by superuseroi
There is actually a "Rails Way" to do this.
实际上有一种“Rails 方式”可以做到这一点。
You could use Arel, which is what Rails uses to construct queries for ActiveRecrods
你可以使用Arel，这是 Rails 用来构造 ActiveRecrods 查询的
I would wrap it in method so that you can call it nicely and pass in whatever argument you would like, something like:
我会将它包装在方法中，以便您可以很好地调用它并传入您想要的任何参数，例如：
class Course < ActiveRecord::Base
....
def left_join_student_enrollments(some_user)
courses = Course.arel_table
student_entrollments = StudentEnrollment.arel_table
enrollments = courses.join(student_enrollments, Arel::Nodes::OuterJoin).
on(courses[:id].eq(student_enrollments[:course_id])).
join_sources
joins(enrollments).where(
student_enrollments: {student_id: some_user.id, id: nil},
active: true
)
end
....
end
There is also the quick (and slightly dirty) way that many use
还有许多人使用的快速（而且有点脏）的方式
Course.eager_load(:students).where(
student_enrollments: {student_id: some_user.id, id: nil},
active: true
)
eager_load works great, it just has the "side effect" of loding models in memory that you might not need (like in your case)
Please see Rails ActiveRecord::QueryMethods .eager_load
It does exactly what you are asking in a neat way.
eager_load 工作得很好，它只是在内存中加载模型的“副作用”，你可能不需要（就像你的情况一样）
请参阅 Rails ActiveRecord::QueryMethods .eager_load
它以一种巧妙的方式完成了你的要求。
回答by mackshkatz
Combining includesand whereresults in ActiveRecord performing a LEFT OUTER JOIN behind the scenes (without the where this would generate the normal set of two queries).
组合includes并where导致 ActiveRecord 在幕后执行 LEFT OUTER JOIN（没有这将生成正常的两个查询集）。
So you could do something like:
所以你可以这样做：
Course.includes(:student_enrollments).where(student_enrollments: { course_id: nil })
文档在这里：http: //guides.rubyonrails.org/active_record_querying.html#specifying-conditions-on-eager-loaded-associations
回答by Jonathon Gardner
Adding to the answer above, to use includes, if you want an OUTER JOIN without referencing the table in the where (like id being nil) or the reference is in a string you can use references. That would look like this:
添加到上面的答案中includes，如果您想要一个 OUTER JOIN 而不引用 where 中的表（例如 id 为 nil）或引用位于字符串中，则可以使用references. 看起来像这样：
Course.includes(:student_enrollments).references(:student_enrollments)
or
或者
Course.includes(:student_enrollments).references(:student_enrollments).where('student_enrollments.id = ?', nil)
http://api.rubyonrails.org/classes/ActiveRecord/QueryMethods.html#method-i-references
http://api.rubyonrails.org/classes/ActiveRecord/QueryMethods.html#method-i-references
回答by Joe Kennedy
You'd execute the query as:
您将执行查询为：
Course.joins('LEFT JOIN student_enrollment on courses.id = student_enrollment.course_id')
.where(active: true, student_enrollments: { student_id: SOME_VALUE, id: nil })
回答by khiav reoy
You could use left_joinsgem, which backports left_joinsmethod from Rails 5 for Rails 4 and 3.
您可以使用left_joinsgem，它left_joins从 Rails 5向后移植Rails 4 和 3 的方法。
Course.left_joins(:student_enrollments)
.where('student_enrollments.id' => nil)
回答by jDmendiola
I know that this is an old question and an old thread but in Rails 5, you could simply do
我知道这是一个老问题和一个老线程，但在 Rails 5 中，你可以简单地做
Course.left_outer_joins(:student_enrollments)
回答by Diego
I've been struggling with this kind of problem for quite some while, and decided to do something to solve it once and for all. I published a Gist that addresses this issue: https://gist.github.com/nerde/b867cd87d580e97549f2
我一直在为这种问题苦苦挣扎，并决定做一些事情来一劳永逸地解决它。我发布了一个解决这个问题的要点：https: //gist.github.com/nerde/b867cd87d580e97549f2
I created a little AR hack that uses Arel Table to dynamically build the left joins for you, without having to write raw SQL in your code:
我创建了一个小 AR hack，它使用 Arel Table 为您动态构建左连接，而无需在代码中编写原始 SQL：
class ActiveRecord::Base
# Does a left join through an association. Usage:
#
# Book.left_join(:category)
# # SELECT "books".* FROM "books"
# # LEFT OUTER JOIN "categories"
# # ON "books"."category_id" = "categories"."id"
#
# It also works through association's associations, like `joins` does:
#
# Book.left_join(category: :master_category)
def self.left_join(*columns)
_do_left_join columns.compact.flatten
end
private
def self._do_left_join(column, this = self) # :nodoc:
collection = self
if column.is_a? Array
column.each do |col|
collection = collection._do_left_join(col, this)
end
elsif column.is_a? Hash
column.each do |key, value|
assoc = this.reflect_on_association(key)
raise "#{this} has no association: #{key}." unless assoc
collection = collection._left_join(assoc)
collection = collection._do_left_join value, assoc.klass
end
else
assoc = this.reflect_on_association(column)
raise "#{this} has no association: #{column}." unless assoc
collection = collection._left_join(assoc)
end
collection
end
def self._left_join(assoc) # :nodoc:
source = assoc.active_record.arel_table
pk = assoc.association_primary_key.to_sym
joins source.join(assoc.klass.arel_table,
Arel::Nodes::OuterJoin).on(source[assoc.foreign_key].eq(
assoc.klass.arel_table[pk])).join_sources
end
end
Hope it helps.
希望能帮助到你。
回答by textral
See below my original post to this question.
请参阅下面我对此问题的原始帖子。
Since then, I have implemented my own .left_joins()for ActiveRecord v4.0.x (sorry, my app is frozen at this version so I've had no need to port it to other versions):
从那以后，我.left_joins()为 ActiveRecord v4.0.x实现了我自己的（抱歉，我的应用程序被冻结在这个版本，所以我没有必要将它移植到其他版本）：
In file app/models/concerns/active_record_extensions.rb, put the following:
在 file 中app/models/concerns/active_record_extensions.rb，输入以下内容：
module ActiveRecordBaseExtensions
extend ActiveSupport::Concern
def left_joins(*args)
self.class.left_joins(args)
end
module ClassMethods
def left_joins(*args)
all.left_joins(args)
end
end
end
module ActiveRecordRelationExtensions
extend ActiveSupport::Concern
# a #left_joins implementation for Rails 4.0 (WARNING: this uses Rails 4.0 internals
# and so probably only works for Rails 4.0; it'll probably need to be modified if
# upgrading to a new Rails version, and will be obsolete in Rails 5 since it has its
# own #left_joins implementation)
def left_joins(*args)
eager_load(args).construct_relation_for_association_calculations
end
end
ActiveRecord::Base.send(:include, ActiveRecordBaseExtensions)
ActiveRecord::Relation.send(:include, ActiveRecordRelationExtensions)
Now I can use .left_joins()everywhere I'd normally use .joins().
现在我可以.left_joins()在我通常使用的任何地方使用.joins().
----------------- ORIGINAL POST BELOW -----------------
----------------- 以下原始帖子-----------------
If you want OUTER JOINs without all the extra eagerly loaded ActiveRecord objects, use .pluck(:id)after .eager_load()to abort the eager load while preserving the OUTER JOIN. Using .pluck(:id)thwarts eager loading because the column name aliases (items.location AS t1_r9, for example) disappear from the generated query when used (these independently named fields are used to instantiate all the eagerly loaded ActiveRecord objects).
如果您希望 OUTER JOIN 没有所有额外急切加载的 ActiveRecord 对象，请使用.pluck(:id)after.eager_load()中止急切加载，同时保留 OUTER JOIN。使用会.pluck(:id)阻碍预先加载，因为列名别名（items.location AS t1_r9例如）在使用时会从生成的查询中消失（这些独立命名的字段用于实例化所有预先加载的 ActiveRecord 对象）。
A disadvantage of this approach is that you then need to run a second query to pull in the desired ActiveRecord objects identified in the first query:
这种方法的一个缺点是，您需要运行第二个查询来拉入第一个查询中标识的所需 ActiveRecord 对象：
# first query
idents = Course
.eager_load(:students) # eager load for OUTER JOIN
.where(
student_enrollments: {student_id: some_user.id, id: nil},
active: true
)
.distinct
.pluck(:id) # abort eager loading but preserve OUTER JOIN
# second query
Course.where(id: idents)
