MySQL Query with recursion in N levels

Question

MySQL Query with recursion in N levels

Navigation

#1 by (3 votes)
#2 by (0 votes)

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 the user level.

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

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

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

Thank you.

mysql sql

asked by anonymous 02.03.2018 / 15:04

2 answers

0

This will not appear this way in mysql only in another language because it requires indentation as html offers the 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 look for the next child while there are still children within that parent is a large loop that should be resolved in a programming language and not in mysql.

Code removed 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>";
        }
    }
}

05.03.2018 / 20:38

Can not resolve method 'getSupportFragmentManager ()' Bring the first row based on a date in SQL

score 3 · Accepted Answer

In mysql, in versions > = 8, you can use a CTE (Common Table Expressions), which can be recursive. I gave 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 it in Db-Fiddle (SQLFiddle does not support)

Result:

UsingId2,asintheexample:

ps.IalsodisplaytheseqfieldIusetosorttherecords.

AsJeffersonQuesadomentionedinthecomments,hehasa HERE answer that gives an example of another similar situation, as well as targeting the MySql blog that talks about CTE (Common Table Expressions)