19

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.

potashin
  • 44,205
  • 11
  • 83
  • 107
Hank-Roughknuckles
  • 528
  • 6
  • 16

5 Answers5

22

Rails 4

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

or

Parent.joins(:child).distinct

Rails 3

Parent.joins(:child).distinct

samjewell
  • 1,068
  • 11
  • 20
Azat Gataoulline
  • 737
  • 3
  • 8
  • Yours seems cleaner than mine. Nice job! – jvillian Feb 19 '16 at 15:43
  • If you're using a custom table name in your model ( `self.table_name = "child_table"`), then use `Parent.includes(:children).where.not(child_table: {id: nil})` . – Zek Jun 28 '16 at 17:30
  • Pleas note the complex form of the subtable. – YaEvan Aug 16 '18 at 02:08
  • 2
    You should add the keyword **references** to use **where** with **includes** like so: `Parent.includes(:children).references(:children).where.not(children: {id: nil})` – konyak Oct 19 '18 at 20:55
  • When using `.joins` the default join is an inner join, and not a left join. As a result you can omit the "where not id nil" part, as these get omitted during the join step. ie. omit these parts: - `.where.not(children: {id: nil})` - `.where('children.id NOT NULL')` – samjewell Aug 05 '20 at 08:27
10

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:

Marian13
  • 7,740
  • 2
  • 47
  • 51
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
Wes Foster
  • 8,770
  • 5
  • 42
  • 62
0

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

Claudio Floreani
  • 2,441
  • 28
  • 34
-3

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.

jvillian
  • 19,953
  • 5
  • 31
  • 44
  • 1
    Your solution uses ruby array methods, and does 2 separate queries. I am inclined to believe it's more optimized to do this by AR capabilities `Parent.find(Child.uniq.pluck(:parent_id))` but again this will be 2 separate queries – Azat Gataoulline Feb 19 '16 at 15:52
  • 3
    Yep. I agree. That's why I upvoted your answer. By the way, my view is that it is useful to future readers to have some less experienced answers (like mine) accompanied by critique from more experience developers (like you) so that they can have a more thorough understanding of why to do a thing a particular way. So, thanks for the follow up note. – jvillian Feb 19 '16 at 15:56