43

I found one answer that had some usable having examples for finding parents with n children, but the same is not usable for finding parents with no children (presumably since the join excludes them).

scope :with_children, joins(:children).group("child_join_table.parent_id").having("count(child_join_table.parent_id) > 0")

Can anyone point me in the right direction?

strivedi183
  • 4,749
  • 2
  • 31
  • 38
Matt
  • 13,948
  • 6
  • 44
  • 68

3 Answers3

86

Update Rails 6.1

With the new Rails version this becomes simple, as described here:

.where.missing(:children)

For older versions see below.

Rails 3 & 4

scope :without_children, includes(:children).where(:children => { :id => nil })

The big difference here is the joins becoming a includes: an include loads all the relations, if they exists, the join will load only the associated objects and ignore the object without a relation.

In fact, scope :with_children, joins(:children) should be just enough to return the Parent with at least 1 child. Try it out!

Rails 5

See @Anson's answer below

Gem activerecord_where_assoc

The activerecord_where_assoc gem can do this for Rails 4.1 up to 6.0.

scope :without_children, where_assoc_not_exists(:children)

Self-referencing relation are handled seemlessly.

This also avoids issues such as joins making the query return multiple rows for a single record.

As @MauroDias pointed out, if it is a self-referential relationship between your parent and children, this code above won't work.

With a little bit of research, I found out how to do it:

Consider this model:

class Item < ActiveRecord::Base
  has_many :children, :class_name => 'Item', :foreign_key => 'parent_id'

How to return all items with no child(ren):

Item.includes(:children).where(children_items: { id: nil })

How did I find that children_items table?

Item.joins(:children) generates the following SQL:

SELECT "items".* 
FROM "items" 
 INNER JOIN "items" "children_items" 
 ON "children_items"."parent_id" = "items"."id"

So I guessed that Rails uses a table when in need of a JOIN in a self-referential case.

Similar questions:

Asped
  • 3,083
  • 4
  • 31
  • 52
MrYoshiji
  • 54,334
  • 13
  • 124
  • 117
  • Seems to have done the trick, same for the `with_children` scope - thanks @MrYoshiji! – Matt Aug 06 '13 at 14:21
  • 1
    What if children were the same class from the parent, a self join, how would this work? Example: class Item has_many :variations, :class_name => "Item", :foreign_key => 'parent_id' – Mauro Dias May 22 '14 at 17:39
  • @MrYoshiji WOw...pretty! Thank You guy! works even if the table calls 'items' – Mauro Dias May 22 '14 at 18:18
  • Love your trick with `scope :with_children, joins(:children)`, solved my problem (not the one OP had)! – Rudolf Jul 01 '14 at 19:58
  • I used to do in another way, but correct me if I am wrong: ```scope :without_affiliations, -> { includes(:affiliations).where({id: nil}) }``` when, ``` \has_many :affiliations, :inverse_of => :professor, :dependent => :delete_all``` where ```has_many :universities, :through => :affiliations ``` – Rubyrider Feb 08 '15 at 08:21
32

@MrYoshiji has a solid Rails 4 answer, but for folks coming here with Rails 5 you have more options.

Using Rails 5:

As of Rails 5, you can also use left_outer_joins to avoid loading the association. It was introduced in pull request #12071.

scope :without_children, -> { left_outer_joins(:children).where(children: { id: nil }) }

For parents with children, MrYoshiji's Rails 4 solution is still the one to use:

scope :with_children, -> { joins(:children).uniq }
Chiperific
  • 4,428
  • 3
  • 21
  • 41
Anson
  • 6,575
  • 2
  • 39
  • 33
  • `NoMethodError: undefined method `left_outer_joins' for # from /box/gems/activemodel-5.0.0.1/lib/active_model/attribute_methods.rb:433:in `method_missing'` – Tom Jul 28 '19 at 15:10
  • @Tom, it looks like your Domain model is ActiveModel, not ActiveRecord, so it wouldn't have any of the [ActiveRecord::QueryMethods](https://api.rubyonrails.org/classes/ActiveRecord/QueryMethods.html) – Anson Jul 29 '19 at 16:30
4

This is how I solved it for Rails 5:

scope :without_comments, -> do
  left_outer_joins(:comments).where(comments: { id: nil })
end
Fellow Stranger
  • 32,129
  • 35
  • 168
  • 232