Как построить дерево одним запросом c сортировкой по родителю?

Для построения дерева с сортировкой по родителю в MySQL можно использовать рекурсивный запрос с использованием общей таблицы выражений (CTE) и объединением таблицы с самой собой.

Вот пример запроса, который выполняет такую задачу:

WITH RECURSIVE cte AS (
SELECT id, parentid, 0 AS level FROM yourtable WHERE parent_id IS NULL
UNION ALL
SELECT t.id, t.parent_id, cte.level + 1
FROM your_table AS t
INNER JOIN cte ON t.parent_id = cte.id
)
SELECT *
FROM cte
ORDER BY parent_id ASC, id ASC;

Давайте разберем этот запрос более подробно:

  1. Мы определяем общую таблицу выражений (CTE) с именем "cte". В ней мы выбираем строки из таблицы "yourtable", где "parentid" равно NULL (то есть корневые узлы дерева) и задаем уровень 0 для этих узлов.
  1. Затем мы выполняем рекурсивное объединение, выбирая строки из таблицы "yourtable", где "parentid" равно id из CTE и увеличиваем уровень на 1 каждый раз.
  1. После этого мы выбираем все столбцы из CTE и сортируем результат по столбцу "parent_id" по возрастанию и по столбцу "id" по возрастанию. Эта сортировка помогает нам представить дерево с иерархической структурой, где дочерние узлы отображаются под своим родительским узлом.
  1. Наконец, мы получаем результирующий набор данных, который содержит все строки таблицы "your_table" с добавленными столбцами уровня для каждой строки и отсортированным порядком.

Таким образом, данный запрос позволяет построить дерево одним запросом с сортировкой по родителю.