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`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;