ActiveRecord #includes, with condition, but load all relations, not just those matching condition

Question

I have ActiveRecord models Parent and Kid. Parents have many Kids.

I have a Parent whose name is Dad, with 3 Kids, whose names are Abel, Bobby, and Cain. I want to find the Parent based on the name of only 1 of the Kids.

parent = Parent.includes(:kids).
  find_by(kids: { name: 'Bobby' })

The above query provides me the Parent I want, but parent.kids only includes Bobby. I want Abel and Cain to be included in parent.kids as well, just like if I did:

Parent.find_by(name: 'Dad').kids

I could do:

Parent.includes(:kids).
  find_by(kids: { name: 'Bobby' }).
  tap { |parent| parent&.kids.reload }

But is there not a “better” way of fetching the rest of the kids? For instance, is there a way I could load all the kids of the parent while querying for the parent (via one SQL statement)?

Answer 1

How about making the Parent.includes(:kids).find_by(kids: { name: 'Bobby' } a subquery

Parent.includes(:kids).where(id: Parent.includes(:kids).find_by(kids: { name: 'Bobby' })

Answer 2

You want an INNER JOIN:

parents = Parent.joins(:kids).where(kids: { name: 'Bobby' })

This will only include rows from parents with a match in the kids table. You can apply it as a subquery to avoid removing the rest of the joined rows:

parents = Parent.where(
            id: Parent.joins(:kids).where(kids: { name: 'Bobby' })
          ).includes(:kids)