Using Rails includes with conditions on children

Question

I have a model Parent that has many children Child. I want to get all Parent models and show every Child of the Parent as well. This is a classic use case for Rails' includes method, as far as I can tell.

However, I can't get Rails to add conditions to the child models without limiting the Parent models to those that have children.

For example, this only outputs parents that have children:

Parent.includes(:children).where(children: {age: 10}).each do |parent|
  # output parent info
  parent.children.where("age = 10").each do |child|
   #output child info
  end
end

I've looked at Rails includes with conditions but it seems like I'm having the same trouble as the question's OP and neither part of the accepted answer doesn't solve it (it either has only some parents, or resorts to multiple queries).

What DBMS you are using ? – Arup Rakshit Aug 25 '15 at 15:37 — Arup Rakshit, Aug 25 '15 at 15:37

score 6 · Accepted Answer · answered Aug 25 '15 at 15:40

You need to use LEFT JOIN.

Parent.joins("LEFT JOIN children ON parent.id = children.parent_id")
      .where("parent.age = 10 AND children.age = 10")
      .select("parent.*, children.*")

If you want to select rows from the parent table which may or may not have corresponding rows in the children table, you use the LEFT JOIN clause. In case there is no matching row in the children table, the values of the columns in the children table are substituted by the NULL values.

Volte · Answer 2 · 2016-12-29T16:47:12.643

I ran into this issue, thus stumbling across this question. Sadly, none of the answers so far are solutions. Happily, I have found the solution! Thanks in-part to the docs :) http://apidock.com/rails/ActiveRecord/QueryMethods/includes

As the docs suggest, simply including the association, and then adding a condition to it is not sufficient; you must also "reference" the association references(:children).

Now, additionally you can see that I'm using some syntactic sugar that I recommend for merging in your conditions, versus re-writing them. Use this when possible.

Parent.includes(:children).merge(Child.at_school).references(:children).first

So what I did, and what I suggest doing is setting up a scope for this:

class Parent < ActiveRecord::Model

    has_many :children

    scope :with_children_at_school, -> { includes(:children).merge(Child.at_school).references(:children) }
    # ...
end

And then you can just call Parent.with_children_at_school.first (or whatever else you want to chain on to the end!

I hope this helps!

This does the same thing as Parent.includes(:children).where(...), i.e. limits it to parents with children only. — Slava Eremenko, Oct 19 '21 at 18:17

score 3 · Answer 3 · answered Aug 25 '15 at 15:41

3

This a limitation of the includes method. What you need is an outer join and unfortunately rails doesnt have a good way to force an outer join without using the raw sql syntax (#joins defaults to inner join and #includes eager loads).
try using something along the lines of

Parent.joins('LEFT OUTER JOIN child on child.parent_id = parent.id').where(...)

this should grab all parents, even those without children

answered Aug 25 '15 at 15:41

PhilVarg

4,762
2
19
37

Thanks. If I do a JOIN, do I still get the performance benefits? It seems like I would - but then I don't understand why `includes` even exists. – you786 Aug 25 '15 at 15:45
@you786 `includes` don't do joins. See [here](http://guides.rubyonrails.org/active_record_querying.html#eager-loading-associations). It eager loads by using `IN` query. – Arup Rakshit Aug 25 '15 at 16:03
@you766 Includes does do a join in rails 4 using the call style you have above. It also defaults to a LEFT OUTER JOIN which is what you are looking for. If you call .to_sql on your statement you will see that this is the case, again in rails 4. Rails 3 works differently and uses an inner join. – dinomix Aug 25 '15 at 17:07

score 1 · Answer 4 · answered Aug 25 '15 at 16:12

This is not a 100% answer, but one approach is to accept that you wil get all child records returned by the eager loading, but to choose the ones that you then want using a non-ActiveRecord method.

You will includes more child records in the eager loading than you need, so that's less efficient than a perfect solution, but you still get the records you want:

Parent.includes(:children).each do |parent|
  parent.children.select{|child| child.age == 10}.each do |child|
    blah blah...
  end
end

I'm assuming here that you need a lot of flexibility on your select criteria, and that an association based on a scope would not offer such flexibility.

score 0 · Answer 5 · answered Aug 25 '15 at 15:37

0

The parents who don't have children will have a children.age of NULL, you are only filtering for children.age = 10.

Try

where('children.age = 10 or children.age is null')

answered Aug 25 '15 at 15:37

dinomix

956
4
5

Using Rails includes with conditions on children

5 Answers5

Linked