Bertrand Fabre“Je travaille sur des données RH et c’est la galère pour parcourir la hiérarchie des employés…”
Une approche qui pourrait aider est l’utilisation d’une CTE récursive !
Ce type de requête permet de parcourir des données qui sont, par exemple, hiérarchiques comme une table d’employé ou une nomenclature produit.
La syntaxe
WITH RECURSIVE cte_nom AS (
-- Requête ancre (non récursive)
SELECT ...
FROM maTable
...
UNION ALL
-- Requête récursive
SELECT ...
FROM maTable
INNER JOIN cte_nom
ON ...
...
)
SELECT *
FROM cte_nom;👉 La requête ancre : elle va servir de base de départ de la CTE récursive ;
👉 La requête récursive : à partir des données de base elle va faire comme une boucle selon les conditions de jointure et de filtre jusqu’à s’arrêter au moment où il n’y a plus de données à parcourir ou que la condition n’est plus respectée.
Bon, ok, il va falloir un bel exemple pour imager tout cela.
Prenons le cas d’un profil data travaillant dans un service RH qui a besoin de connaître tous les niveaux de responsables d’une personne à partir de son identifiant.
DECLARE id_emp DEFAULT 8;
WITH RECURSIVE hierarchie AS (
-- Requête ancre (non récursive)
SELECT id_employe, nom, id_manager, 0 AS niveau
FROM (
SELECT 1 AS id_employe, 'Alice' AS nom, NULL AS id_manager UNION ALL
SELECT 2, 'Bob', 1 UNION ALL
SELECT 3, 'Charlie', 1 UNION ALL
SELECT 4, 'David', 2 UNION ALL
SELECT 5, 'Eve', 2 UNION ALL
SELECT 6, 'Frank', 3 UNION ALL
SELECT 7, 'Grace', 3 UNION ALL
SELECT 8, 'Heidi', 4
) AS employes
WHERE id_employe = id_emp
UNION ALL
-- Requête récursive
SELECT e.id_employe, e.nom, e.id_manager, h.niveau + 1 AS niveau
FROM (
SELECT 1 AS id_employe, 'Alice' AS nom, NULL AS id_manager UNION ALL
SELECT 2, 'Bob', 1 UNION ALL
SELECT 3, 'Charlie', 1 UNION ALL
SELECT 4, 'David', 2 UNION ALL
SELECT 5, 'Eve', 2 UNION ALL
SELECT 6, 'Frank', 3 UNION ALL
SELECT 7, 'Grace', 3 UNION ALL
SELECT 8, 'Heidi', 4
) AS e
INNER JOIN hierarchie h
ON e.id_employe = h.id_manager
)
SELECT * FROM hierarchie
ORDER BY niveau, id_employe;🎁 Voilà cadeau…
Un peu d’explications aidera grandement .
Les données
Les deux sous-requêtes que vous voyez dans les deux FROM sont les mêmes données qui pourraient donc faire référence à une même table. Dans notre cas, je voulais que vous puissiez faire copier-coller de cette requête ;
La variable
Au tout début je fais un DECLARE afin de définir l’identifiant de l’employé pour lequel on va rechercher ses responsables. C’est juste une question de lisibilité et de pratique mais en aucun cas obligatoire pour la CTE récursive ;
Le champ “niveau”
J’ai ajouté ce champ pour que l’on constate le niveau de hiérarchie par rapport à l’employé initial ;
Filtre dans l’ancre
Dans le WHERE je lui indique quel est l’employé qui sera mon point de départ. L’employé avec l’identifiant 8 dans notre cas ;
L’incrément du champ “niveau”
A chaque fois que l’on trouvera un responsable d’un niveau supérieur à l’employé on ajoutera 1 au champ “niveau” ;
La jointure
Je dirai que c’est le concept le plus important. La jointure qui est faite est le lien entre l’ancre et la récursivité.
On utilise un INNER JOIN et la condition de jointure lie l’identifiant du manager de l’employer 8 (h.id_manager) avec un identifiant d’un employé (4 dans le premier cas). Et ainsi de suite jusqu’à ne plus avoir de manager à un plus haut niveau.
Le résultat
id_employe | nom | id_manager | niveau |
8 | Heidi | 4 | 0 |
4 | David | 2 | 1 |
2 | Bob | 1 | 2 |
1 | Alice | null | 3 |
L’employé 8 a comme manager l’employé 4 qui a comme manager l’employé 2 qui a comme manager l’employé 1 qui n’a pas de manager car l’id_manager est null.
Et pour le champ “niveau” on peut dire que l’employé 1 (Alice) est le N+3 de l’employé 8 (Heidi).
Merci de votre lecture !
