Rails 5左外连接使用includes()和where()

我正在使用includes()和where()获得预期的行为。

Result I want:
-所有学生(即使他们没有签到)
-图书馆内所有签到

结果:
-图书馆只允许签到的学生使用
-所有签到到图书馆，对于那些学生

目前我的代码是基于这个:http://edgeguides.rubyonrails.org/active_record_querying.html specifying-conditions-on-eager-loaded-associations

描述我想要的行为:

Article.includes(:comments).where(comments: { visible: true })
如果，在这个包含查询的情况下，没有任何注释文章，所有的文章仍然会被加载。

我的代码:

@students = Student.includes(:check_ins)
                    .where(check_ins: {location: "Library"})
                    .references(:check_ins)

class CheckIn < ApplicationRecord
  belongs_to :student
end

class Student < ApplicationRecord
  has_many :check_ins, dependent: :destroy
end

生成的SQL查询:

SELECT "students"."id" AS t0_r0,"check_ins"."id" AS t1_r0, "check_ins"."location" AS t1_r1, "check_ins"."student_id" AS t1_r6 FROM "students" LEFT OUTER JOIN "check_ins" ON "check_ins"."student_id" = "students"."id" WHERE "check_ins"."location" IN ('Library')

这个SQL查询给出了我想要的连接行为:

SELECT first_name, C.id FROM students S LEFT OUTER JOIN check_ins C ON C.student_id = S.id AND location IN ('Library');

尝试了一种使用关系作用域的新方法，期望预加载所有内容并将其过滤掉，但令人惊喜的是，作用域实际上给了我想要的确切行为(直到急于加载)。

结果如下:

这个ActiveRecord调用拉入学生的完整列表并热切加载签到:

@students = Student.all.includes(:check_ins)

check_ins的作用域可以在has_many声明中进行限制:

Class Student < ApplicationRecord
    has_many :check_ins, -> {where('location = 'Library'}, dependent: :destroy
end

生成两个干净高效的查询:

  Student Load (0.7ms)  SELECT "students".* FROM "students"
  CheckIn Load (1.2ms)  SELECT "check_ins".* FROM "check_ins" WHERE location = 'Library') AND "check_ins"."student_id" IN (6, 7, 5, 3, 1, 8, 9, 4, 2)

宾果!

注。你可以在这里阅读更多关于使用关联作用域的信息:
http://ducktypelabs.com/using-scope-with-associations/

您想要的纯SQL是:

LEFT OUTER JOIN "check_ins" ON "check_ins"."student_id" = "students"."id"
  AND location IN ('Library')

然而，这是不可能的(错误)，让ActiveRecord标记为没有欺骗的关联加载*。

class Student < ApplicationRecord
  has_many :check_ins
  def self.joins_check_ins
    joins( <<~SQL
      LEFT OUTER JOIN "check_ins" ON "check_ins"."student_id" = "students"."id"
      AND location IN ('Library')
    SQL
    )
  end
end

因此，如果我们遍历结果，它将导致N+1查询问题:

irb(main):041:0> Student.joins_check_ins.map {|s| s.check_ins.loaded? }
  Student Load (1.0ms)  SELECT "students".* FROM "students" LEFT OUTER JOIN "check_ins" ON "check_ins"."student_id" = "students"."id"
AND location IN ('Library')
=> [false, false, false]
irb(main):042:0> Student.joins_check_ins.map {|s| s.check_ins.size }
  Student Load (2.3ms)  SELECT "students".* FROM "students" LEFT OUTER JOIN "check_ins" ON "check_ins"."student_id" = "students"."id"
AND location IN ('Library')
   (1.2ms)  SELECT COUNT(*) FROM "check_ins" WHERE "check_ins"."student_id" = $1  [["student_id", 1]]
   (0.7ms)  SELECT COUNT(*) FROM "check_ins" WHERE "check_ins"."student_id" = $1  [["student_id", 2]]
   (0.6ms)  SELECT COUNT(*) FROM "check_ins" WHERE "check_ins"."student_id" = $1  [["student_id", 3]]

老实说，我从来不喜欢只预加载关联的子集因为应用程序的某些部分可能会认为它是正确的完全加载。它可能只有在您获得数据时才有意义显示它。
- Robert Pankowecki，在Rails 3中进行热切加载(预加载)的3种方法4

因此，在这种情况下，您应该考虑预加载所有数据，并使用类似子查询的东西来选择check_ins的计数。

我认为这是创建您想要的查询的唯一方法。

Student.joins("LEFT OUTER JOIN check_ins ON check_ins.student_id = students.id AND check_ins.location = 'Library'")

参考:http://apidock.com/rails/ActiveRecord/QueryMethods/joins

相关内容

最新更新

热门标签：