Rails 4 scope to find parents with no children

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:

• How to query a model based on attribute of another model which belongs to the first model?
• Rails active record querying association with 'exists'
• Rails 3, has_one / has_many with lambda condition
• Join multiple tables with active records

@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 }

This is how I solved it for Rails 5:

scope :without_comments, -> do
  left_outer_joins(:comments).where(comments: { id: nil })
end