Managing Hierarchical Data in MySQL

Exemple de generare categoriilor ierarhic din SQL

exemplu:

LOCK TABLE nested_category WRITE;

SELECT @myRight := rgt FROM nested_category
WHERE name = ‘TELEVISIONS’;

UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft + 2 WHERE lft > @myRight;

INSERT INTO nested_category(name, lft, rgt) VALUES(‘GAME CONSOLES’, @myRight + 1, @myRight + 2);

UNLOCK TABLES;

SELECT CONCAT( REPEAT(‘ ‘, COUNT(parent.name) – 1), node.name) AS name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;

+———————–+
| name |
+———————–+
| ELECTRONICS |
| -TELEVISIONS |
| – -TUBE |
| – -LCD |
| – -PLASMA |
| -PORTABLE ELECTRONICS |
| – -MP3 PLAYERS |
| – -FLASH |
| –CD PLAYERS |
| –2 WAY RADIOS |
+———————–+

Soursa: aici

Leave a Reply