Query for updating a node in a hierarchial tree structure
However once have in memory the tree data the application need a way to save them for future use.
SQL database can be a good solution but, in this case, we need a way to store data in efficient mode. IMPORTANT NOTE: Please note that I'm not the original author of this article.
Try picturing our electronics categories this way: Notice how our hierarchy is still maintained, as parent categories envelop their children.
For example, when showing the PORTABLE ELECTRONICS category, we will want to show MP3 PLAYERS, CD PLAYERS, and 2 WAY RADIOS, but not FLASH.
This can be easily accomplished by adding a HAVING clause to our previous query: SELECT node.name, (COUNT(parent.name) - (sub_tree.depth 1)) AS depth FROM nested_category AS node, nested_category AS parent, nested_category AS sub_parent, ( SELECT node.name, (COUNT(parent.name) - 1) AS depth FROM nested_category AS node, nested_category AS parent WHERE BETWEEN AND AND = 'PORTABLE ELECTRONICS' GROUP BY ORDER BY )AS sub_tree WHERE BETWEEN AND AND BETWEEN sub_AND sub_AND sub_= sub_GROUP BY HAVING depth CREATE TABLE product( product_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(40), category_id INT NOT NULL ); INSERT INTO product(name, category_id) VALUES('20" TV',3),('36" TV',3), ('Super-LCD 42"',4),('Ultra-Plasma 62"',5),('Value Plasma 38"',5), ('Power-MP3 5gb',7),('Super-Player 1gb',8),('Porta CD',9),('CD To go!
What I would like to focus on in this article is a different approach, commonly referred to as the Nested Set Model.
In the Nested Set Model, we can look at our hierarchy in a new way, not as nodes and lines, but as nested containers.
The deletion of a node is based off of the lft and rght values of the entity.