Discussion :

[Firlfire]

Bonjour,

Je souhaite mettre en place une requête récursive qui me permette de retourner un "chemin", donc remonter une arborescence en partant de l'ID du dernier "fils".

La Structure:

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
Create Table PROJECTS(
	K_PROJECT PRIMARY KEY,
	name varchar(50)
);
 
Create Table PROJ_PROJ(
	K_PROJECT integer References PROJECTS(K_PROJECT),
	K_PROJECT2 integer References PROJECTS(K_PROJECT),
	CONSTRAINT PK_PROJ_PROJ PRIMARY KEY (K_PROJECT,K_PROJECT2)
);
 
insert into table PROJECTS VALUES
	(1, 'Roots'),
	(2, 'Child2'),
	(3, 'Child3'),
	(4, 'Child4'),
	(5, 'Child5'),
	(6, 'Child6'),
	(7, 'Child7');
 
insert into table PROJ_PROJ(K_PROJECT,K_PROJECT2) VALUES
	(1, 2),
	(1, 3),
	(2, 4),
	(2, 5),
	(5, 6),
	(6, 7);

La clé de l'enfant est PROJ_PROJ.K_PROJECT2
Par exemple si je passe 7 je souhaite retrouver 'Roots/Child2/Child5/Child6/Child7'
J'ai essayer en m'aidant de ça mais ma requête ne trouve pas le 'Roots'.
Requête :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
WITH tree(data, parentId) as (
	Select p.NAME, pp.K_PROJECT
		from PROJECTS p
		join PROJ_PROJ pp on pp.K_PROJECT2=p.K_PROJECT
			where p.K_PROJECT=430
	UNION ALL
	Select p.NAME, pp.K_PROJECT
		from PROJECTS p
		join PROJ_PROJ pp on pp.K_PROJECT2=p.K_PROJECT
		join tree t on t.parentId=pp.K_PROJECT2
)
 
select * from tree

Le problème c'est que la clé du Roots (ici 1) n'est pas présent dans PROJ_PROJ.K_PROJECT2.
J'ai voulu essayer avec un LEFT JOIN mais j'ai une erreur :

La jointure externe n'est pas autorisée dans la partie récursive d'une expression de table commune récursive 'tree'.

Je n'en suis pas encore à concaténer les name (même sis c'est le but final), j'en suis à simplement retrouver tous les name.
De plus elle sera destiné à être utilisé dans une autre requete (et d'où viendra "where p.K_PROJECT={id}"

Merci d'avance!

[soazig]

Bonsoir,
Tu as oublié de réutiliser tree dans la définition de tree , or c'est ce qui fait la récursion, relis l'exemple 10 de l'article on dirait que tu t'es arrêté à l'exemple 9 qui n'est pas récursif.
Bonne soirée
Soazig

[aieeeuuuuu]

Bonjour,

Le problème est effectivement celui que vous avez indiqué. Vous avez une ligne de moins dans la table associative que d'étapes dans le chemin. il vous faudra à un moment donnée traiter les deux colonnes pour une des lignes.

Mais comme votre but final est de concaténer pour obtenir le chemin, vous pouvez par exemple le faire dans l''ancrage de la CTE, par exemple comme ceci :

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
16
17
18
19
 
WITH tree(parentId, chemin) as (
	Select pp.K_PROJECT, CAST(CONCAT(REVERSE(pPere.NAME), '/', REVERSE(pFils.NAME)) AS varchar(MAX))
		from PROJECTS pFils
		join PROJ_PROJ pp on pp.K_PROJECT=pFils.K_PROJECT
		JOIN PROJECTS pPere ON pPere.K_PROJECT = pp.K_PROJECT2
 
		where pp.K_PROJECT2=7
 
	UNION ALL
	Select  pp.K_PROJECT, CAST(CONCAT(chemin, '/', REVERSE(P.Name)) AS varchar(MAX))
		from PROJECTS p
		join PROJ_PROJ pp on pp.K_PROJECT=p.K_PROJECT
		join tree t on t.parentId=pp.K_PROJECT2
)
 
select REVERSE(chemin)
from tree
WHERE parentId = 1

[Firlfire]

Bonjour!

Merci pour vos réponses.

@aieeeuuuuu je ne comprends pas à quoi servent les REVERSE dans votre query ? Je suis arrivé à en faire une sans les "REVERSE" (performance peut-etre ?).
La query marche (j'ai juste du rajouter des Collate pour le '/').

La query que j'ai faites :

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
with TREE(parentId, chemin) as (
	select pp.K_PROJECT, Cast(Concat(pPere.NAME, '/' Collate SQL_Latin1_General_CP1_CI_AI, pFils.NAME) as varchar(MAX))
		from PROJECTS pFils
		join PROJ_PROJ pp on pp.K_PROJECT2=pFils.K_PROJECT
		JOIN PROJECTS pPere on pp.K_PROJECT=pPere.K_PROJECT
		where pp.K_PROJECT2=7
 
	union ALL
	select  pp.K_PROJECT, Cast(Concat(parent.Name, '/' Collate SQL_Latin1_General_CP1_CI_AI, chemin) as varchar(MAX))
		from PROJECTS parent
		join PROJ_PROJ pp on pp.K_PROJECT=parent.K_PROJECT
		join TREE t on t.parentId=pp.K_PROJECT2
)
select TOP 1 chemin from TREE order by Len(chemin) DESC;

Je ne peux pas connaitre le parentId, donc comme ci-dessus je tri par longueur et prend le premier (pour un childId donné, logiquement ce sera toujours le plus long).

En revanche je n'arrive pas à l'utiliser comme je souhaite.
Le vrai contexte : dans la DB il y a une table DOCUMENTS qui contient une liste de Documents, et le but est de pouvoir récupérer le chemin de chacun d'eux avec une query. Donc je voulais utiliser cette CTE dans une autre "main query" pour avoir un résultat comme ceci :

K_DOCUMENT	NAME	chemin
134	Docu test	Roots/Child2/Child5
178	Docu re-test	Roots/Child2/Child5/Child6/Child7

J'ai cherché comment je pourrais passer la clé du child dans la CTE (pour la mettre dans le "where pp.K_PROJECT2={monId}"), apparemment ce n'est pas possible. Peut-etre qu'un pivot est mieux ?
De plus, il y a environ 19 000 documents dans la table, donc j'ai peur que faire une telle query recursive pour chacun prenne un peu (beaucoup) de temps..
Donc j'ai pensé à générer les différents chemin possibles dans un variable que je pourrais utiliser, le probleme c'est que dans cette variables il me faut seulement les chemins qui "rompent" la recursion mais je n'arrive pas a trouver la condition (le "where pp.K_PROJECT2 in(5,7)" est la pour l'exemple, il n'y sera plus par la suite):

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
with TREE(parentId, sourceId, isFull, chemin) as (
	select pp.K_PROJECT, pp.K_PROJECT2,
		0, -- ici ce ne sera jamais le "chemin complet"
		Cast(Concat(pPere.NAME, '/', pFils.NAME) as varchar(MAX))
		from PROJECT pFils
		join PROJ_PROJ pp on pp.K_PROJECT2=pFils.K_PROJECT
		JOIN PROJECT pPere on pp.K_PROJECT=pPere.K_PROJECT
		where pp.K_PROJECT2 in(5,7)
 
	union ALL
	select  pp.K_PROJECT, t.sourceId, -- sourceId est la cle du child "initial"
		CASE when pp.K_PROJECT2 is null then 0 else 1 END, -- comment savoir si c'est bien la boucle qui stop la récursion ?
		Cast(Concat(parent.Name, '/', chemin) as varchar(MAX))
		from PROJECT parent
		join PROJ_PROJ pp on pp.K_PROJECT=parent.K_PROJECT
		join TREE t on t.parentId=pp.K_PROJECT2
)
 
select * into #tmp from TREE
select * from #tmp
 
-- utilisé dans quelque chose comme
/*
select d.K_DOCUMENT, d.NAME, p.chemin
	from DOCUMENTS d
	join DOCU_PROJ dp on dp.K_DOCUMENT=d.K_DOCUMENT
	join #tmp p on p.isFull=1 and p.sourceId=dp.K_PROJECT
*/

Le screen ci-dessous sont mes résultats, sauf que seules les 2 ddernière lignes m'interessent (je ne peux ni filtrer sur le parentId ni sur son nom.)


P.S : j'ai essayer de mettre un SQL Fiddle en place, mais quelques soucis.. Je le fais dès que possible !

[SQLpro]

Au final votre rerquête devrait ressembler à ceci :

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
16
17
18
19
20
21
22
23
24
25
26
WITH 
tree (OrigineID,
      parentId, 
      chemin,
      NIVEAU)
AS (SELECT pp.K_PROJECT, pp.K_PROJECT,
           CAST(CONCAT(REVERSE(pPere.NAME), '/', REVERSE(pFils.NAME)) AS VARCHAR(MAX)),
           0 AS NIVEAU
    FROM   PROJECTS pFils
           JOIN PROJ_PROJ pp ON pp.K_PROJECT = pFils.K_PROJECT
           JOIN PROJECTS pPere ON pPere.K_PROJECT = pp.K_PROJECT2
    WHERE  pp.K_PROJECT2 = 7
    UNION  ALL
    SELECT OrigineID, pp.K_PROJECT, 
           CAST(CONCAT(chemin, '/', REVERSE(P.Name)) AS VARCHAR(MAX)),
           NIVEAU + 1
    FROM   PROJECTS p
           JOIN PROJ_PROJ pp ON pp.K_PROJECT = p.K_PROJECT
           JOIN tree t ON t.parentId = pp.K_PROJECT2),
tmax 
AS (SELECT *, RANK() OVER(PARTITION BY OrigineID
                          ORDER BY NIVEAU DESC) AS R
    FROM    tree)
SELECT OrigineID, REVERSE(chemin)
FROM   tree
WHERE  R = 1;

A +

[aieeeuuuuu]

@aieeeuuuuu je ne comprends pas à quoi servent les REVERSE dans votre query ? Je suis arrivé à en faire une sans les "REVERSE" (performance peut-etre ?).

Non, ce n'est pas pour les performances, votre solution est mieux je trouve.
Comme on partait de la dernière étape, j'ai pensé à ça sur le coup, mais en effet, il suffi d'inverser l'ordre des colonnes dans le CONCAT...

J'ai cherché comment je pourrais passer la clé du child dans la CTE (pour la mettre dans le "where pp.K_PROJECT2={monId}"), apparemment ce n'est pas possible.

Si, il suffit de l'ajouter dans l'ancrage et de le garder tel quel dans la partie récursive

De plus, il y a environ 19 000 documents dans la table, donc j'ai peur que faire une telle query recursive pour chacun prenne un peu (beaucoup) de temps..

L'optimisation des requêtes récursive est parfois très étonnant. Il faut tester, mais ça peut donner de bonnes perfs quand meme !

P.S : j'ai essayer de mettre un SQL Fiddle en place, mais quelques soucis.. Je le fais dès que possible !

oui, ou completer votre jeu d'essai initial pour avoir plusieurs projets


En attendant, que donne ceci

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
16
17
18
19
20
21
22
23
24
25
 
WITH tree(parentId, chemin, Depart, niveau) as (
	Select pp.K_PROJECT, CAST(CONCAT(pFils.NAME, '/' ,pPere.NAME ) AS varchar(MAX)), pp.K_PROJECT2  AS Depart, 1 as niveau
		from PROJECTS pFils
		join PROJ_PROJ pp on pp.K_PROJECT=pFils.K_PROJECT
		JOIN PROJECTS pPere ON pPere.K_PROJECT = pp.K_PROJECT2
 
 
 
	UNION ALL
	Select  pp.K_PROJECT, CAST( CONCAT( P.Name, '/' ,chemin) AS varchar(MAX)), Depart, niveau + 1
		from PROJECTS p
		join PROJ_PROJ pp on pp.K_PROJECT=p.K_PROJECT
		join tree t on t.parentId=pp.K_PROJECT2
)
 , tmp AS (
	SELECT chemin,Depart,  ROW_NUMBER() OVER(PARTITION BY Depart ORDER BY Niveau DESC) AS RN
	FROM tree
 
)
SELECT chemin
FROM tmp
WHERE Depart = 7 
AND RN = 1
;