Geek Answers Handbook

How to request a nested set model with multiple roots and filtering

How do you request a nested set model with multiple roots, such trees in the same table? Currently, I have added an extra column called "Root" indicating the identifier of the root node for all nodes of the subtree, however I cannot calculate sql to get them in the correct order

I mean the article Hierarchical Data Management in MySQL .

Typically, a query to retrieve items in order is in order the value of the left sheet, but with several roots, you can get several "left: 1" one after another to split the tree.

I am currently using a solution completely non-SQL related. I redid them into my C # code, but I'm just wondering if there is a way to do this using SQL and thus save the time I spent on the web server.

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

Tell me

  • Task 1 (in progress)
    • Task 2 (active)
      • Task 3 (active)

If the filter should show the whole tree with the status "Active", what do you do?

+4
source share
1 answer

“Multiple Roots” simply means that you start from the first level and completely eliminate the “true” root. Thus,

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

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

Do NOT restart the sequence left / right; you will fall into a world of pain.

Regarding the filter question, it's just a presentation question. There are various ways to handle this; the one I used in the past was to show all the nodes in the path leading to a node that meets your filter criteria, but differentiates the “filtered” nodes and makes them inactive (for example, it may not be selected in the user interface, operations on them cannot be performed, etc.). Sort of:

  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 the path to the node is shown flattened, but the nodes under the node (if any) are shown as a tree. Sort of:

  Task1 -> Task 2 (Active)
  + Task 3 (Active)
 Task1 -> Task 4 -> Task 6 (Active)
  + Task 7 (Active)
+7
source

More articles:


All Articles