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

Question

我有一段时间使用 includes() 和 where() 获得预期的行为。

我想要的结果:
- 所有学生(即使他们签到为零)
- 在图书馆签到

我得到的结果:
- 只有在图书馆签到的学生
- 所有学生在图书馆签到

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

它描述了我想要的行为:

Article.includes(:comments).where(comments: { visible: true })

If, in the case of this includes query, there were no comments for any articles, all the articles would still be loaded.

我的代码:

@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');

Answer 1

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

结果如下:

这个 ActiveRecord 调用会拉取完整的学生列表并立即加载签到:

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

可以在 has_many 声明中限制 check_ins 的范围:

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/