Help with tricky query / relationship?

[Matt_M]

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

[Matt_M]

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

[Greg_Donald1 (Greg Donald)]

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

[Matt_M]

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

[Matt_M]

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