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

Answer 1

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.