You can think this question as follow-up for that one: Sorting a subtree in a closure table hierarchical-data structure
Let's consider the modified example (with a new row called rating in category table):
--
-- Table `category`
--
CREATE TABLE IF NOT EXISTS `category` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) COLLATE utf8_czech_ci NOT NULL,
`rating` int(11) NOT NULL,
`active` tinyint(1) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
INSERT INTO `category` (`id`, `name`, `rating`, `active`) VALUES
(1, 'Cat 1', 0, 1),
(2, 'Cat 2', 0, 1),
(3, 'Cat 1.1', 0, 1),
(4, 'Cat 1.1.1', 2, 1),
(5, 'Cat 2.1', 0, 1),
(6, 'Cat 1.2', 2, 1),
(7, 'Cat 1.1.2', 3, 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);
Thanks to Bill Karwin, i am able to sort my data based on numeric order of id's with following query:
SELECT c2.*, cc2.ancestor AS `_parent`,
GROUP_CONCAT(breadcrumb.ancestor ORDER BY breadcrumb.depth DESC) AS breadcrumbs
FROM category AS c1
JOIN category_closure AS cc1 ON (cc1.ancestor = c1.id)
JOIN category AS c2 ON (cc1.descendant = c2.id)
LEFT OUTER JOIN category_closure AS cc2 ON (cc2.descendant = c2.id AND cc2.depth = 1)
JOIN category_closure AS breadcrumb ON (cc1.descendant = breadcrumb.descendant)
WHERE c1.id = 1/*__ROOT__*/ AND c1.active = 1
GROUP BY cc1.descendant
ORDER BY breadcrumbs;
+----+------------+--------+---------+-------------+
| id | name | active | _parent | breadcrumbs |
+----+------------+--------+---------+-------------+
| 1 | Cat 1 | 1 | NULL | 1 | Rating: 0
| 3 | Cat 1.1 | 1 | 1 | 1,3 | Rating: 0
| 4 | Cat 1.1.1 | 1 | 3 | 1,3,4 | Rating: 2
| 7 | Cat 1.1.2 | 1 | 3 | 1,3,7 | Rating: 3
| 6 | Cat 1.2 | 1 | 1 | 1,6 | Rating: 2
+----+------------+--------+---------+-------------+
So far so good, now i want to sort this results using rating row from category table. It should be like this:
+----+------------+--------+---------+-------------+
| id | name | active | _parent | breadcrumbs |
+----+------------+--------+---------+-------------+
| 1 | Cat 1 | 1 | NULL | 1 | Rating: 0
| 6 | Cat 1.2 | 1 | 1 | 1,6 | **Rating: 2**
| 3 | Cat 1.1 | 1 | 1 | 1,3 | Rating: 0
| 7 | Cat 1.1.2 | 1 | 3 | 1,3,7 | **Rating: 3**
| 4 | Cat 1.1.1 | 1 | 3 | 1,3,4 | **Rating: 2**
+----+------------+--------+---------+-------------+
So all data should have both breadcrumbs ASC and rating DESC order without breaking the hierarchy. Is this possible with one query? Is this even possible?
Thanks.
UPDATE:
Here is what i've tried so far based on Bill's answer's second part:
SELECT c2.*, cc2.ancestor AS `_parent`,
GROUP_CONCAT(c2.rating ORDER BY breadcrumb.depth DESC) AS breadcrumbs
FROM category AS c1
JOIN category_closure AS cc1 ON (cc1.ancestor = c1.id)
JOIN category AS c2 ON (cc1.descendant = c2.id)
LEFT OUTER JOIN category_closure AS cc2 ON (cc2.descendant = c2.id AND cc2.depth = 1)
JOIN category_closure AS breadcrumb ON (cc1.descendant = breadcrumb.descendant)
WHERE c1.id = 1/*__ROOT__*/ AND c1.active = 1
GROUP BY cc1.descendant
ORDER BY breadcrumbs;
+----+------------+--------+---------+-------------+
| id | name | active | _parent | breadcrumbs |
+----+------------+--------+---------+-------------+
| 7 | Cat 1.1.2 | 1 | 3 | 3,3,3 | **Rating: 3**
| 6 | Cat 1.2 | 1 | 1 | 2,2 | **Rating: 2**
| 4 | Cat 1.1.1 | 1 | 3 | 2,2,2 | **Rating: 2**
| 1 | Cat 1 | 1 | NULL | 0 | Rating: 0
| 3 | Cat 1.1 | 1 | 1 | 0,0 | Rating: 0
+----+------------+--------+---------+-------------+
Also please be mind that rating values can be SIGNED (negative) as well.
POSSIBLE ANSWER:
Not working with 2 roots, check the comments.
SELECT c2.*, cc2.ancestor AS `_parent`,
GROUP_CONCAT(999-c3.rating ORDER BY breadcrumb.depth DESC) AS breadcrumbs
FROM category AS c1
JOIN category_closure AS cc1 ON (cc1.ancestor = c1.id)
JOIN category AS c2 ON (cc1.descendant = c2.id)
LEFT OUTER JOIN category_closure AS cc2 ON (cc2.descendant = c2.id AND cc2.depth = 1)
JOIN category_closure AS breadcrumb ON (cc1.descendant = breadcrumb.descendant)
JOIN category AS c3 ON (breadcrumb.ancestor = c3.id)
WHERE c1.id = 1/*__ROOT__*/ AND c1.active = 1
GROUP BY cc1.descendant
ORDER BY breadcrumbs;
I believe that this is the query that you need/want. Since you have no PARENT_ID column in the category table I first get all root items from the closure and then find all of their children, ordering by a modified breadcrumb in which the last item is not the ID of the current leaf but its rating instead. So you get reverse sorting by rating while still keeping the hierarchy levels.
SELECT category.id,name,rating,
(SELECT GROUP_CONCAT(CONCAT(LPAD(1000 - rating, 5, "0"), "#", ancestor) ORDER BY depth DESC)
FROM category_closure LEFT JOIN category AS cat ON ancestor = cat.id WHERE descendant = category.id
) AS sorting
FROM category_closure
LEFT JOIN category ON descendant = category.id
WHERE ancestor IN
(SELECT ancestor FROM category_closure AS c1
WHERE depth = 0
AND NOT EXISTS(SELECT 1 FROM category_closure AS c2
WHERE c2.descendant = c1.descendant AND depth > 0)
)
ORDER BY sorting