75

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.

gusa
  • 823
  • 1
  • 6
  • 9
  • Is there any difference in performance? notes.important = 't' in a join clause is not semantic, since it only takes in consideration one model. – fotanus May 02 '13 at 22:04
  • Yes. It's a matter of performance. I need all the people, eager loading important notes if there are any. – gusa May 03 '13 at 18:16
  • [Rails Active Record Guide](http://guides.rubyonrails.org/active_record_querying.html) says "If, in the case of this includes query, there were no comments(notes, in this example) for any articles(Persons), all the articles(Persons) would still be loaded. By using joins (an INNER JOIN), the join conditions must match, otherwise no records will be returned.". This really doesn't seem correct. – bkdir Sep 16 '17 at 07:06

9 Answers9

47

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)
Leo Correa
  • 19,131
  • 2
  • 53
  • 71
  • 15
    Person.includes(...).where(...) returns only people associated with important notes. I need all the people, eager loading important notes. – gusa May 03 '13 at 18:18
  • 5
    The second solution solves the example, which I drew for the sake of clarity. However, reality is more complicated. Suppose now that I need important notes in a period (between start_date and end_date). – gusa May 03 '13 at 18:21
  • https://stackoverflow.com/a/51870495/3090068 would return all the people, without introducing any new has_many relation, a bit ugly though... – Yuki Inoue Aug 16 '18 at 06:02
  • The second solution is great since it returns all Person objects even if they don't have important notes. If you want notes in a period you can just use a normal where clause has_many :important_notes, -> { where('important = ? AND start_date > ? AND end_date < ?', true, 2.weeks.ago, 1.day.ago) }, class_name: "Note" – yoshyosh Feb 27 '21 at 19:49
42

Rails 5+ syntax:

Person.includes(:notes).where(notes: {important: true})

Nested: 

Person.includes(notes: [:grades]).where(notes: {important: true, grades: {important: true})
Graham Slick
  • 6,692
  • 9
  • 51
  • 87
  • 24
    This is not working as it was mentioned here: [Active Record Guide](http://guides.rubyonrails.org/active_record_querying.html). It is not returning Person objects which are not associated with a Note. It will only return Persons who have a note with master attribute equals to true – bkdir Sep 16 '17 at 07:02
23

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" = ?
Daniel Loureiro
  • 4,595
  • 34
  • 48
  • See this answer if anyone wants to manually specify join parameter... https://stackoverflow.com/a/51870495/3090068 – Yuki Inoue Aug 16 '18 at 06:05
11

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.

https://ja.stackoverflow.com/q/22812/754

Yuki Inoue
  • 3,569
  • 5
  • 34
  • 53
  • 1
    To use placeholders, you can use the ActiveRecord::Base#sanitize_sql_array method documented here : https://apidock.com/rails/ActiveRecord/Sanitization/ClassMethods/sanitize_sql_array `Person.eager_load(:notes).joins(Person.sanitize_sql_array(["AND notes.important = ?", true])` – Vala Sep 18 '18 at 15:31
  • This is great. Thank you! But there is also another limitation! If we want to eager_load multiple associations and there are multiple left joins, we can't control which join this will add an additional condition to... and I haven't been able to get over it lol – Slava Eremenko Oct 19 '21 at 18:07
5

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

juliangonzalez
  • 4,231
  • 2
  • 37
  • 47
2

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

Yoko
  • 793
  • 10
  • 19
1

A really simple example:

User.includes(:posts).where(posts: { name: 'John' })

Notes:

  • If your association is has_one, replace :posts with :post (watch the plural/singular).

  • More info here.

To take it one association further, if you had

thing.other_things.includes(:even_more_things).where(other_things: {col1: 'value'})

but you wanted to filter on even_more_things, you could use:

thing.other_things.joins(:even_more_things).where(even_more_things: { attribute: value })
stevec
  • 41,291
  • 27
  • 223
  • 311
0

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.

kirstu
  • 1
0

you can try

Person.includes(:notes).where(notes: { important: 't' })

make sure that the Note table name is notes and in person model the association like this : has_many :notes or you can change it Person.includes(:asoociation).where(table_name: { important: 't' })

Elamine
  • 1
  • 1