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.

What do you want to achieve? What do you mean by "I know how to do this"? Also, how is this related to PHP - you haven't shared any such code — Nico Haase, Jan 11 '21 at 16:29
Does this answer your question? [How to create a MySQL hierarchical recursive query](https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) — Traian GEICU, Jan 11 '21 at 16:30
What version of MySQL are you using? Is it 5.6, as implied by the fiddle? Or later? — Strawberry, Jan 11 '21 at 16:37
@Strawberry I checked, the production server is using MySQL 10.2, On my dev server I currently have installed 5.6, I can upgrade, no problem — RDU, Jan 11 '21 at 16:55
*the production server is using MySQL 10.2* Not exists. Maybe it is MariaDB? show the output for `SELECT VERSION();`. *I can upgrade, no problem* Do it... — Akina, Jan 11 '21 at 17:16

score 1 · Accepted Answer · answered Jan 11 '21 at 17:32

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.

answered Jan 11 '21 at 17:32

Akina

39,301
5
14
25

If my query is really long, and sometimes I need to use recursion and sometimes not, Is this still the best approach, feels weird to have to write the long query once then union all and write the query again, or is that normal ? – RDU Jan 14 '21 at 17:46
1

@Radu *sometimes I need to use recursion and sometimes not* Nothing prevents you to add a condition into recursive part which is statically FALSE (`WITH .. (.. UNION ALL .. WHERE .. AND @recurse_needed) ..`, where `@recurse_needed` is TRUE or FALSE constant) - such recursive CTE in practice is equal to non-recursive in the case of FALSE, I think that nobody can detect the difference in execution time in this case. *or is that normal ?* I think this is a norma. – Akina Jan 14 '21 at 19:18

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

1 Answers1