Select récursive pour build arborescence ne trouve pas le record Root

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:

---

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:

---

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 :

Citation:

---

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! :)

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

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:

---

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

---

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:

---

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:

---

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.)
Pièce jointe 502040

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

Au final votre rerquête devrait ressembler à 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

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 +

Citation:

---

Envoyé par Firlfire

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

Citation:

---

Envoyé par Firlfire

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

Citation:

---

Envoyé par Firlfire

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 !

Citation:

---

Envoyé par Firlfire

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:

---

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 ;

---