Mysql Query with recursive N levels

Question

Mysql Query with recursive N levels

Asked 7 years, 8 months ago

Viewed 1,539 times

1

I have the following query

    select  id,
        nome,
        user_id
from    (select * from users
         order by user_id, id) users,
        (select @pv := '2') initialisation
where   find_in_set(user_id, @pv) > 0
and     @pv := concat(@pv, ',', id)
union
select  id,
        nome,
        user_id
from users where id = 2

And here are some fake values for this table.

ID  NAME        USER_ID
1   Main User   0
2   User A      1
3   User B      1
4   User C      2
5   User D      2
6   User E      2
7   User F      4
8   User G      4
9   User H      1
10  User I      1
11  User J      2

The query works, but I need the results to be nested and sorted by user level.

In the query example, I passed ID 2, so the result should come like this:

2. User A
 ___ 4. User C
    ___ 7. User F
    ___ 8. User G
 ___ 5. User D
 ___ 6. User E
 ___ 11. User J

Can be with __ in the result, no problem. The important thing is to have this tree scheme.

Thank you.

What version of MYSQL ?

– Rovann Linhalis

2018/03/05 at 20:28
Relevant: https://answall.com/a/193833/64969

– Jefferson Quesado

2018/03/05 at 22:13

2 answers

3

In mysql, in versions >= 8, you can use a CTE (Common Table Expressions), which can be recursive. I made an example:

with recursive aux (id , nome , user_id , i  , seq ) as
(
    select
    id, 
    name,
    coalesce(user_id,0) as user_id,
    CAST('' AS CHAR(100)) as i,
    cast(LPAD(id,3,'0') as char(100)) as seq
    from users where id = 2

UNION ALL

    select
    c.id, 
    c.name,
    c.user_id,
    concat(x.i , '  '),
    concat(x.seq , '.',LPAD(c.id,3,'0')) as seq
    from users c
INNER JOIN aux x ON c.user_id = x.id
)

select 
x.id,
x.nome,
x.user_id as pai,
concat(x.i,'__ ',x.id,'. ', x.nome) as raiz,
x.seq
from aux x order by seq;

I put in the Db-Fiddle (Sqlfiddle does not support)

Upshot:

Using Id 2 as reported in the example:

ps. Also displayed the field seq I use to sort the records.

As Jefferson Quesado mentioned in the comments, he has an answer HERE that makes an example of another similar situation, in addition to directing to the mysql blog on CTE (Common Table Expressions)

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.

by Gustavo Castro • **599** points · Answer 1 · 2018-03-05T19:38:20+00:00

This will not appear this way in mysql only in another language as it requires indentation as html offers lists

function dba_buscaproximo($id_pai = null) {
    $sql = "SELECT *
            FROM `ingredientes`
            WHERE id_pai = {$id_pai} order by nome";
    $consulta = mysql_query($sql);
    return $consulta;
}

function dba_buscapai($id_filho = null) {
    $sql = "SELECT *
            FROM `ingredientes`
            WHERE id_ingrediente = {$id_filho} order by nome";

    $consulta = mysql_query($sql);
    return $consulta;
}

<html>
 <ul>    
        <?php
        buscafilho();

        ?>
    </ul>

</html>

This is a recursive call where I search for the next child while there are still children inside that parent is a big loop that should be solved in a programming link and not in mysql.

Code taken from my TCC =D

function buscafilho($id_pai = 0) { 

    $consulta = dba_buscaproximo($id_pai);

    if (mysql_num_rows($consulta) > 0) {
        if ($id_pai != 0) {
            echo "<ul>";
        }
        while ($row = mysql_fetch_array($consulta)) {

            echo '<li>';

            echo $row["nome"];
            buscafilho($row['id_ingrediente']);

            echo '</li>';

        }
        if ($id_pai != 0) {
            echo "</ul>";
        }
    }
}