Rails 5 left outer join using includes() and where()

Question

I'm having a heck of a time getting the intended behavior using includes() and where().

Result I want:
- All students (even if they have zero check-ins)
- All check-ins in the Library

Result I'm getting:
- Only students with check-ins in the library
- All check-ins in the library, for those students

Currently my code is based off of this: http://edgeguides.rubyonrails.org/active_record_querying.html#specifying-conditions-on-eager-loaded-associations

Which describes the behavior I want:

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.

My code:

@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

The generated SQL query:

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

This SQL query gives the join behavior I want:

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

Tried a new approach using Scopes with relations, expecting to preload everything and filter it out, but was pleasantly surprised that Scopes actually give me the exact behavior I want (right down to the eager loading).

Here's the result:

This ActiveRecord Call pulls in the full list of students and eager loads the check-ins:

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

The scope of check_ins can be limited right in the has_many declaration:

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

Resulting in two clean, efficient queries:

  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)

Bingo!

p.s. you can read more about using scopes with assocations here:
http://ducktypelabs.com/using-scope-with-associations/

Answer 2

What you want in terms of pure SQL is:

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

However it is not possible (afaik) to get ActiveRecord to mark the association as loaded without trickery*.

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

So if we iterate though the result it will cause a N+1 query issue:

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]]

To be honest, I never like preloading only a subset of association because some parts of your application probably assume that it is fully loaded. It might only make sense if you are getting the data to display it.
- Robert Pankowecki, 3 ways to do eager loading (preloading) in Rails 3 & 4

So in this case you should consider preloading all the data and using something like a subquery to select the count of check_ins.

I would also advise you to create a separate table for locations.

Answer 3

I think this is the only way to create the query you want.

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

Reference : http://apidock.com/rails/ActiveRecord/QueryMethods/joins