order sql tree hierarchy
What is the best way to sort a table like this:
CREATE TABLE category(
id INT(10),
parent_id INT(10),
name VARCHAR(50)
);
INSERT INTO category (id, parent_id, name) VALUES
(1, 0, 'pizza'), --node 1
(2, 0, 'burger'), --node 2
(3, 0, 'coffee'), --node 3
(4, 1, 'piperoni'), --node 1.1
(5, 1, 'cheese'), --node 1.2
(6, 1, 'vegetariana'), --node 1.3
(7, 5, 'extra cheese'); --node 1.2.1
To sort it hierarchically by id or name :
'pizza' //node 1
'piperoni' //node 1.1
'cheese' //node 1.2
'extra cheese' //node 1.2.1
'vegetariana' //node 1.3
'burger' //node 2
'coffee' //node 3
EDIT: the number at the end of the name is to visualize the strucutre better, it is not for sorting.
EDIT 2: as mentioned several times ... the number at the end of the name "cheese 1.2 " was only for visualization purpose, NOT for sorting. I moved them as comments, too many people got confused, sorry.
By adding a path column and a trigger, this can be done fairly easily.
First add a varchar column that will contain the path from root to the node:
ALTER TABLE category ADD path VARCHAR(50) NULL;
Then add a trigger that calculates the path on insert:
(simply concats the new id with path of the parent)
CREATE TRIGGER set_path BEFORE INSERT ON category
FOR EACH ROW SET NEW.path =
CONCAT(IFNULL((select path from category where id = NEW.parent_id), '0'), '.', New.id);
Then simply select order by path:
SELECT name, path FROM category ORDER BY path;
Result:
pizza 0.1
piperoni 0.1.4
cheese 0.1.5
extra cheese 0.1.5.7
vegetariana 0.1.6
burger 0.2
coffee 0.3
See fiddle.
This way maintenance cost is also minimal. The path field is hidden when inserting and is calculated via trigger. Removing a node has no overhead, since all the children of the node are also removed. The only problem is when updating the parent_id of a node; Well, don't do that! :)
Nested Tree Sets in combination with a level column is a really nice technique for reading and sorting tree based structures. It is easy to select a sub-tree, limit the result to certain level, and do sorting in one query. But the cost for inserting and deleting entires is relatively high, so you should use it if you query your data more often then you write them and where reading performance is important. (for 50-100 the time for removing, inserting or moving elements should be no problem, even with 1000 it should not be problematic).
With each entry you store it's level and value for left and right , in the sample below it is: ( left , right , level ) if you want to select only the 1.2 with it's descendants you would do:
SELECT * FROM table WHERE left >=7 AND right <=16
if you would like to select only the children then
SELECT * FROM table WHERE left >=7 AND right <=16 AND level=2
if you want to sort you could do
SELECT * FROM table WHERE left >=7 AND right <=16 ORDER BY left
Sorting by other fields while keeping the grouping of the hierarchy could be problematic, depending on how you would like to sort.
1 (0,17,0)
|
|
+---------------+---------------------------------------+
| |
1.1 (1,6,1) 1.2 (7,16,1)
| |
+------------+-------+ +-------------------+--------+----------------+
| | | | |
1.1.1 (2,3,2) 1.1.2 (4,5,2) 1.2.1 (8,9,2) 1.2.2 (10,13,2) 1.2.2 (14,15,2)
|
|
|
1.2.2.1 (11,12,3)
Closure Table (for completion, but I would not recommend for your use case). It stores all paths in the tree and therefore the required storage space for the hierarchy will grow really fast if you have many levels.
Path Enumeration there you store the path of each element with the entry /0/ , /0/1/ querying path is easy there, but for sorting it is not that flexible.
For a small amount of entires I would use Nested Tree Sets . sadly I don't have a good reference page that describes these techniques and compares them.
if there is only 3 levels of nesting you can do something like that
SELECT c1.name FROM category as c1 LEFT JOIN category as c2
ON c1.parent_id = c2.id OR (c1.parent_id = 0 AND c1.id = c2.id)
ORDER BY c2.parent_id, c2.id, c1.id;
if you have more nesting levels it would be more tricky
for more nesting level you can write function
delimiter ~
DROP FUNCTION getPriority~
CREATE FUNCTION getPriority (inID INT) RETURNS VARCHAR(255) DETERMINISTIC
begin
DECLARE gParentID INT DEFAULT 0;
DECLARE gPriority VARCHAR(255) DEFAULT '';
SET gPriority = inID;
SELECT parent_id INTO gParentID FROM category WHERE ID = inID;
WHILE gParentID > 0 DO
SET gPriority = CONCAT(gParentID, '.', gPriority);
SELECT parent_id INTO gParentID FROM category WHERE ID = gParentID;
END WHILE;
RETURN gPriority;
end~
delimiter ;
so i now on
SELECT * FROM category ORDER BY getPriority(ID);
i have
+------+-----------+--------------------+
| ID | parent_id | name |
+------+-----------+--------------------+
| 1 | 0 | pizza 1 |
| 4 | 1 | piperoni 1.1 |
| 5 | 1 | cheese 1.2 |
| 7 | 5 | extra cheese 1.2.1 |
| 6 | 1 | vegetariana 1.3 |
| 2 | 0 | burger 2 |
| 3 | 0 | coffee 3 |
+------+-----------+--------------------+
上一篇: SQL Server 2008中的表查询
下一篇: 顺序的SQL树层次结构