Recursive query in MySQL for adjacency list depth first?

Question

The table this query running on is roughly structured like this:

comments_table(PK id, FK reply_to_id, content) 

FK is a self join on itself

It's running on 10.4.27-MariaDB

And the data looks something like this:

+----+-------------+---------+
| id | reply_to_id | content |
+----+-------------+---------+
| 12 |     NULL    |   text  |
| 13 |      12     |   text  |
| 14 |      12     |   text  |
| 15 |      13     |   text  |
+----+-------------+---------+

The query is supposed to retrieve in order all the reply comments given in input a father (or tree root).

The result order is supposed to be depth first.

An example of the expected result:

Input : 12
Result: 13,15,14

    12
  /    \
13      14
  \
   15
+----+
| id |
|----+
| 13 |
| 15 |
| 14 |
+----+

And so on

What I'm trying to archive is to have this be done in a query without any external code being used.

I've been trying recursion and to modify a query that looks like this:

select id 
from (
    select * from comments order by id
) comments_sorted, (
    select @pv := '62'
) initialisation 
where find_in_set(replied_to_id, @pv)
and length(@pv := concat(@pv, ',', id));

The query does work and it gives in output all the replies to a given father (or tree root)

The output looks like this:

+----+
| id |
+----+
| 13 |
| 14 |
| 15 |
+----+

Meanwhile the desired output is the one shown above

How can it possibly be implemented?

EDIT

To provide additional feedback

Using your query @Luuk with this set of data:

+----+---------------+
| id | replied_to_id |
+----+---------------+
| 81 |          NULL |
| 82 |          NULL |
| 83 |            82 |
| 84 |            83 |
| 85 |            83 |
| 86 |            83 |
| 87 |            84 |
| 88 |            87 |
| 93 |            88 |
+----+---------------+

I get this result:

+---+----+---------------+
| x | id | replied_to_id |
+---+----+---------------+
| 1 | 83 |            82 |
| 1 | 84 |            83 |
| 1 | 85 |            83 |
| 1 | 86 |            83 |
| 1 | 87 |            84 |
| 1 | 88 |            87 |
| 1 | 93 |            88 |
+---+----+---------------+

I can see the x value is not incrementing.

The query I used is:

WITH RECURSIVE cte AS ( 
   SELECT row_number() over (order by id) as x, id, replied_to_id 
   FROM comments 
   WHERE replied_to_id=82 
   UNION ALL 
   SELECT x, comments.id, comments.replied_to_id 
   FROM cte 
   INNER JOIN comments on comments.replied_to_id = cte.id 
) 
SELECT * FROM cte ORDER BY x,id;

What could it be?

The only recursion in SQL is a recursive WITH. MySQL version 8 is needed. — jarlh, Aug 31 '23 at 07:35
@jarlh But MariaDB 10.4 also can do a recursive WITH (It is working since 10.2.1, see: https://mariadb.com/kb/en/with/ ) — Luuk, Aug 31 '23 at 07:48
@Aarlaneth: What point are **you** trying to make? Why did you not complain to the person asking the question that he did use the _wrong_ tags? Why no complaints about "phpMyAdmin Server" ? Please... — Luuk, Aug 31 '23 at 08:27

score 2 · Answer 1 · answered Aug 31 '23 at 07:59

2

WITH RECURSIVE cte AS (
  SELECT 
     row_number() over (order by id) as x,
     id,
     reply_to_id 
  FROM test 
  WHERE reply_to_id=12
  
  UNION ALL
  
  SELECT x, test.id, test.reply_to_id
  FROM cte 
  INNER JOIN test on test.reply_to_id = cte.id
  )

SELECT * 
  FROM cte
  ORDER BY x,id;

see: DBFIDDLE

The row_number() orders the replies on first level, and this ordering is copied to next level.

output:

x	id	reply_to_id
1	13	12
1	15	13
2	14	12

answered Aug 31 '23 at 07:59

Luuk

12,245
5
22
33

When testing it on my table, the x value stays always 1, that resulting in them not being in the correct order, what could it be? – Jacopo V Aug 31 '23 at 08:54
3

@JacopoV That means that only one of the records returned is an immediate child of the node you are "starting at". Please update the example in your question to better represent the current issue. This old [MySQL blog article](https://dev.mysql.com/blog-archive/mysql-8-0-labs-recursive-common-table-expressions-in-mysql-ctes-part-three-hierarchies/) has an example of ordering by path. – user1191247 Aug 31 '23 at 09:05
1

@user1191247 done – Jacopo V Aug 31 '23 at 09:07
My solution is not working OK when multiple level exists (87 is reply to 84 is reply to 83). For this look at this answer https://stackoverflow.com/a/77014411/724039 – Luuk Aug 31 '23 at 09:17

score 2 · Accepted Answer · answered Aug 31 '23 at 09:11

Here is an other way to do it, using the path to order data :

WITH recursive cte (id, reply_to_id, path)
AS
(
    SELECT id, reply_to_id, CAST(id AS CHAR(200)) AS path
    FROM comments_table
    WHERE reply_to_id = 12
  UNION ALL
    SELECT e.id, e.reply_to_id, CONCAT(cte.path, ",", e.id)
    FROM comments_table AS e
    JOIN cte ON e.reply_to_id = cte.id
)
SELECT *
from cte
order by path

Demo here

Recursive query in MySQL for adjacency list depth first?

2 Answers2