У меня есть такая таблица:
CREATE TABLE `category` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`parent_id` int(10) UNSIGNED DEFAULT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `parent_id` (`parent_id`),
CONSTRAINT `parent` FOREIGN KEY (`parent_id`) REFERENCES `category` (`id`)
)
И есть рекурсивный запрос, для того что-бы получить древовидную структуру за один запрос:
WITH RECURSIVE category_path AS
(
SELECT
c.parent_id,
c.id,
CONVERT(c.id, CHAR(1000)) AS ids
FROM category AS c
WHERE parent_id IS NULL
UNION ALL
SELECT
c.parent_id,
c.id,
CONCAT(p.ids, ',', c.id) AS ids
FROM category AS c
JOIN category_path AS p ON p.id = c.parent_id
)
SELECT
category_path.*
FROM category_path
ORDER BY category_path.ids
Результат такой (точками вырезанные строки):
parent id ids
null 1 1
1 190 1,190
....
1 2 1,2
2 103 1,2,103
....
2 3 1,2,3
...
Мне необходимо отсортировать этот результат по полю ids. В примере результата видно проблемы такой сортировки:
- Она не сортирует каждый "узел" по порядку
- Сортируются не числа а строки. Т.е. 190 будет выше чем 2
В MySQL есть функция FIND_IN_SET она ищет индекс указанного узла по его значению.
Меня интересует обратная функция. Т.е. извлечение значение по его индексу. Есть ли простой способ это сделать?
Пока что я нашёл 2 варианта это сделать.
Первый вариант такой:
SUBSTRING_INDEX(SUBSTRING_INDEX(ids, ',', 1), ',', -1) + 0 ASC,
SUBSTRING_INDEX(SUBSTRING_INDEX(ids, ',', 2), ',', -1) + 0 ASC,
SUBSTRING_INDEX(SUBSTRING_INDEX(ids, ',', 3), ',', -1) + 0 ASC,
#...
У этого варианта есть проблема, например если для строки "1,2,103" попытаться получить значение по индексу 5 то он вернёт 103 и для любого индекса больше 3 будет возвращено значение последней колонки.
Я немного подумал и сделал вариант у которого нет этой проблемы:
REGEXP_REPLACE(category_path.ids, '^((([0-9]+,){0})([0-9]+)((,[0-9]+)*)|.*)$','\\4') + 0,
REGEXP_REPLACE(category_path.ids, '^((([0-9]+,){1})([0-9]+)((,[0-9]+)*)|.*)$','\\4') + 0,
REGEXP_REPLACE(category_path.ids, '^((([0-9]+,){2})([0-9]+)((,[0-9]+)*)|.*)$','\\4') + 0,
#...
Но это всё равно слишком сложное и не очевидное решение т.к. использует регулярки.
И у него тоже есть недостаток. Чтобы отсортировать длинные "ветви" нужно написать десятки полей в ORDER BY
Пока писал вопрос у меня появилась идея которая решила мою проблему с сортировкой другим способом.
Для колонки ids я дополнил id каждого узла нулями до 10 символов (у вас может быть другое, всё зависит от типа, для INT(10) UNSIGNED это 10). И увеличил максимальную длину строки для этого поля (CHAR(11000)), так оно будет корректно работать для деревьев с максимальной вложенностью до 1000.
Вот такой запрос получился:
WITH RECURSIVE category_path AS
(
SELECT
c.parent_id,
c.id,
CONVERT(LPAD(c.id, 11, '0'), CHAR(11000)) AS ids
FROM category AS c
WHERE parent_id IS NULL
UNION ALL
SELECT
c.parent_id,
c.id,
CONCAT(p.ids, ',', LPAD(c.id, 11, '0')) AS ids
FROM category AS c
JOIN category_path AS p ON p.id = c.parent_id
)
SELECT category_path.*
FROM category_path
ORDER BY category_path.ids;
Теперь результат правильно сортируется, не нужно прописывать сортировки для каждого узла по порядку в колонке ids. Единственное я не уверен на счёт производительности для больших таблиц. Сейчас у меня всего 963 записи в таблице category и запрос выполняется 100 миллисекунд.
Результат получается такой:
parent id ids
null 1 0000000001
1 2 0000000001,0000000002
2 3 0000000001,0000000002,0000000003
3 4 0000000001,0000000002,0000000003,0000000004
4 5 0000000001,0000000002,0000000003,0000000004,0000000005
2 103 0000000001,0000000002,0000000103
103 104 0000000001,0000000002,0000000103,0000000104
104 105 0000000001,0000000002,0000000103,0000000104,0000000105
104 427 0000000001,0000000002,0000000103,0000000104,0000000427
103 353 0000000001,0000000002,0000000103,0000000353
353 354 0000000001,0000000002,0000000103,0000000353,0000000354
103 653 0000000001,0000000002,0000000103,0000000653
2 219 0000000001,0000000002,0000000219
....
Если у кого-то появятся идеи как сделать запрос проще или сделать его более быстрым или вдруг есть что-то чего я не учёл, то прошу написать об этом.
Свою задачу с сортировкой я в принципе решил, но вопрос о максимально простом извлечении значения по индексу из списка значений разделённых запятыми остаётся открытым.
Средний
