I have a MySQL database holding hierarchical data using the Closure Table method. A simple sample database create script follows the question. My issue at the moment is how do I pull data out of the database in the correct order? I'm currently using the following select statement.
SELECT `TreeData`.`iD`, `TreeData`.`subsectionOf`,
CONCAT(REPEAT('-', `TreePaths`.`len`),`TreeData`.`name`),
`TreePaths`.`len`,`TreePaths`.`ancestor`,`TreePaths`.`descendant`
FROM `TreeData`
LEFT JOIN `TreePaths` ON `TreeData`.`iD` = `TreePaths`.`descendant`
WHERE `TreePaths`.`ancestor` = 1
ORDER BY `TreeData`.`subsectionOrder`
It pulls the correct information but not in the correct order.
The sample database create script with sample data.
-- Simple Sample
SET FOREIGN_KEY_CHECKS=0;
DROP TRIGGER IF EXISTS Tree_Insert;
DROP TRIGGER IF EXISTS Tree_Update;
DROP TABLE IF EXISTS TreePaths;
DROP TABLE IF EXISTS TreeData;
SET FOREIGN_KEY_CHECKS=1;
CREATE TABLE `TreeData` (
`iD` INT NOT NULL, -- PK
`subsectionOf` INT, -- Parent ID & FK
`subsectionOrder` INT, -- Oder of Subsections
`name` NVARCHAR(500) NOT NULL, -- Name for the entry
PRIMARY KEY (`iD`),
FOREIGN KEY (`subsectionOf`) REFERENCES TreeData(`iD`) ON DELETE CASCADE,
INDEX(`name`)
) ENGINE = MYISAM;
-- Trigger to update the EntryPaths table for new entries
DELIMITER //
CREATE TRIGGER `Tree_Insert` AFTER INSERT ON `TreeData` FOR EACH ROW
BEGIN
INSERT INTO `TreePaths` (`ancestor`, `descendant`, `len`)
SELECT `ancestor`, NEW.`iD`, len + 1 FROM `TreePaths`
WHERE `descendant` = NEW.`subsectionOf`
UNION ALL SELECT NEW.`iD`, NEW.`iD`, 0;
END; //
DELIMITER ;
DELIMITER //
CREATE TRIGGER `Tree_Update` BEFORE UPDATE ON `TreeData` FOR EACH ROW
BEGIN
-- From http://www.mysqlperformanceblog.com/2011/02/14/moving-subtrees-in-closure-table/
IF OLD.`subsectionOf` != NEW.`subsectionOf` THEN
-- Remove the node from its current parent
DELETE a FROM `TreePaths` AS a
JOIN `TreePaths` AS d ON a.`descendant` = d.`descendant`
LEFT JOIN `TreePaths` AS x
ON x.`ancestor` = d.`ancestor` AND x.`descendant` = a.`ancestor`
WHERE d.`ancestor` = OLD.`iD` AND x.`ancestor` IS NULL;
-- Add the node to its new parent
INSERT `TreePaths` (`ancestor`, `descendant`, `len`)
SELECT supertree.`ancestor`, subtree.`descendant`, supertree.`len`+subtree.`len`+1
FROM `TreePaths` AS supertree JOIN `TreePaths` AS subtree
WHERE subtree.`ancestor` = OLD.`iD`
AND supertree.`descendant` = NEW.`subsectionOf`;
END IF;
END; //
DELIMITER ;
CREATE TABLE `TreePaths` (
`ancestor` INT NOT NULL,
`descendant` INT NOT NULL,
`len` INT NOT NULL,
PRIMARY KEY (`ancestor`, `descendant`),
FOREIGN KEY (`ancestor`) REFERENCES TreeData(`iD`) ON DELETE CASCADE,
FOREIGN KEY (`descendant`) REFERENCES TreeData(`iD`) ON DELETE CASCADE
) ENGINE = MYISAM;
INSERT INTO `TreeData` VALUES(1, NULL, NULL, 'Root A');
INSERT INTO `TreeData` VALUES(2, 1, 1, 'Item 1');
INSERT INTO `TreeData` VALUES(3, 1, 2, 'Item 2');
INSERT INTO `TreeData` VALUES(4, 1, 3, 'Item 3');
INSERT INTO `TreeData` VALUES(5, 2, 2, 'Item 1 Sub Item 2');
INSERT INTO `TreeData` VALUES(6, 2, 1, 'Item 1 Sub Item 1');
INSERT INTO `TreeData` VALUES(7, 1, 3, 'Item 4');
INSERT INTO `TreeData` VALUES(8, 4, 1, 'Item 3 Sub Item 1');
INSERT INTO `TreeData` VALUES(9, 4, 2, 'Item 3 Sub Item 2');
INSERT INTO `TreeData` VALUES(10, NULL, NULL, 'Root B');
INSERT INTO `TreeData` VALUES(11, 10, 1, 'Item A');
INSERT INTO `TreeData` VALUES(12, 10, 2, 'Item B');
INSERT INTO `TreeData` VALUES(13, 10, 3, 'Item C');
SELECT d.`iD`, d.`subsectionOf`,
CONCAT(REPEAT('-', p.`len`), d.`name`) as hier,
p.`len`, p.`ancestor`, p.`descendant`,
GROUP_CONCAT(crumbs.`ancestor`) AS breadcrumbs
FROM `TreeData` AS d
JOIN `TreePaths` AS p ON d.`iD` = p.`descendant`
JOIN `TreePaths` AS crumbs ON crumbs.`descendant` = p.`descendant`
WHERE p.`ancestor` = 1
GROUP BY d.`iD`
ORDER BY breadcrumbs;
+----+--------------+---------------------+-----+----------+------------+-------------+
| iD | subsectionOf | hier | len | ancestor | descendant | breadcrumbs |
+----+--------------+---------------------+-----+----------+------------+-------------+
| 1 | NULL | Root A | 0 | 1 | 1 | 1 |
| 2 | 1 | -Item 1 | 1 | 1 | 2 | 1,2 |
| 5 | 2 | --Item 1 Sub Item 2 | 2 | 1 | 5 | 1,2,5 |
| 6 | 2 | --Item 1 Sub Item 1 | 2 | 1 | 6 | 1,2,6 |
| 3 | 1 | -Item 2 | 1 | 1 | 3 | 1,3 |
| 4 | 1 | -Item 3 | 1 | 1 | 4 | 1,4 |
| 8 | 4 | --Item 3 Sub Item 1 | 2 | 1 | 8 | 1,4,8 |
| 9 | 4 | --Item 3 Sub Item 2 | 2 | 1 | 9 | 1,4,9 |
| 7 | 1 | -Item 4 | 1 | 1 | 7 | 1,7 |
+----+--------------+---------------------+-----+----------+------------+-------------+