Obtenir une hiérarchie

Version imprimable

Voir 40 message(s) de cette discussion en une page

22/11/2012, 11h57
miniil

Obtenir une hiérarchie

Bonjour,

Je cherche à créer une requête SQL me permettant d'obtenir une hiérarchie parent-enfant(s).

J'ai la table suivante :
TACHE
TACHE_PARENT

Et j'aimerai obtenir le résultat suivant :

TACHE_PARENT
--TACHE_PARENT
----TACHE

D'avance merci de votre aide
22/11/2012, 13h08
islamov2000

Sur quel SGBD travailles tu?
22/11/2012, 13h19
SQLpro

En SQL normalisé, cela se fait via des requêtes récursives. Lisez l'article que j'ai écrit à ce sujet :
http://sqlpro.developpez.com/cours/s...te-recursives/
Notamment l'exemple : IV-A-1. Indentation hiérarchique

A +
22/11/2012, 13h31
miniil

Cela semble en effet être la solution.
Je teste et reviens vers vous si besoin.

REM : SGBD = SQLServer
22/11/2012, 15h25
miniil

Il me semble que les requêtes récursives sont effectivement la bonne solution.

Malheureusement, mon cas est un peu plus compliqué que celui que je présentais dans mon premier message. Peut-être pourrez-vous m'aider...

J'ai 3 tables :

TACHES
--------
ID
Nom
Parent (pouvant être null)

PERSONNES
-----------
ID
NOM

PERSONNES_TACHES
--------------------
IDTACHE
IDPERSONNE

J'aimerai obtenir la liste suivante :

Tache parent
-- Tache enfant : nom personne

Mais malheureusement, dans ma table personnes_taches, je n'ai pas la tache parent car elle n'est pas clairement assignée à une personne.

Heu c'est clair???

Merci d'avance de votre aide.
22/11/2012, 16h04
CinePhil

Si tu es capable de récupérer la ou les tâches parent d'une tâche T, tu es aussi capable, par jointure avec les deux autres tables, normalement, de récupérer la ou les personnes affectées aux tâches parent.
26/11/2012, 16h11
miniil

Bonjour,

Je reviens à nouveau vers vous. Pour la jointure, je pense y être parvenue.

Voici mon problème suivant :

J'aimerai à partir d'une tâche enfant, récupérer la tâche parent pour créer la hiérarchie.

Mais si deux tâches enfants données ont le même parent, celui-ci ne doit être affiché qu'une seule fois.

Par exemple :

- Tâche parent 1
-- Tâche enfant 1 : Nom ressource
-- Tâche enfant 2 : Nom ressource
- Tâche parent 2
-- Tâche enfant 3 : Nom ressource

REM : Je dois lister cette hiérarchie en fonction du nom de la ressource assignée à une tâche enfant.

Pourriez-vous m'aider?

Avec un petit cas concret, vous pouvez créer votre hiérarchie pour ensuite l'interroger :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
With TACHES (Id_T, Nom_T, Parent_Id_T) as
(
select 1, 'Tâche 1', null union all
select 2, 'Tâche 2', 1    union all
select 3, 'Tâche 3', 1    union all
select 4, 'Tâche 4', null union all
select 5, 'Tâche 5', 4
)
  ,  PERSONNES (Id_P, Nom_P) as
(
select 1, 'Personne A' union all
select 2, 'Personne B' union all
select 3, 'Personne C'
)
  ,  PERSONNES_TACHES (Id_T, Id_P) as
(
select 2, 1 union all
select 3, 2 union all
select 5, 3
)
  ,  Hierarchie (Id_T_init, Id_T, Nom_T_init, Nom_T, lvl) as
(
select Id_T, Id_T, Nom_T, Nom_T, 1
  from TACHES
 where Parent_Id_T is null
 union all
select H.Id_T_init, T.Id_T, H.Nom_T_init, T.Nom_T, H.lvl + 1
  from Hierarchie as H
  join TACHES     as T
    on T.Parent_Id_T = H.Id_T
)
    select replicate('-', HI.lvl) + ' ' + HI.Nom_T as Nom_T
         , PE.Nom_P
      from Hierarchie       as HI
 left join PERSONNES_TACHES as PT
inner join PERSONNES        as PE
        on PE.Id_P = PT.Id_P
        on PT.Id_T = HI.Id_T
  order by HI.Id_T_init asc
         , HI.lvl       asc
         , HI.Id_T      asc;
 
Nom_T       Nom_P
----------- ----------
- Tâche 1   NULL
-- Tâche 2  Personne A
-- Tâche 3  Personne B
- Tâche 4   NULL
-- Tâche 5  Personne C

Il faut sûrement modifier les colonnes retournées, mais elles sont toutes à disposition.

27/11/2012, 10h23
miniil

Bonjour,

L'exemple fonctionne très bien mais lorsque je transpose cette solution à ma DB, qui contient beaucoup plus de records, j'obtiens l'erreur suivante :

"The statement terminated. The maximum recursion 100 has been exhausted before statement completion".

Ce maximum de recursion est-il un paramètre de SQL Server, peut-on le modifier?
27/11/2012, 10h34
Waldar

En dynamique à la fin de la requête :

Code:

OPTION (MAXRECURSION 32767);
27/11/2012, 10h55
miniil

Super ça fonctionne. C'est déjà une étape pour moi :)

Maintenant encore une petite question.

Si je veux sélectionner les tâches pour une personne donnée suivant son nom, donc ici :

WHERE PE.Nom_p LIKE 'Personne A'

Comment puis-je également obtenir la tâche parent dans le résultat?
Et si cette personne possède plusieurs tâches différentes mais ayant le même parent?

Sorry pour les nombreuses questions mais là je patauge un peu :)

Je viens de tester ceci :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
 
WITH TACHES (Id_T, Nom_T, Parent_Id_T) AS
(
SELECT 1, 'Tâche 1', NULL union ALL
SELECT 2, 'Tâche 2', 1    union ALL
SELECT 3, 'Tâche 3', 1    union ALL
SELECT 4, 'Tâche 4', NULL union ALL
SELECT 5, 'Tâche 5', 4
)
  ,  PERSONNES (Id_P, Nom_P) AS
(
SELECT 1, 'Personne A' union ALL
SELECT 2, 'Personne B' 
)
  ,  PERSONNES_TACHES (Id_T, Id_P) AS
(
SELECT 2, 1 union ALL
SELECT 3, 1 union ALL
SELECT 5, 2
)
  ,  Hierarchie (Id_T_init, Id_T, Nom_T_init, Nom_T, Parent_id_T, lvl) AS
(
SELECT Id_T, Id_T, Nom_T, Nom_T, null, 1
  FROM TACHES
 WHERE Parent_Id_T IS NULL
 union ALL
SELECT H.Id_T_init, T.Id_T, H.Nom_T_init, T.Nom_T, T.Parent_id_T, H.lvl + 1
  FROM Hierarchie AS H
  JOIN TACHES     AS T
    ON T.Parent_Id_T = H.Id_T
)
, HierarchieByPerson (idtinit, lvl, idt, parentidt, nomt, nomp) AS
(
	SELECT HI.ID_T_Init, HI.lvl, HI.Id_T, HI.Parent_id_T, HI.Nom_T, PE.Nom_p
	FROM Hierarchie AS HI
	LEFT JOIN PERSONNES_TACHES AS PT
	INNER JOIN PERSONNES AS PE
	ON PE.Id_p = PT.id_P
	ON PT.Id_T = HI.Id_T
)
, test (idtinit, lvl, idt, parentidt, nomt, nomp) AS
(
	SELECT idtinit, lvl, idt, parentidt, nomt, nomp
	FROM HierarchieByPerson
	WHERE nomp LIKE 'Personne A'
	UNION ALL
	SELECT id_t, 1, id_t, parent_id_t, nom_t, null
	FROM TACHES t
	JOIN test AS te ON t.id_t = te.parentidt
)
 
SELECT replicate('-', t.lvl) + ' ' + t.nomt, t.nomp
  FROM test t
 ORDER BY t.idtinit ASC, 
 t.lvl ASC,
 t.idt ASC

Je ne sais pas si c'est correct mais le résultat est presque celui attendu.
Sauf que si j'assigne la tâche 3 (qui a pour parent la tâche 1 également) à la Personne A et que j'effectue ma recherche sur cette personne, j'obtiens deux fois le résultat de la tâche parent.

Donc :

- Tâche 1 NULL
- Tâche 1 NULL
-- Tâche 2 Personne A
-- Tâche 3 Personne A

Vous avez rajouté un peu trop d'étapes, une fois que vous avez HierarchieByPerson vous avez tout ce qu'il faut pour faire le test d'existence :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
  ,  HierarchieByPerson (Id_T_init, lvl, Id_T, Nom_T_init, Nom_T, Nom_P) as
(
    select HI.Id_T_init, HI.lvl, HI.Id_T, HI.Nom_T_init
         , replicate('-', HI.lvl) + ' ' + HI.Nom_T as Nom_T
         , PE.Nom_P
      from Hierarchie       as HI
 left join PERSONNES_TACHES as PT
inner join PERSONNES        as PE
        on PE.Id_P = PT.Id_P
        on PT.Id_T = HI.Id_T
)
  select HP1.Nom_T, HP1.Nom_P
    from HierarchieByPerson as HP1
   where exists (select null
                   from HierarchieByPerson as HP2
                  where HP2.Id_T_init = HP1.Id_T_init
                    and HP2.Nom_P = 'Personne A')
  OPTION (MAXRECURSION 32767);
 
 
Nom_T       Nom_P
----------- ----------
- Tâche 1   NULL
-- Tâche 2  Personne A
-- Tâche 3  Personne A -- en utilisant PA sur T3

J'y ai cru...
Mais
Si j'assigne la personne A aux tâches 2 et 5
Et la personnes B à la tâche 3

Comme ceci :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
 
 
WITH TACHES (Id_T, Nom_T, Parent_Id_T) AS
(
SELECT 1, 'Tâche 1', NULL union ALL
SELECT 2, 'Tâche 2', 1    union ALL
SELECT 3, 'Tâche 3', 1    union ALL
SELECT 4, 'Tâche 4', NULL union ALL
SELECT 5, 'Tâche 5', 4
)
  ,  PERSONNES (Id_P, Nom_P) AS
(
SELECT 1, 'Personne A' union ALL
SELECT 2, 'Personne B' 
)
  ,  PERSONNES_TACHES (Id_T, Id_P) AS
(
SELECT 2, 1 union ALL
SELECT 3, 2 union ALL
SELECT 5, 1
)
  ,  Hierarchie (Id_T_init, Id_T, Nom_T_init, Nom_T, Parent_id_T, lvl) AS
(
SELECT Id_T, Id_T, Nom_T, Nom_T, NULL, 1
  FROM TACHES
 WHERE Parent_Id_T IS NULL
 union ALL
SELECT H.Id_T_init, T.Id_T, H.Nom_T_init, T.Nom_T, T.Parent_id_T, H.lvl + 1
  FROM Hierarchie AS H
  JOIN TACHES     AS T
    ON T.Parent_Id_T = H.Id_T
)
,  HierarchieByPerson (Id_T_init, lvl, Id_T, Nom_T_init, Nom_T, Nom_P) AS
(
    SELECT HI.Id_T_init, HI.lvl, HI.Id_T, HI.Nom_T_init
         , replicate('-', HI.lvl) + ' ' + HI.Nom_T AS Nom_T
         , PE.Nom_P
      FROM Hierarchie       AS HI
 LEFT JOIN PERSONNES_TACHES AS PT
INNER JOIN PERSONNES        AS PE
        ON PE.Id_P = PT.Id_P
        ON PT.Id_T = HI.Id_T
)
  SELECT HP1.Nom_T, HP1.Nom_P
    FROM HierarchieByPerson AS HP1
   WHERE EXISTS (SELECT NULL
                   FROM HierarchieByPerson AS HP2
                  WHERE HP2.Id_T_init = HP1.Id_T_init
                    AND HP2.Nom_P = 'Personne A')
  OPTION (MAXRECURSION 32767);

J'obtiens le résultat :

- Tâche 1 NULL
- Tâche 4 NULL
-- Tâche 5 Personne A
-- Tâche 2 Personne A
-- Tâche 3 Personne B

La personne B y apparaît.

Je n'y comprends vraiment rien...

En tout cas, merci beaucoup de prendre le temps de m'aider.

27/11/2012, 17h27
Waldar

Ah dans ce cas j'ai probablement mal compris le besoin.
Je pensais qu'à partir d'une personne vous vouliez reconstruire toute la hierarchie dans laquelle elle appartient.

En fait vous voulez juste lui-même et son parent ?
28/11/2012, 08h01
miniil

Oui c'est bien ça, juste la personne et la hiérarchie correspondante.

Et si il y a plusieurs tâches sous une tâche parent, il faudrait que le parent n’apparaisse qu'une seule fois.

Est-ce possible?
Je m'arrache les cheveux depuis plusieurs jours sur ce cas...

Encore merci de votre aide.
11/12/2012, 10h22
miniil

Je me permets de remonter le sujet car je n'ai toujours trouvé aucune réponse à mon problème.

Si quelqu'un peut m'aider...

D'avance merci.
bonne journée.
14/12/2012, 15h13
AVSInfoGest

Bonjour,

Je trouve ce sujet sur les requêtes récursives et l'excellent article de F. BROUARD très intéressants :ccool:

En ajoutant un "order by Id_T_init" avant OPTION, on obtient bien la hiérarchie :

Nom_T Nom_P
- Tâche 1 NULL
-- Tâche 2 Personne A
-- Tâche 3 Personne B
- Tâche 4 NULL
-- Tâche 5 Personne A

Quel résultat attendiez-vous ?
17/12/2012, 17h59
fsmrel

Bonjour,

Citation:

Envoyé par miniil

Je m'arrache les cheveux depuis plusieurs jours sur ce cas...

Supposons que vous ayez la situation suivante (où T001, ..., T192 sont des tâches et Fernand, etc., les personnes affectées à ces tâches) :

http://www.fsmwarden.com/developpez_...recursion).png

Cette situation est-elle conforme à votre structure ? Si oui, quel résultat attendez-vous si la personne à considérer est Raoul ?
18/12/2012, 16h16
miniil

Bonjour,

Désolée pour ma réponse un peu tardive...

Le résultat que j'aimerai obtenir suivant cette représentation est :

-T001
-- T004
---- T005 - Raoul
-- T090
---- T100
------ T101 - Raoul
------ T105
-------- T107 - Raoul
---- T110
------ T180 - Raoul
------ T190
-------- T192 - Raoul

Merci d'avance pour votre aide

Voir 40 message(s) de cette discussion en une page