How to Query Nested Set model with multiple roots plus filtering

Question

Hello All,

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 this article

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?

Thanks a lot

Answer 1

"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)