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

Question

[Eduard J]

SELECT * FROM comments ORDER BY ...

spoiler

Итог:

1
..2
5
..3
....4

Пробовал так, но рекурсивно не получается:

SELECT * FROM comments
ORDER BY 
   CASE
       WHEN parent_id = 0  
       THEN id
       ELSE parent_id 
   END DESC

Comments

[idShura]

А результат какой хочешь получить???

[Akina]

Пробовал так, но рекурсивно не получается

А что тут рекурсивного-то?

Answer 1

[Akina]

Как-то так:

WITH RECURSIVE
cte AS (
    SELECT *, LPAD(id, 10, '0') path
    FROM comments
    WHERE parent_id = 0
  UNION ALL
    SELECT comments.*, CONCAT(cte.path, LPAD(comments.id, 10, '0'))
    FROM comments
    JOIN cte ON cte.id = comments.parent_id
)
SELECT id, parent_id, content
FROM cte
ORDER BY path;

Откорректировать длину в зависимости от типа данных.

Answer 2

[Rsa97]

Проще не получится. Надо делать рекурсивное cte с формированием пути, потом сортировать по этому пути.

WITH RECURSIVE `cte` AS (
  SELECT `id`, `parent_id`, `comment`,
         CAST(RIGHT(CONCAT('0000', `id`), 4) AS CHAR(256)) AS `path`
    FROM `comments`
    WHERE `parent_id` = 0
  UNION
  SELECT `c`.`id`, `c`.`parent_id`, `c`.`comment`,
         CONCAT(`cte`.`path`, '/', RIGHT(CONCAT('0000', `c`.`id`), 4)) AS `path`
    FROM `cte`
    JOIN `comments` AS `c` ON `c`.`parent_id` = `cte`.`id`
)
SELECT *
  FROM `cte`
  ORDER BY `path`