4

How do you query a nested set model with multiple roots, such trees in the same table? Currently, I added an extra column called the "Root" indicating the ID of the root node for all sub-tree nodes, however, I can't figure out the sql to retrieve them in the proper order

I'm referring to the article Managing Hierarchical Data in MySQL.

Normally, the query to retrieve the items out in order is by order the left leaf value, but with multiple roots, you can end up with multiple "left:1" one after another, such break the tree.

I'm currently using a solution completely not related to SQL. I re-arranged them in my C# code, but I'm just wondering if there is a way to do it with SQL and such save the time I spent on the web server

One last question. If I have a filter and it filters out some data out of the tree, how do you deal with it?

Say

  • Task 1 (In progress)
    • Task 2 (Active)
      • Task 3 (Active)

If the filter is to show all tree with status "Active", what do you do?

Paŭlo Ebermann
  • 73,284
  • 20
  • 146
  • 210
Liming
  • 1,641
  • 3
  • 28
  • 38

1 Answers1

7

"Multiple roots" simply means that you're starting at the first level and omitting the "true" root altogether. So,

 Root1 (1, 4)
   Node1 (2, 3)

 Root2 (5, 12)
   Node21 (6, 7)
   Node22 (8, 11)
     Node221 (9, 10)

Do NOT restart the sequence on left / right indexes; you'd be walking into a world of hurt.

As far as your filter question goes, it's purely a matter of presentation. There are different ways to handle this; the one I used in the past was to show all nodes in the path leading to the node that satisfies your filter criteria but highlight "filtered out" nodes differently and make them non-actionable (e.g. can't be selected in UI, operations can't be performed on them, etc...). Something like:

Task 1 (In progress) [greyed out, inactive]
 +Task 2 (Active)
  +Task 3 (Active)

Another approach is to use grid / tree combo to display filter results where path to the node is shown flattened but nodes under the node (if any) are shown as tree. Something like:

Task1 -> Task 2 (Active)
 + Task 3 (Active)
Task1 -> Task 4 -> Task 6 (Active)
 + Task 7 (Active)
ChssPly76
  • 99,456
  • 24
  • 206
  • 195
  • ChssPly76! I really dig your thoughts on the filter, that makes a whole lot sense!!! I never thought of it that way. I think usability wise, your first approach makes the most sense to non-technical users, I will do exactly what you suggested. Thanks a bunch! Regards to your approach to multiple roots, while doing the select, it works out great, but what if you add two more new node under node1(2,3)? so it should be Node1 (1,8) ..Node1(2,7) ....Node1(3,4) ....Node1(5,6) We are getting duplicated values? – Liming Nov 21 '09 at 01:35
  • You're welcome :-) I didn't quite understand what you meant by "duplicates" - if you were to add another node(s) under Node1 then **all** left / right indexes greater than left index of the parent node (e.g. Node1) would have to change. Don't think of those "roots" as entirely separate; that's not the way nested sets model is designed to operate. Think of them as regular nodes that are under some "invisible master node". All operations (adding / moving / deleting a note) still apply to and (potentially) affect all nodes. – ChssPly76 Nov 21 '09 at 01:46
  • Thanks again ChssPly76. My brain didn't come around earlier. I see what you are saying now. Finally I can finish it this weekend! – Liming Nov 21 '09 at 04:05