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?

Answer 1

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