CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-3postgresqlactiverecord
Hank-Roughknuckles
Hank-Roughknuckles

Reputation: 578

Rails - only find records where has_many associated records are present

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.

Upvotes: 21

Views: 8564

Answers (5)

Claudio Floreani
Claudio Floreani

Reputation: 2571

Since a RIGHT (OUTER) JOIN returns all records from the associated (right) model, nilifying unmatched records from the (left) model, its usage when chained in a Rails query will effectively filters out all records without any associated records.

It takes full advantage of the DB engine, providing the best execution performance and use a single standard, highly portable SQL statement:

Author.joins("RIGHT OUTER JOIN posts ON posts.author_id=authors.id")

Voilà.

Upvotes: 0

Marian13
Marian13

Reputation: 9338

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:

Notes:

Upvotes: 12

Azat Gataoulline
Azat Gataoulline

Reputation: 737

Rails 4

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

or

Parent.joins(:child).distinct

Rails 3

Parent.joins(:child).distinct

Upvotes: 22

Wes Foster
Wes Foster

Reputation: 8910

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

Upvotes: 3

jvillian
jvillian

Reputation: 20253

Similar to Wes' answer, but staying a bit shy of SQL syntax:

Parent.find(Child.all.map{|c|c.parent_id}.uniq)

This bit:

Child.all.map{|c|c.parent_id}.uniq

gives you an array of parent_ids (with dupes removed using .uniq).

From there, it's a simple .find. Or, use .where if you prefer.

Upvotes: -3

Related Questions