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 ######## And here is an example of my relation fixture: ######## one: child_node: jr parent_node: dad notes: jr is child of dad two: child_node: jr parent_node: mom notes: jr is child of mom three: child_node: son_of_jr parent_node: jr notes: son_of_jr is child of jr --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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 On Dec 31, 3:52 pm, goodieboy <goodie...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> 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 > > ######## > And here is an example of my relation fixture: > ######## > > one: > child_node: jr > parent_node: dad > notes: jr is child of dad > > two: > child_node: jr > parent_node: mom > notes: jr is child of mom > > three: > child_node: son_of_jr > parent_node: jr > notes: son_of_jr is child of jr--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On 12/31/07, goodieboy <goodieBoy-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> 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: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 -- Greg Donald http://destiney.com/ --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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
On Dec 31, 4:07 pm, goodieboy
<goodie...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:> 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
>
> On Dec 31, 3:52 pm, goodieboy
<goodie...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> > 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
>
> > ########
> > And here is an example of my relation fixture:
> > ########
>
> > one:
> > child_node: jr
> > parent_node: dad
> > notes: jr is child of dad
>
> > two:
> > child_node: jr
> > parent_node: mom
> > notes: jr is child of mom
>
> > three:
> > child_node: son_of_jr
> > parent_node: jr
> > notes: son_of_jr is child of jr
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
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 On Dec 31, 4:17 pm, goodieboy <goodie...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> 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 > > On Dec 31, 4:07 pm, goodieboy <goodie...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > 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 > > > On Dec 31, 3:52 pm, goodieboy <goodie...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > > 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 > > > > ######## > > > And here is an example of my relation fixture: > > > ######## > > > > one: > > > child_node: jr > > > parent_node: dad > > > notes: jr is child of dad > > > > two: > > > child_node: jr > > > parent_node: mom > > > notes: jr is child of mom > > > > three: > > > child_node: son_of_jr > > > parent_node: jr > > > notes: son_of_jr is child of jr--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---