Finding a node with no children in a tree like structure with Rails

Question

Hi, 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
   ...
end

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"

Answer 1

Got it:

named_scope :not_parent, :conditions => "id NOT IN (SELECT DISTINCT parent_id FROM nodes WHERE parent_id IS NOT NULL)"

Answer 2

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.

Answer 3

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)
end

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.