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
| 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 .
| 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:
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.
