在Rails 4中的LEFT OUTER JOIN

Question

我有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

我希望查询Courses表中与某个学生关联的，但在StudentEnrollments表中不存在的课程列表。

我发现也许左连接是解决这个问题的方法，但是在Rails中，join()似乎只接受一个表作为参数。我认为能够实现我想要的功能的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

我该如何以Rails 4的方式执行这个查询？

非常感谢您的任何建议。

- Khanetor

---

如果在StudentEnrollments中记录不存在，那么se.student_id = <SOME_STUDENT_ID_VALUE>肯定是不可能的吧？ - PJSCopeland

Answer 1

你可以传递一个表示join-sql的字符串。例如，joins("LEFT JOIN StudentEnrollment se ON c.id = se.course_id")。

尽管我会使用符合Rails标准的表命名以提高清晰度：

joins("LEFT JOIN student_enrollments ON courses.id = student_enrollments.course_id")

- Taryn East

---

我的解决方案最终是这样的： query = "LEFT JOIN student_enrollments ON courses.id = student_enrollments.course_id AND" + " student_enrollments.student_id = #{self.id}" courses = Course.active.joins(query) .where(student_enrollments: {id: nil})虽然它不像我想要的那样符合Rails的风格，但它完成了工作。我尝试使用.includes()，它可以进行LEFT JOIN，但不能让我在连接时指定额外的条件。 谢谢Taryn！ - Khanetor

太好了。有时候我们做事情就是为了让它能够运行。现在是回来重新审视并在未来使其更好的时候了... :) - Taryn East

@TarynEast "让它工作，让它快速，让它美丽。" :) - Joshua Pinter

Answer 2

如果有人在这里寻找一种通用的方法来执行Rails 5中的左外连接，您可以使用#left_outer_joins函数。

多重连接示例：

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

- Blaskovicz

---

谢谢，我想提一下关于交叉关联左外连接，使用left_outer_joins(a: [:b, :c])。 - Fangxing

同时，您还可以使用 left_joins 进行简短的操作，其行为方式与上述相同。例如：left_joins(:order_reports)。 - alexventuraio

Answer 3

实际上有一种“Rails方式”来完成这个任务。

你可以使用Arel，这是Rails用于构建ActiveRecrods查询的工具。

我会将它封装在一个方法中，这样你就可以很好地调用它并传入任何你想要的参数，例如：

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

还有许多人使用的快速（但略显粗糙）方法

Course.eager_load(:students).where(
    student_enrollments: {student_id: some_user.id, id: nil}, 
    active: true
)

eager_load非常好用，只是有一个“副作用”，会将你可能不需要的模型加载到内存中（就像在你的情况下一样）
请参阅Rails ActiveRecord::QueryMethods .eager_load
它以一种简洁的方式完全满足了你的需求。

- superuseroi

---

这么多年过去了，我必须说我无法相信 ActiveRecord 仍然没有内置支持此功能。这完全是不可思议的。 - mrbrdo

那么，Sequel 何时可以成为 Rails 中默认的 ORM？ - animatedgif

Rails 不应变得臃肿。我的看法是，当他们决定将默认捆绑的 gem 提取出来时，他们做得很对。这个哲学是“做得少但要做好”，“挑选你想要的”。 - Adit Saxena

Rails 5支持左外连接：http://blog.bigbinary.com/2016/03/24/support-for-left-outer-joins-in-rails-5.html - Murad Yusufov

为了避免 eager_load 的“副作用”，请参考我的答案。 - textral

Answer 4

结合includes和where会导致ActiveRecord在幕后执行一个LEFT OUTER JOIN（如果没有where，则这将生成两个普通查询的集合）。

因此，您可以像下面这样做：

Course.includes(:student_enrollments).where(student_enrollments: { course_id: nil })

文档在此处：http://guides.rubyonrails.org/active_record_querying.html#specifying-conditions-on-eager-loaded-associations

- mackshkatz

---

这个会起作用，但是使用.includes默认会选择所有字段（使用自定义的.select不会删除其他字段）。 - dlauzon

Answer 5

在上面的回答中提到，如果你想要一个不需要在where条件中引用表格（比如id为空）或者引用在字符串中的OUTER JOIN，可以使用includes和references。具体操作如下：

Course.includes(:student_enrollments).references(:student_enrollments)

或者

Course.includes(:student_enrollments).references(:student_enrollments).where('student_enrollments.id = ?', nil)

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

- Jonathon Gardner

---

这对于深度嵌套的关系是否有效，还是关系需要直接挂在被查询的模型上？我似乎找不到前者的任何示例。 - a2f0

太好了！只需要将“joins”替换为“includes”，问题就解决了。 - RaphaMex

Answer 6

您可以执行以下查询：

Course.joins('LEFT JOIN student_enrollment on courses.id = student_enrollment.course_id')
      .where(active: true, student_enrollments: { student_id: SOME_VALUE, id: nil })

- Joe Kennedy

Answer 7

我知道这是一个老问题和旧线程，但在Rails 5中，你可以简单地这样做：

Course.left_outer_joins(:student_enrollments)

- zekromWex

---

问题特别针对Rails 4.2版本。 - Volte

Answer 8

你可以使用left_joins gem，该gem将Rails 5的left_joins方法回溯到Rails 4和3。

Course.left_joins(:student_enrollments)
      .where('student_enrollments.id' => nil)

- khiav reoy

Answer 9

我已经苦恼这种问题有一段时间了，决定采取措施来一劳永逸地解决它。我发布了一个Gist来解决这个问题：https://gist.github.com/nerde/b867cd87d580e97549f2 我创建了一个小型的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

希望这有所帮助。

- Diego

---

喜欢这个递归解决方案！ - Lorin Thwaits

Answer 10

请看下面我对这个问题的原始帖子。

从那时起，我已经为ActiveRecord v4.0.x实现了自己的.left_joins()（抱歉，我的应用程序被冻结在这个版本，所以我没有必要将其移植到其他版本）：

在文件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)

现在我可以在通常使用.joins()的地方使用.left_joins()。

----------------- 原始帖子如下 -----------------

如果您想要外连接而又不想要所有额外的急切加载的ActiveRecord对象，请在.eager_load()之后使用.pluck(:id)来中止急切加载，同时保留外连接。使用.pluck(:id)会破坏急切加载，因为列名称别名（例如items.location AS t1_r9）会从生成的查询中消失（这些独立命名的字段用于实例化所有急切加载的ActiveRecord对象）。

这种方法的劣势是，您需要运行第二个查询，以获取在第一个查询中确定的所需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)

- textral

---

这很有趣。 - a2f0

+1，但您可以进一步改进并使用select(:id)而不是pluck(:id)，避免实例化内部查询，并将其全部留给数据库。 - Andre Figueiredo