Rails 4 scope to find parents with no children

Question

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?

Accepted Answer

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.

Rails 4 scope to find parents with no children

Solution

Problem

Related problems