Rails includes with conditions
SqlRuby on-RailsSql Problem Overview
Is is possible in Rails > 3.2 to add conditions to the join statement generated by the includes
method?
Let's say I have two models, Person and Note. Each person has many notes and each note belong to one person. Each note has an attribute important
.
I want to find all the people preloading only the notes that are important. In SQL that will be:
SELECT *
FROM people
LEFT JOIN notes ON notes.person_id = people.id AND notes.important = 't'
In Rails, the only similar way to do that is using includes
(note: joins
won't preload notes) like this:
Person.includes(:notes).where(:important, true)
However, that will generate the following SQL query which returns a different result set:
SELECT *
FROM people
LEFT JOIN notes ON notes.person_id = people.id
WHERE notes.important = 't'
Please, notice that the first resultset includes all the people and the second one only the people associated to important notes.
Also notice that :conditions are deprecated since 3.1.
Sql Solutions
Solution 1 - Sql
According to this guide Active Record Querying
You can specify conditions on includes for eager loading like this
Person.includes(:notes).where("notes.important", true)
It recommends to use joins
anyway.
A workaround for this would be to create another association like this
class Person < ActiveRecord::Base
has_many :important_notes, :class_name => 'Note',
:conditions => ['important = ?', true]
end
You would then be able to do this
Person.find(:all, include: :important_notes)
Solution 2 - Sql
Rails 5+ syntax:
Person.includes(:notes).where(notes: {important: true})
Nested:
Person.includes(notes: [:grades]).where(notes: {important: true, grades: {important: true})
Solution 3 - Sql
Rails 4.2+:
Option A - "preload": multiple selects, uses "id IN (...)"
class Person < ActiveRecord::Base
has_many :notes
has_many :important_notes, -> { where(important: true) }, class_name: "Note"
end
Person.preload(:important_notes)
SQL:
SELECT "people".* FROM "people"
SELECT "notes".* FROM "notes" WHERE "notes"."important" = ? AND "notes"."person_id" IN (1, 2)
Option B - "eager_load": one huge select, uses "LEFT JOIN"
class Person < ActiveRecord::Base
has_many :notes
has_many :important_notes, -> { where(important: true) }, class_name: "Note"
end
Person.eager_load(:important_notes)
SQL:
SELECT "people"."id" AS t0_r0, "people"."name" AS t0_r1, "people"."created_at" AS t0_r2, "people"."updated_at" AS t0_r3, "notes"."id" AS t1_r0, "notes"."person_id" AS t1_r1, "notes"."important" AS t1_r2
FROM "people"
LEFT OUTER JOIN "notes" ON "notes"."person_id" = "people"."id" AND "notes"."important" = ?
Solution 4 - Sql
Same was discussed in Japanese stackoverflow. Quite hacky, but following seems to work, at least on rails 5.
Person.eager_load(:notes).joins("AND notes.important = 't'")
One important aspect is that by this way, you can write arbitrary join condition. Down side is that you cannot use placeholder so you need to be careful when using params as the join condition.
Solution 5 - Sql
I was unable to use the includes with a condition like Leo Correa's answer. Insted I neeed to use:
Lead.includes(:contacts).where("contacts.primary" =>true).first
or you can also
Lead.includes(:contacts).where("contacts.primary" =>true).find(8877)
This last one will retrieve the Lead with id 8877 but will only include its primary contact
Solution 6 - Sql
For people interested, I tried this where a record attribute was false
Lead.includes(:contacts).where("contacts.primary" => false).first
and this doesn't work. Somehow for booleans only true
works, so I turned it around to include where.not
Lead.includes(:contacts).where.not("contacts.primary" => true).first
This works perfectly
Solution 7 - Sql
One way is to write the LEFT JOIN clause yourself by using joins:
Person.joins('LEFT JOIN "notes" ON "notes"."person_id" = "people.id" AND "notes"."important" IS "t"')
Not pretty, though.