Take the 2-minute tour ×

I have a tree like structure roughly like this:

class Node < ActiveRecord::Base
   belongs_to :parent,   :class_name => self.to_s, :foreign_key => 'parent_id'
   has_many   :children, :class_name => self.to_s, :foreign_key => 'parent_id', :dependent => :destroy

I can load all nodes that don't have a parent with this scope:

named_scope :not_child, :conditions => 'parent_id IS NULL'

But I also need to find nodes that don't have children but can have a parent and I am having a hard time with it. I guess I have to include children_events but then I am lost, I cannot use:

named_scope, :faulty_not_parent, :include => :children, :conditions => "node.parent_id IS NULL"
share|improve this question

3 Answers 3

up vote 3 down vote accepted

Got it:

named_scope :not_parent, :conditions => "id NOT IN (SELECT DISTINCT parent_id FROM nodes WHERE parent_id IS NOT NULL)"
share|improve this answer
This works, but I edited your answer to search the "nodes" table so it matches the example in your question. This will avoid confusion with people who read this answer in the future. –  Jaime Bellmyer Oct 22 '10 at 20:04
Thanks and sorry! –  Macario Oct 22 '10 at 22:19
My scope worked on itself but when using other scopes I was having an ambiguous column name problem, had to be a bit more specific on column names: named_scope :not_parent, :conditions => "nodes.id NOT IN (SELECT DISTINCT nodes.parent_id FROM nodes WHERE nodes.parent_id IS NOT NULL)" –  Macario Oct 22 '10 at 22:28

I highly recommend you use a plugin for this. There are a few, acts_as_tree, awesome_nested_set and my personal recommendation, ancestry which uses only one column to map the structure rather than three as in nested set structures.

share|improve this answer

I'm not a SQL guru, so I couldnt' figure out a good way to do this in pure SQL. Of course, this means I couldn't find a way to do it with named_scopes, either. But you can find parents, and subtract that from all nodes, like so:

def childless
 @childless ||= self.all - self.all(:joins => :children)

This isn't as elegant as I'd like, since it requires loading a lot in memory. But it only takes 2 select statements, which is good. I'll be interested to see the other answers.

share|improve this answer

Your Answer


