Find parent with no children in self-referential relationship

Question

In Rails 4, how can I find all records which have no children in a self-referential parent-child relationship?

The model is a task which may have a single parent task and multiple child tasks. Here's the model code:

class Task < ActiveRecord::Base
  belongs_to :parent, class_name: 'Task'
  has_many :children, class_name: 'Task', foreign_key: 'parent_id'
end

I've tried the solution from a similar question about finding parents with no children (in a situation which doesn't involve self-referential models), but it returns the error:

ActiveRecord::StatementInvalid: SQLite3::SQLException: no such column: children.id

I think I've found a solution by adapting this answer to give:

scope :without_children, joins("left join tasks as children on children.parent_id = tasks.id").where("children.parent_id is null")

But is there a more idiomatic way to solve the problem rather than explicitly writing out the SQL?

Answer 1

I don't know of a more rubyish method , but the SQL method would be more efficient where multiple children might exist as:

scope :without_children, where("not exists (select null from children where children.parent_id = tasks.id)")

People seem to love that outer join method, possibly because of an inefficient implementation of the not exists method in some databases, but with not exists a half-decent query optimiser will use a semi-join that stops looking for other children after finding the first.