Rails - only find records where has_many associated records are present

Question

I have a model with a has_many relation with another model like so:

class Parent < ActiveRecord::Base
  has_many :children
end

class Child < ActiveRecord::Base
  belongs_to :parent
end

Since there are some parents that may not have children, I'd like to do a query that returns only the parents that DO have children. How would I go about something like this?

Since I'm doing this on Rails 3, it would be helpful if this query were to not use the where.not. syntax.

Answer 1

Rails 4

Parent.includes(:child).where.not(children: {id: nil})

or

Parent.joins(:child).distinct

Rails 3

Parent.joins(:child).distinct

Answer 2

where.associated (Rails 7+)

Rails 7 introduces a new way to check for the presence of an association - where.associated.

Please, have a look at the following code snippet:

# Before:
account.users.joins(:contact).where.not(contact_id: nil)

# After:
account.users.where.associated(:contact)

And this is an example of SQL query that is used under the hood:

Post.where.associated(:author)
# SELECT "posts".* FROM "posts"
# INNER JOIN "authors" ON "authors"."id" = "posts"."author_id"
# WHERE "authors"."id" IS NOT NULL

As a result, your particular case can be rewritten as follows:

Parent.where.associated(:child)

Thanks.

Sources:

• where.associated official docs.

• Pull request.

Notes:

• where.missing - a counterpart for checking for the absence of an association is also available starting from Rails 6.1.
• See offical docs and this answer.

Answer 3

Can be done using an SQL syntax

Parent.where('id IN (SELECT DISTINCT(parent_id) FROM children)')

Or, to keep things DRY, can be used in a scope:

class Parent < ActiveRecord::Base
  has_many :children

  scope :with_children, where('id IN (SELECT DISTINCT(parent_id) FROM children)')
end

Then you can find the parents that have children using:

Parent.with_children