CODEWITHSUNDEEP
ruby-on-railsrails-activerecord
you786
you786

Reputation: 3550

Using Rails includes with conditions on children

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

Upvotes: 6

Views: 10649

Answers (5)

Volte
Volte

Reputation: 1905

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!

Upvotes: 4

David Aldridge
David Aldridge

Reputation: 52376

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.

Upvotes: 1

PhilVarg
PhilVarg

Reputation: 4820

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

Upvotes: 3

Arup Rakshit
Arup Rakshit

Reputation: 118299

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.

Upvotes: 6

dinomix
dinomix

Reputation: 976

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

Upvotes: 0

Related Questions