Help with tricky query / relationship?

Hi,

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 where:

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?

Thanks, Matt

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:

dad:   name: dad   description: father

mom:   name: mom   description: mother

jr:   name: jr   description: son

son_of_jr:   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">]

Thanks, Matt

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

http://wiki.rubyonrails.org/rails/pages/ActsAsTree

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 = b.child_node_id'

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

end

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 soon.

Matt