Mysql, functions for tree-structure
Question by user809829
I have a tabel containing a column named parent which is able to store a parent ID.
This makes it possible to create a tree-structure of my data.
Are there any good helper-functions for travelling through such a tree-structure in MySQL?
For example, if I have a row in my table and I want to be able to retreive all “parents” above it. So getting the it’s parent’s parent ID and so on…
Answer by cctan
Copied from this popular link:
CREATE TABLE nested_category (
category_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
lft INT NOT NULL,
rgt INT NOT NULL
);
INSERT INTO nested_category VALUES(1,'ELECTRONICS',1,20),(2,'TELEVISIONS',2,9),(3,'TUBE',3,4),
(4,'LCD',5,6),(5,'PLASMA',7,8),(6,'PORTABLE ELECTRONICS',10,19),(7,'MP3 PLAYERS',11,14),(8,'FLASH',12,13),
(9,'CD PLAYERS',15,16),(10,'2 WAY RADIOS',17,18);
SELECT * FROM nested_category ORDER BY category_id;
+-------------+----------------------+-----+-----+
| category_id | name | lft | rgt |
+-------------+----------------------+-----+-----+
| 1 | ELECTRONICS | 1 | 20 |
| 2 | TELEVISIONS | 2 | 9 |
| 3 | TUBE | 3 | 4 |
| 4 | LCD | 5 | 6 |
| 5 | PLASMA | 7 | 8 |
| 6 | PORTABLE ELECTRONICS | 10 | 19 |
| 7 | MP3 PLAYERS | 11 | 14 |
| 8 | FLASH | 12 | 13 |
| 9 | CD PLAYERS | 15 | 16 |
| 10 | 2 WAY RADIOS | 17 | 18 |
+-------------+----------------------+-----+-----+
You can use this to find all parents from node FLASH:
Retrieving a Single Path
With the nested set model, we can retrieve a single path without
having multiple self-joins:
SELECT parent.name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.name = 'FLASH'
ORDER BY node.lft;
+----------------------+
| name |
+----------------------+
| ELECTRONICS |
| PORTABLE ELECTRONICS |
| MP3 PLAYERS |
| FLASH |
+----------------------+
This works because the child’s left will be in between its parents’ left and right.
You can read further or search for modified preorder tree traversal.
Answer by Starx
Please read this “Hierarchical queries in MySQL” article, for in depth explanation on the topic.
But still I would like to keep things simple and create a recursive PHP function instead.
But after reading few articles, I found that best way regarded for this, is Modified Preorder Tree Traversal, which has been further explained in this article.
