Discussion :

[StringBuilder]

Bonjour,

Je dois parcours la hiérarchie d'utilisateurs dans une requête.

La structure de la table des utilisateurs est la suivante :

rep (id, name, sup, adj)

id = pk
name = nom de l'utilsiateur
sup = id du suppérieur (nullable)
adj = adjoin de l'utilisateur (nullable)

Voici un jeu d'exemple qui fonctionne bien :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
id, name, sup, adj
1, 'Boss', null, null
2, 'Chef 1', 1, 3
3, 'Chef 2', 1, null
4, 'Chef 3', 1, null
5, 'Subordonné 1', 2, null
6, 'Subordonné 2', 2, null
7, 'Subordonné 3', 3, null
8, 'Subordonné 4', 4, null

Et ma requête pour demander l'arborescente complète (en partant du boss) :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
 
with hie (id_filtre, id, name)
as
(
	select rep.id, rep.id, rep.name from rep
	union all
	select hie.id_filtre, rep.id, rep.name from hie inner join rep on hie.id = rep.sup or hie.id = rep.adj
)
select distinct id, name 
from hie
where id_filtre = 1

J'obtiens bien :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
 
1	Boss
2	Chef 1
3	Chef 2
4	Chef 3
5	Subordonné 1
6	Subordonné 2
7	Subordonné 3
8	Subordonné 4

Parfait.

Seulement voilà...
Un adjoint peut avoir pour adjoint... la personne dont il est adjoint :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
id, name, sup, adj
1, 'Boss', null, null
2, 'Chef 1', 1, 3
3, 'Chef 2', 1, 2
4, 'Chef 3', 1, null
5, 'Subordonné 1', 2, null
6, 'Subordonné 2', 2, null
7, 'Subordonné 3', 3, null
8, 'Subordonné 4', 4, null

Ou bien un adjoint peut avoir comme chef... la personne dont il est adjoint :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
id, name, sup, adj
1, 'Boss', null, null
2, 'Chef 1', 1, 3
3, 'Chef 2', 2, null
4, 'Chef 3', 1, null
5, 'Subordonné 1', 2, null
6, 'Subordonné 2', 2, null
7, 'Subordonné 3', 3, null
8, 'Subordonné 4', 4, null

Et ça c'est quand c'est bien saisi... Des fois un chef se retrouve subordonné de ses subordonnés...

Bref : le souci c'est que ma requête plante dès qu'il y a une boucle dans la hiérarchie.

Comment faire pour vérifier qu'il n'y a pas déjà les id sélectionnés dans "hie" avant de les rajouter de nouveau ?

Dès que je tente de rajouter un "not exists" ou une seconde jointure sur "hie" je me retrouve avec une erreur "Un membre récursif d'une expression de table commune 'hie' possède plusieurs références récursives."

Reste la solution méga-crado de compter le niveau de récursivité, et la stopper arrivé à un certain niveau arbitraire... Mais je trouve cette solution aussi moche que sale

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
with hie (lvl, id_filtre, id, name)
as
(
	select 1, rep.id, rep.id, rep.name from rep
	union all
	select lvl + 1, hie.id_filtre, rep.id, rep.name from hie inner join rep on hie.id = rep.sup or hie.id = rep.adj where hie.lvl < 50
)
select distinct id, name from hie
where id_filtre = 1

[aieeeuuuuu]

Bonjour,

il est possible de stocker dans une colonne le "chemin", et vérifier qu'on ne tourne pas en rond.

Par exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
WITH hie (id_filtre, id, name, chemin)
as
(
	select rep.id, rep.id, rep.name , '-' + CAST(id AS VARCHAR(MAX)) + '-' as chemin
	from rep
	union all
	select hie.id_filtre, rep.id, rep.name , chemin + '-' + CAST(rep.id AS VARCHAR(MAX)) + '-' as chemin
	from hie inner join rep 
	   on hie.id = rep.sup or hie.id = rep.adj
	where chemin NOT LIKE '%-' + CAST(rep.id AS VARCHAR(MAX)) + '-%'
)
select distinct id, name 
from hie
where id_filtre = 1

Une version sur le même principe mais avec de l'XML doit également être possible

[Waldar]

Déjà votre requête de base n'est pas bonne, quand vous dites vous partez du boss, il faut partir du boss :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
with hie (id, name) as
(
select rep.id, rep.name
  from rep
 where sup is null -- C'est ici qu'on met le point de départ de la récursion
 union all
select rep.id, rep.name
  from hie 
  join rep on hie.id in (rep.sup, rep.adj)
)
select id, name
  from hie;

Plus de distinct, plus d'id_filtre qui sont évalués à la fin de la requête après avoir fait toutes les récursions.

Ensuite, ça va dépendre du SGBD sur lequel vous travaillez.
Oracle Database a une syntaxe (propriétaire) afin d'éviter les boucles.
Sur SQL-Server et PostgreSQL, il faut suivre une solution comme celle proposée par aieeeuuuuu, conservez la liste des ID déjà parcouru (dans une chaîne pour SQL-Server ou un array sur PostgreSQL) et vérifier qu'on ne les parcourt pas de nouveau.

[StringBuilder]

Bonjour,
il est possible de stocker dans une colonne le "chemin", et vérifier qu'on ne tourne pas en rond.

Merci

C'est pas extra, mais ça sera toujours bien mieux que ma solution à base de limitation de la profondeur de récursion

Déjà votre requête de base n'est pas bonne, quand vous dites vous partez du boss, il faut partir du boss

En effet, ma requête "réelle" était bonne, je sais pas pourquoi quand je l'ai réécrite simplifiée pour le forum j'ai introduit cette erreur. Je me demandais bien pourquoi j'avais des doublons

A la base la requête ressemble à ça :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

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
57
58
59
60
61
62
63
64
65
66
 
with bea (id, adv)
as
(
	select id2.id, id3.f7019 from {0}cp cp inner join {0}id id1 on id1.id_fi = cp.id_fi and id1.id_pe = cp.id_pe and id1.LosKZ = 0 inner join {0}id id2 on id2.mandnr = id1.mandnr and id2.LosKZ = 0 inner join {0}id id3 on id3.id = id1.RechteGrId and id3.LosKZ = 0 where cp.id = ? and cp.LosKZ = 0 and id3.f7019 = 1
	union all
	select id1.id, 0 from {0}cp cp inner join {0}id id1 on id1.id_fi = cp.id_fi and id1.id_pe = cp.id_pe and id1.LosKZ = 0 inner join {0}id id3 on id3.id = id1.RechteGrId and id3.LosKZ = 0 where cp.id = ? and cp.LosKZ = 0 and id3.f7019 = 0
	union all
	select id.id, 0 from {0}id id inner join bea on bea.adv = 0 and id.VorgesetzterId = bea.id and id.LosKZ = 0
	union all
	select id.id, 0 from {0}id id inner join bea on bea.adv = 0 and id.VertretungId = bea.id and id.LosKZ = 0
)
select
	id.Bezeichnung rep, 
	fi.F7007 prospect,
	fi.Firma client,
	ISNULL(ma.[1], 0) [1],
	ISNULL(ma.[2], 0) [2],
	ISNULL(ma.[3], 0) [3],
	ISNULL(ma.[4], 0) [4],
	ISNULL(ma.[5], 0) [5],
	ISNULL(ma.[6], 0) [6],
	ISNULL(ma.[7], 0) [7],
	ISNULL(ma.[8], 0) [8],
	ISNULL(ma.[9], 0) [9],
	ISNULL(ma.[10], 0) [10],
	ISNULL(ma.[11], 0) [11],
	ISNULL(ma.[12], 0) [12],
	ISNULL(SUM(case cast(ent.F7018 / 10000 AS int) when ? then lng.F7012 else 0 end), 0) annee,
	ISNULL(SUM(case cast(ent.F7018 / 10000 AS int) when ? then lng.F7012 else 0 end), 0) precedent
from bea
inner join {0}ID id on id.ID = bea.id
inner join {0}SB sb on sb.BearbeiterId = bea.id
inner join {0}FI fi on fi.ID = sb.ID_FI and fi.ID_FI_250 > 0
left outer join 
(
	select id.ID rep, ma.ID_FI fi,
	SUM(case cast(ma.Datum_DT / 100000000000 as int) % 100 when 1 then 1 else 0 end) [1],
	SUM(case cast(ma.Datum_DT / 100000000000 as int) % 100 when 2 then 1 else 0 end) [2],
	SUM(case cast(ma.Datum_DT / 100000000000 as int) % 100 when 3 then 1 else 0 end) [3],
	SUM(case cast(ma.Datum_DT / 100000000000 as int) % 100 when 4 then 1 else 0 end) [4],
	SUM(case cast(ma.Datum_DT / 100000000000 as int) % 100 when 5 then 1 else 0 end) [5],
	SUM(case cast(ma.Datum_DT / 100000000000 as int) % 100 when 6 then 1 else 0 end) [6],
	SUM(case cast(ma.Datum_DT / 100000000000 as int) % 100 when 7 then 1 else 0 end) [7],
	SUM(case cast(ma.Datum_DT / 100000000000 as int) % 100 when 8 then 1 else 0 end) [8],
	SUM(case cast(ma.Datum_DT / 100000000000 as int) % 100 when 9 then 1 else 0 end) [9],
	SUM(case cast(ma.Datum_DT / 100000000000 as int) % 100 when 10 then 1 else 0 end) [10],
	SUM(case cast(ma.Datum_DT / 100000000000 as int) % 100 when 11 then 1 else 0 end) [11],
	SUM(case cast(ma.Datum_DT / 100000000000 as int) % 100 when 12 then 1 else 0 end) [12]
	from {0}ID id
	inner join {0}MA ma on ma.BeaId = id.ID or CHARINDEX(right(concat('000000000', id.ID), 9), ma.TeilnehmerIds) > 0
	where ma.Kontakt in (1, 210) and ma.LosKZ = 0 and cast(ma.Datum_DT / 1000000000 as int) between ? and ?
	group by id.ID, ma.ID_FI
) ma on ma.rep = id.ID and ma.fi = fi.ID 
left outer join 
(
	{0}AU au
	inner join {0}UP up on up.ID_AU = au.ID and up.LosKZ = 0
	inner join {0}C008 lng on lng.ID_UP = up.ID and lng.DEL = 0
	inner join {0}C007 ent on ent.ID = lng.ID_C007 and ent.DEL = 0 and (ent.F7018 between ? and ? or ent.F7018 between ? and ?)
	inner join {0}AR ar on ar.ID = lng.ID_AR and ar.LosKZ = 0 {2}{3}
) on au.ID_FI = fi.ID and ent.VerkaeuferId = id.ID and au.loskz = 0
where 1 = 1
{1}
group by id.Bezeichnung, fi.Firma, fi.F7007, ma.[1], ma.[2], ma.[3], ma.[4], ma.[5], ma.[6], ma.[7], ma.[8], ma.[9], ma.[10], ma.[11], ma.[12]
order by id.Bezeichnung asc, fi.F7007 desc, fi.Firma asc

Du coup mal à la tête plus vendredi, j'ai perdu les pédales