I'm not going to go into what the nested set model is, I'm assuming that since you are here you know something about it. If not then I recommend the de facto blog here: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/
Data StructuresFirst step is we need to define a data structure for our nodes. It will contain the id, name, and list of child nodes. You'll notice I set the properties for left and right to internal. This is because there is no value giving users access to these properties so they should only be accessible within the project.
Okay this looks good.
Public MethodNext we can create the public method to open a connection to the database, load the data into a list, then pass that list off to a recursive function called "BuildNodes()" that will generate the node hierarchy, which we will then return. Notice we are sorting the results the by left value in the SQL command.
The last step is the recursive method to step through the list and convert it to a tree structure. The trick here is to track 3 values, the row index and then the left and right values of the node in that row index. The flow is hierarchy
- If a leaf node (a node without children) then return the node.
- If an internal node (a node with children) then
- Continue to step through the list and add children while the child's right value is less than the node's right value.
This will build up the hierarchy converted from a list. It assumes that the left and right values are continuous and there are no numbers missing. If your list filters the tree and there are gaps in the left and right values then good luck and let me know what you find.