php - Can you pull all parent-child relation columns from the same table in one query while keeping the right order?

Question

CREATE TABLE messages (
    id int,
    text varchar(255),
    parent int NULL
);

insert into messages (id, text, parent) values 
(1, 'Parent message 1', NULL),
(2, 'Parent message 2 ', NULL),
(3, 'Parent message 3', NULL),
(4, 'Child 1 of parent one', 1),
(5, 'Child 1 of of child', 4),
(6, 'child 2 of parent 2', 2),
(7, 'child 3 of parent 4', 3);

[SQL Fiddle][1]

[1]: http://sqlfiddle.com/#!9/fca7240/1 I'm also saving the date in my project

I have a simple table that has parent-child relation where a parent can have multiple children and the children can also have N number of children.

I know how to do this on the back-end by pulling parents first and make another Ajax request for every parent message and they recursively get all the children.

But I don't know how or if it's possible to do this in SQL so i don't have to make another request for every parent that has children and also keep the right order. Like a message board or comment scenario where people can reply to each other.

Is it possible to query all parents and their children while keeping the right order in one query ?

-- Parent Message
---- Reply to parent
------'later another message gets inserted here so I can't order by id'
---- Reply to replay
-- Parent Message
---- Reply

I know How to to this recursively with multiple trips but I don't know how to do it in one query.

question from:https://stackoverflow.com/questions/65670775/can-you-pull-all-parent-child-relation-columns-from-the-same-table-in-one-query

Answer 1

WITH RECURSIVE
cte AS ( SELECT id, text, parent, CAST(LPAD(id, 6, '0') AS CHAR(65535)) path
         FROM messages
         WHERE parent IS NULL 
       UNION ALL
         SELECT messages.id, messages.text, messages.parent, CONCAT(cte.path, LPAD(messages.id, 6, '0'))
         FROM cte
         JOIN messages ON cte.id = messages.parent )
SELECT id, text, parent
FROM cte
ORDER BY path

DEMO

MySQL 8 or MariaDB 10.2.2 needed.

PS. The length specified in LPAD (6 in my query) depends on maximal id value length (must be not less).

PPS. It is possible that CAST in non-recursive CTE part is excess for MariaDB - test.