Edit Question - KnowledgeHub

Title Body

I would like to ask you to help me with the problem with sorting of the hierarchical data structure stored as a closure table . I wanted to use this structure to store my website menu. Everything works fine, but the problem is that I do not know how to sort the exact subtree in a custom order. At the moment the tree is sorted in the order in which the items were added to the database. My structure is based on Bill Karwin's article about Closure Tables and some other posts. Here is my MySQL database structure with some DEMO data: -- -- Table `category` -- CREATE TABLE IF NOT EXISTS `category` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) COLLATE utf8_czech_ci NOT NULL, `active` tinyint(1) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB; INSERT INTO `category` (`id`, `name`, `active`) VALUES (1, 'Cat 1', 1), (2, 'Cat 2', 1), (3, 'Cat 1.1', 1), (4, 'Cat 1.1.1', 1), (5, 'Cat 2.1', 1), (6, 'Cat 1.2', 1), (7, 'Cat 1.1.2', 1); -- -- Table `category_closure` -- CREATE TABLE IF NOT EXISTS `category_closure` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `ancestor` int(11) DEFAULT NULL, `descendant` int(11) DEFAULT NULL, `depth` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `fk_category_closure_ancestor_category_id` (`ancestor`), KEY `fk_category_closure_descendant_category_id` (`descendant`) ) ENGINE=InnoDB; INSERT INTO `category_closure` (`id`, `ancestor`, `descendant`, `depth`) VALUES (1, 1, 1, 0), (2, 2, 2, 0), (3, 3, 3, 0), (4, 1, 3, 1), (5, 4, 4, 0), (7, 3, 4, 1), (8, 1, 4, 2), (10, 6, 6, 0), (11, 1, 6, 1), (12, 7, 7, 0), (13, 3, 7, 1), (14, 1, 7, 2), (16, 5, 5, 0), (17, 2, 5, 1); Here is my SELECT query for one tree: SELECT c2.*, cc2.ancestor AS `_parent` FROM category AS c1 JOIN categor

Tags (comma-separated) Cancel