Home / Questions / 440352
Accepted
cplaiuu
Asked: 2022-03-31 00:46:35 +0800 CST 2022-03-31 00:46:35 +0800 CST

MariaDB recursion query

  • 772

I am creating a page where clients can create their company organization chart and assign the heads of each department.

When a user enters the platform, the department is assigned automatically, but if they have access to more than one, they have a selector where they can select the department.

The data is stored in two databases:

Organization chart

EXPLAIN organization chart

idElement customerid idSubClient idSport idCategory teamid parentid active
201 fifteen -1 (null) (null) (null) (null) true
202 fifteen -1 two (null) (null) 201 true
203 fifteen -1 (null) 7 (null) 202 true
204 fifteen -1 (null) (null) 100 203 true
205 fifteen -1 (null) (null) 101 203 true
206 fifteen -1 (null) 4 (null) 202 true
209 fifteen -1 (null) two (null) 202 true
212 fifteen -1 (null) 3 (null) 202 true
213 fifteen -1 (null) (null) 106 206 true
214 fifteen -1 (null) (null) 107 206 true
215 fifteen -1 (null) (null) 108 209 true
216 fifteen -1 (null) (null) 109 209 true
217 fifteen -1 (null) (null) 110 212 true
218 fifteen -1 (null) (null) 111 212 true
219 fifteen -1 (null) 1 (null) 202 true
220 fifteen -1 (null) (null) 112 219 true
221 fifteen -1 (null) 5 (null) 202 true
222 fifteen -1 (null) (null) 113 221 true

users Organization chart .

EXPLAIN users Organization chart

idReference customerid idSubClient userid idElement active
131 fifteen -1 4914 202 true
132 fifteen -1 4914 205 true
134 fifteen -1 4914 213 true

I show you an example:

organization structure

As you can see there is an entity, with several sports below it, which in turn has sports categories with their teams.

I need a query that collects all the elements where I am positioned and then goes up to the top until I reach the sports to determine which sports I have access to and which sports I do not.

In the example above, Cristian will have access to these sports: Soccer and Futsal.

Until now I was using this query that searched for all the client's records and returned only the records that have idSport.

with recursive elem as (
            select elem.idUsuario, elem.idElemento as root, o.idElemento, o.idPadre, o.idDeporte, o.activo
            from usuariosOrganigrama elem
            join organigrama o on o.idElemento = elem.idElemento
            where o.idCliente = 15 and o.idSubCliente = -1 and elem.activo = 1
                  and o.activo = 1 and  elem.idUsuario = 4914
           UNION ALL
            select elem.idUsuario, elem.root, o.idElemento, o.idPadre, o.idDeporte, o.activo
            from elem 
            join organigrama o on o.idElemento = elem.idPadre
          )
          select idElemento, idDeporte
          from elem
          where activo = 1 and idDeporte is not null
          group by idElemento
          order by idElemento

but now with ~500 records in organization chart and ~1000 in users Organization chart the query fails and fills up my hard disk space (I have to shut down and start the maridb.service).

What do I have to do to improve the query?

http://sqlfiddle.com/#!9/ab7d26/1

Thanks.

sql

1 Answers

  1. Best Answer

    I modified your query a bit, try it to see if it improves performance:

    WITH recursive elemento AS (
            SELECT o.idElemento, o.idPadre, o.idDeporte
            FROM organigrama o
            INNER JOIN usuariosOrganigrama uo ON uo.idElemento = o.idElemento
            WHERE o.idCliente = 15 AND o.idSubCliente = -1 AND o.activo = true AND uo.activo = true AND uo.idUsuario = 4914 
          
           UNION ALL
           
            SELECT o.idElemento, o.idPadre, o.idDeporte
            FROM elemento e
            JOIN organigrama o on e.idPadre = o.idElemento
            WHERE o.idCliente = 15 and o.idSubCliente = -1 AND o.activo = true)
          
SELECT DISTINCT e.idElemento, e.idDeporte
FROM elemento e
WHERE e.idDeporte IS NOT NULL;
ORDER BY e.idElemento;

    Output (according to the example data in sqlfiddle):

    idElement idSport
    202 two

    As you can see, the query returns the idElemento field and idDeporte (if it is not null), but not necessarily the idElemento that is in usersOrganigrama, but all those of the tree (upwards) whose idDeporte is not empty.

    • 1