When I use a nested set, I make it like a combination of nested set and a simple tree. So in my database, I have:
id parent_id lft rgt name ---------------------------------------------- 1 NULL 1 20 Electronics 2 1 2 9 Televisions 3 2 3 4 Tube 4 2 5 6 LCD 5 2 7 8 Plasma 6 1 10 19 Portable Electronics 7 6 11 14 MP3 Players 8 7 12 13 Flash 9 6 15 16 CD Players 10 6 17 18 2-way Radios Then I get things the following way: For MP3 Players (#7): -- Ancestors select * from mytable where lft < 11 and rgt > 14; -- Ancestors and self select * from mytable where lft <= 11 and rgt >= 14; -- Parent select * from mytable where id = 6; -- Siblings and self select * from mytable where parent_id = 6; -- Siblings select * from mytable where parent_id = 6 and id <> 7; -- Siblings on the left select * from mytable where parent_id = 6 and rgt < 11; -- Siblings on the right select * from mytable where parent_id = 6 and lft > 14; -- Immediate children select * from mytable where parent_id = 7; -- Descendants and self select * from mytable where lft >= 11 and rgt <= 14; -- Descendants select * from mytable where lft > 11 and rgt < 14; In JPA/Hibernate, you'd have to modify this slightly. Each node in the tree would have a parent: Node attribute and a children: java.util.Set[Node] attribute. So then for example to get immediate children you would do: from Node n where n.parent.id = :id (But you could just access them through node.children as well.) Is this helping at all? Chas. Tim Perrett wrote: > Im not sure I follow? Can you use this diagram to try and explain?: > > http://dev.mysql.com/tech-resources/articles/hierarchical-data-4.png > > If you wanted to select the direct children of 'electronics', how > would one go about doing that? Is there a simpler way than all that > HQL? > > Cheers > > Tim > > On Dec 1, 10:28 am, "Charles F. Munat" <[EMAIL PROTECTED]> wrote: >> Are you including a link to the parent node or are you just using the >> left and right values to figure out which nodes are children? I always >> include a foreign key to the parent so I can just select the children of >> that parent directly. Then to get all descendants, I use the left and >> right values. Similarly, to get the parent I use the foreign key, to get >> all ancestors, I use the left/right values. >> >> Does this help? >> >> (Sometimes I also include a calculated field called "level" which >> indicates how far away I am from the root node. It's a simple matter of >> counting the ancestors, but you have to remember to update it on moves. >> The level field is very useful if you want to go, say, two levels deep >> on children, or to parent and grandparent, but not great-grandparent.) >> >> Chas. >> >> Tim Perrett wrote: >> >>> On Dec 1, 9:21 am, "Charles F. Munat" <[EMAIL PROTECTED]> wrote: >>>> Will do, if I figure it out. But my comment wasn't complaining. I am >>>> honestly mystified. Am I the only one using trees in Hibernate? Is there >>>> a tree library in Scala that I'm missing? What the heck does everyone >>>> else do? It just blows my mind. >>> I found someone's contribution on Hibernate JIRA for handling sets, >>> but it never seems to make it into core or the proper distro; it does >>> seem odd its not in there. >>> Anyway, the HQL problem I mentioned last night... I have the following >>> HQL, which is just a slight mod from what was in that mysql article; >>> effectivly I want to list all the children of a single leaf: >>> SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth >>> FROM Content AS node, >>> Content AS parent, >>> Content AS sub_parent, >>> ( SELECT node.name, (COUNT(parent.name) - 1) AS depth >>> FROM Content AS node, >>> Content AS parent >>> WHERE node.lft BETWEEN parent.lft AND parent.rft >>> AND node.name = :leaf >>> GROUP BY node.name >>> ORDER BY node.lft >>> ) AS sub_tree >>> WHERE node.lft BETWEEN parent.lft AND parent.rft >>> AND node.lft BETWEEN sub_parent.lft AND sub_parent.rft >>> AND sub_parent.name = sub_tree.name >>> GROUP BY node.name >>> HAVING depth <= 1 >>> ORDER BY node.lft >>> However, it bombs on the sub-query in the SELECT ... FROM ... >>> <subquery> statement.... im guessing you guys are using "get all leaf >>> nodes" type queries, so what are you doing instead of this? >>> Cheers >>> Tim > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Lift" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/liftweb?hl=en -~----------~----~----~----~------~----~------~--~---
