An algorithm for implementing infinite categorical left and right values

First, Introduction

Product classification, multi-level tree structure of the forum, mailing lists and many other places we all encounter this problem: how to store multi-level structure of data? In the application of PHP, it is usually a relational database that provides back-end data storage, which can save large amount of data and provide efficient data retrieval and update service. However, the basic form of relational data is a criss-cross table, and it is a planar structure, so if we want to store the multilevel tree structure in the relational database, we need to make a reasonable translation work. Next, I will look at what I saw and see and some practical experience and everyone to discuss:
There are basically two common design methods for hierarchical data storage in a flat database:
* Adjacent directory mode (adjacency list model)
* Pre-sorted traversal tree algorithm (modified preorder tree traversal algorithm)

Second, the model

Here I use a simple food catalogue as our sample data.
Our data structure is this, the following is the code:

Food||---Fruit|    ||    |---Red|    |    ||    |    |--Cherry|    ||    +---Yellow|          ||          +--Banana|+---Meat      |--Beef      +--Pork

In order to take care of those English mess PHP enthusiasts

Food  : 食物Fruit : 水果Red   : 红色Cherry: 樱桃Yellow: 黄色Banana: 香蕉Meat  : 肉类Beef  : 牛肉Pork  : 猪肉

Third, the realization

1. Adjacent directory mode (adjacency list model)

This pattern we often use, a lot of tutorials and books are also introduced. We represent the parent node of the node by adding a property parent to each node to describe the entire tree structure through a flat table. According to this principle, the data in the example can be converted into the following table:
Here's the code:

+-----------------------+|   parent |    name    |+-----------------------+|          |    Food    ||   Food   |   Fruit    ||   Fruit  |    Green   ||   Green  |    Pear    ||   Fruit  |    Red     ||   Red    |    Cherry  ||   Fruit  |    Yellow  ||   Yellow |    Banana  ||   Food   |    Meat    ||   Meat   |    Beef    ||   Meat   |    Pork    |+-----------------------+

We see that Pear is a child of green, and green is a sub-node of fruit. and the root node ' food ' has no parent node. To simply describe the problem, this example uses only name to represent a record. In the actual database, you need to use the number ID to mark each node, the database table structure should probably like this: ID, parent_id, name, Descrīption.
With such a table we can save the entire multilevel tree structure through the database.

Show multi-level tree, if we need to display such a multilevel structure requires a recursive function .
Here's the code:

!--? PHP  //$parent is the parent of the children we want to see  //$level is increased if we go deeper into the tree,  //used to display a nice indented tree    function   display_children   ( $parent ,  $level )  { //Get all child nodes $parent of a parent node   $result  = mysql_query ( "SELECT name From tree WHERE parent = ' ".  $parent .     '; '     ); //Show each child node   while  ( $row  = mysql_fetch_array ( $result )) {//Indent display node name   echo  str_repeat ( ",  $level ).  $row  [ ' name ' ].         "\ n" ; //Call this function again to show child nodes of child nodes  Display_children ( $row  [ ' name ' ],  $level  +<    Span>1 ); }} ?  

Using this function on the root node (food) of the whole structure can print out the entire multilevel tree structure, because food is the root node of its parent node is empty, so call: Display_children (", 0). The contents of the entire tree are displayed:

Food    Fruit        Red            Cherry        Yellow            Banana    Meat        Beef        Pork

If you only want to show part of the whole structure, such as the fruit part, you can call it this way: Display_children (' Fruit ', 0);

With almost the same method, we can know the path from the root node to any node. For example, Cherry's path is "food >; Fruit >; Red ". In order to get such a path we need to start with the deepest level of "Cherry", query to get its parent node "red" to add it to the path, then we query Red's parent node and add it to the path, and so on to the top of the "food", the following is the code:

    //$node is the deepest node functionget_path($node) {//Query the parent node of this node$result= mysql_query ("Select parent from tree WHERE name = '".$node."'        ;");$row= Mysql_fetch_array ($result);//Save the path with an array$path=Array();//If it is not the root node, continue to query up//(root node does not have parent node)if($row[' Parent '] !='') {//The last part of the path to $node, is the name// of the parent of $node$path[] =$row[' Parent '];//We should add the path to the parent of this node// to the path$path= Array_merge (Get_path ($row[' Parent ']),$path); }//Return the pathreturn$path;}?>

If you use this function for "Cherry": Print_r (Get_path (' Cherry ')), you will get an array like this:

Array (    [0] => Food    [1] => Fruit    [2] => Red)

Then how to print it into the format you want, is your thing.

Disadvantages:
This method is simple, easy to understand and good to get started. But there are some drawbacks. Mainly because the running speed is very slow, because each node needs to make a database query, the large amount of data to make a lot of queries to complete a tree. In addition, due to recursive operation, each level of recursion needs to occupy some memory, so the efficiency of space utilization is also relatively low.

2. Pre-sorted traversal tree algorithm

Now let's take a look at another faster method that does not use recursive calculations, which is the pre-sort traversal tree algorithm (modified preorder tree traversal algorithm)
This method may be less contact with the first use is not as easy to understand as the above method, but because this method does not use recursive query algorithm, there is a higher query efficiency.

We first draw the multilevel data on the paper in the following way, write 1 on the left side of food on the root node and then continue down the tree on the left side of the Fruit to write 2 and then move on, along the edge of the tree to each node labeled with the left and right numbers. The last number is labeled 18 on the right side of food. In this diagram below you can see the entire multilevel structure labeled with the numbers. (Not read?) Use your finger pointing at the number from 1 to 18 to see what's going on. Do not understand, count again, pay attention to move your fingers).
These numbers indicate the relationship between the nodes, and the "Red" number is 3 and 6, which is the descendant node of "food" 1-18. Again, we can see that all nodes with an lvalue greater than 2 and a right value of less than 11 are descendants of "Fruit" 2-11.
Here's the code:

118|            +------------------------------+            |                              |2111217|                              |    +-------------+                 +------------+    |             |                 |            |3671013141516|             |4589

This allows the entire tree structure to be stored in the database with left and right values. Before we go on, let's take a look at the data tables we've compiled below.
Here's the code:

+----------+------------+-----+-----+|  parent  |    name    | lft | rgt |+----------+------------+-----+-----+|          |    Food    | 1   | 18  ||   Food   |   Fruit    | 2   | 11  ||   Fruit  |    Red     | 3   |  6  ||   Red    |    Cherry  | 4   |  5  ||   Fruit  |    Yellow  | 7   | 10  ||   Yellow |    Banana  | 8   |  9  ||   Food   |    Meat    | 12  | 17  ||   Meat   |    Beef    | 13  | 14  ||   Meat   |    Pork    | 15  | 16  |+----------+------------+-----+-----+

Note: Since "left" and "right" have special meanings in SQL, we need to use "LfT" and "RGT" to represent the field. In addition, the "Parent" field is no longer required in this structure to represent a tree structure. This means that the following table structure is sufficient.
Here's the code:

+------------+-----+-----+|    name    | lft | rgt |+------------+-----+-----+|    Food    | 1   | 18  ||    Fruit   | 2   | 11  ||    Red     | 3   |  6  ||    Cherry  | 4   |  5  ||    Yellow  | 7   | 10  ||    Banana  | 8   |  9  ||    Meat    | 12  | 17  ||    Beef    | 13  | 14  ||    Pork    | 15  | 16  |+------------+-----+-----+

Okay, now we can get the data from the database, for example, we need to get all the nodes under "Fruit" to write the query statement:

SELECT * FROM tree WHERE lft BETWEEN 2AND11;

This query has the following results.
Here's the code:

+------------+-----+-----+|    name    | lft | rgt |+------------+-----+-----+|    Fruit   | 2   | 11  ||    Red     | 3   |  6  ||    Cherry  | 4   |  5  ||    Yellow  | 7   | 10  ||    Banana  | 8   |  9  |+------------+-----+-----+

See, as long as a query can get all these nodes. To be able to display the entire tree structure like the recursive function above, we also need to sort the query. To sort by the left value of the node:

SELECT * FROM tree WHERE lft BETWEEN 2AND11ORDERBY lft ASC;

The remaining question is how to show the level indentation.
Here's the code:

     functiondisplay_tree($root) {//Get the left and right values of the root node$result= mysql_query ("Select LfT, RGT from tree WHERE name = '".$root."'        ;");$row= Mysql_fetch_array ($result);//Prepare an empty right-value stack$right=Array();//Get all descendant nodes of the base point$result= mysql_query ("SELECT Name, LfT, RGT from tree WHERE lft between '".$row[' LfT '] ."' and '".$row[' RGT '] ."' ORDER by LfT ASC;");//Show each line while($row= Mysql_fetch_array ($result)) {//Only check stack if there is oneif(Count ($right) >0) {//Check if we should move the node out of the stack while($right[Count ($right) -1] <$row[' RGT ']) {Array_pop ($right); }        }//indent the name of the display nodeEchoStr_repeat ('  ', COUNT ($right)) .$row[' name '] ."\ n";//Add this node to the stack$right[] =$row[' RGT ']; }}?>

If you run the above function, you will get the same result as the recursive function . Just this new function of ours may be faster because there are only 2 database queries.
It is even easier to know the path of a node, if we want to understand the path of cherry and use its left and right values of 4 and 5来 to do a query.

SELECTFROMWHERE4AND5ORDERBYASC;

This gives you the following results:
Here's the code:

+------------+|    name    |+------------+|    Food    ||    Fruit   ||    Red     |+------------+

So how many descendant nodes does a node have? Very simple, descendants total = (Rvalue-lvalue-1)/2

descendants = (rightleft12

Don't believe it? I counted it.
With this simple formula, we can quickly figure out that the "Fruit 2-11" node has 4 descendant nodes, and the "Banana 8-9" node has no descendant nodes, which means it is not a parent node.
It's amazing, isn't it? Although I've used this method many times, I feel amazing every time I do it.
This is a really good idea, but what can be done to help us build a data table with the right and left values? Here we introduce a function that can automatically convert a table of name and parent structure to a data table with left and right values.
Here's the code:

    functionrebuild_tree($parent, $left) {//The right value of this node was the left value + 1$right=$left+1;//Get all children of this node$result= mysql_query ("SELECT name from tree WHERE parent = '".$parent."'        ;"); while($row= Mysql_fetch_array ($result)) {//Recursive execution of this function for each// child of this node//$right is the current right value, which is//incremented by the Rebuild_tree function$right= Rebuild_tree ($row[' name '],$right); }//We ' ve got the left value, and now that we ' ve processed//The children of this node we also know the right valuemysql_query ("UPDATE tree SET lft = '".$left."', rgt= '".$right."' WHERE name = '".$parent."'        ;");//Return the right value of this node + 1return$right+1;}?>

Of course this function is a recursive function , we need to run this function from the root node to reconstruct a tree with left and right values

rebuild_tree('Food',1);

This function may seem a bit complicated, but its function is the same as manually numbering the table, which is to convert the stereoscopic multilayer structure into a data table with left and right values.

So how do we add, update, and delete a node for such a structure?
There are two ways to add a node:
First, retain the original name and parent structure, add data to the data using the old method, and use the Rebuild_tree function to renumber the entire structure once each additional piece of data is added.
The second, more efficient approach is to change all values on the right side of the new node. For example: We want to add a new fruit "strawberry" (strawberry) It will be the last child node of the "Red" node. First we need to make some room for it. The value of "Red" should be changed from 6 to 8, and the left and right values of "Yellow 7-10" should be changed to 9-12. And so we can tell that if you want to make room for the new value, you need to give all nodes with a value greater than 5 (5 is the right value of the last child node of "Red") plus 2. So we do database operations like this:

UPDATE tree SET rgt = rgt + 2WHERE rgt > 5;UPDATE tree SET lft = lft + 2WHERE lft > 5;

This frees up space for the newly inserted value and now creates a new data node in the vacated space, with the left and right values of 6 and 7, respectively.

INSERTINTO tree SET lft=6, rgt=7, name='Strawberry';

Let's do another check! What do you think? It's pretty quick.

Iv. Conclusion

Well, now you can design your multi-level database structure in two different ways, depending on your personal judgment, but I prefer the second approach to a large number of hierarchies. The first method is easier if the query volume is small but the data needs to be added and updated frequently.
In addition, if the database is supported, you can also write Rebuild_tree () and free space operations as a trigger function on the database side, which is executed automatically when inserting and updating, which makes the operation more efficient, and the SQL statement that you add the new node becomes simpler.

Http://www.cnblogs.com/guowei1027/archive/2009/12/14/1623507.html

The above describes the infinite classification of the value of the implementation of the algorithm, including the aspects of the content, I hope that the PHP tutorial interested in a friend helpful.

