Rails/SQL: Find parents that have no child or have all children with a condition

Question

I need to find parents that either have no child OR have all children exclusively with condition (status = 1).

class Parent
  has_many :children
end

class Child
  enum status: [ :confirmed, :not_confirmed ]
  belongs_to :parent
end

I know the first part, which is finding parents with no children.

Parent.joins(:children).where('count(children) = 0')

Rails answer.

You might want to see [Rails find record with zero has_many records associated](https://stackoverflow.com/a/39410256/6231376) — TeWu, Mar 05 '19 at 13:51

Kache · Accepted Answer · 2019-03-05T13:41:28.760

6

Since you're using Postgres, you can use a NOT EXISTS query:

# Parents with no children
Parent.where.not('exists (?)', Child.where('children.parent_id = parents.id').select(1))

This query performs better than anything requiring a join, as an EXPLAIN will show you that Postgres will accomplish this with a Nested Loop Anti Join operation.

edited Mar 05 '19 at 13:41

answered Mar 05 '19 at 12:28

Kache

15,647
12
51
79

what does select(1) do in the above code? – Bibek Sharma Aug 15 '22 at 23:18
It's just a placeholder value. The meat of the sub-query is the WHERE condition, so it's testing whether `SELECT 'i_dont_care_value' WHERE important_condition = is_true_or_not` produces any rows at all. – Kache Aug 15 '22 at 23:46

score 1 · Answer 2 · answered Mar 05 '19 at 03:41

Here is a solution in Rails:

grp = Parent.left_outer_joins(:children).distinct
models = grp.where('children.id IS NULL').or(grp.where('children.status = 1'))

Basically you need to use LEFT OUTER JOIN (see left_outer_joins in Rails5 Reference).

I do not think your example of the first part works. It would return an error message like

# ActiveRecord::StatementInvalid (PG::GroupingError: ERROR:  
# aggregate functions are not allowed in WHERE

and also Rails joins is INNER JOIN.

Rails/SQL: Find parents that have no child or have all children with a condition

2 Answers2