Help with tricky query / relationship?


I've got a tree that is described by 2 different tables. One table is
for the nodes, and another is for the relationships. The relationship
table simply has a child_node_id and a parent_node_id. I'd like to
find a way to find the root relations, something like: Relation.roots

the roots are relationships that have child_node relationships
pointing to them (by the child_node relationship parent_node_id's) but
don't have any other relationships pointing to them as if they were
child nodes. In other words, I want to find all relations that have
child nodes, but no parent nodes. This seems difficult because every
relation has a child_node_id and a parent_node_id.

It seems to me that I'd have to do some kind of nested select or a
tricky join on the same table. Anyone want to take a shot at this?


I've copied in my fixtures below. The root relations in this case
would be the mom and dad relations.

Here is an example of my node fixture:

  name: dad
  description: father

  name: mom
  description: mother

  name: jr
  description: son

  name: son_of_jr
  description: grandchild of mom and dad

OK I have the SQL that does what I want, now how can I implement this
using the find method? I'd like to be able to use this but pass in
additional conditions, like using with_scope or something:

in the console:

CategoryRelation.find_by_sql('select a.* from category_relations as a left join category_relations b on a.parent_node_id = b.child_node_id where b.child_node_id IS NULL')

=> [#<CategoryRelation id: 953125641, child_node_id: 7170908,
parent_node_id: 858529756, notes: "jr is child of dad">,
#<CategoryRelation id: 996332877, child_node_id: 7170908,
parent_node_id: 936153944, notes: "jr is child of mom">]


I could be wrong but it sounds like you want all that
wheel-already-invented goodness that comes with acts_as_tree.

Whew, got it. I seem to always answer my own questions. Maybe
describing the problem to others helps? I think so...

class CategoryRelation < ActiveRecord::Base

  def self.roots(*args)
    #return find_by_sql("select a.* from #{table_name} as a left join
#{table_name} b on a.parent_node_id = b.child_node_id where
b.child_node_id IS NULL")
    conditions=['b.child_node_id IS NULL']
    joins='LEFT JOIN ' + "#{table_name}" + ' b ON a.parent_node_id =

with_scope(:find=>{:conditions=>conditions, :joins=>joins, :select=>'a.*', :from=>"#{table_name}
a"}) do
      find(:all, *args)


Ha, not quite (I wish it were that simple for me). The problem is this
model has multiple parents! So it's really a family tree. That's OK
though... because I'm just about ready to release (drum-roll
please).... "acts_as_family_tree" - I'll post an announcement here