Requete récursive simple

Version imprimable

22/01/2009, 14h05
Louis-Guillaume Morand

Requete récursive simple

Pour une appli, j'ai une hierarchie de dossiers représentés dans une table (simplifiée ici)

Citation:

T_DOSSIER
-----------
dos_id
dos_name
dos_parent (pointe vers un dos_id)

mon idée est de choisir un dossier et de retourner tous les dossiers enfants et le dossier lui même (les ids).
Ces id me serviront comme filtre sur une autre requete. En gros, ca sera, chercher un fichier qui se trouve dans un dossier ou ses sous-dossiers.

J'ai un peu du mal à voir comment faire ma reqûete proprement. Une UNION m'aiderait-elle?

merci d'avance
22/01/2009, 14h35
Cybher

salut LGM,

si j'ai bien compris, tu as un dos_name en entrée et tu veux retourner son id ainsi que les id de ses dossiers enfants? (sur un seul niveau ou plus?)
22/01/2009, 14h37
Louis-Guillaume Morand

j'ai un dos_id en entrée, et je voudrais que ca me retourne

dos_id + tous les dos_id de ceux qui ont dos_parent= dos_id
sur plusieurs niveaux bien évidemment, sinon ca serait plus drole

Salut !

Oui, c'est bien UNION ALL que tu veux...
Code:

1 2 3 4 5 6 7 8 SELECT * FROM T_DOSSIER WHERE dos_parent = leparent UNION ALL SELECT * FROM T_DOSSIER WHERE dos_id = leparent
L'autre solution, c'est de disjonctionner tes conditions...
Code:

1 2 3 4 5 SELECT * FROM T_DOSSIER WHERE dos_parent = leparent OR dos_id = leparent

22/01/2009, 14h49
Waldar

Mais non pacmann, il veut gérer une hiérarchie.

La solution dépend de votre SGBD. Cherchez l'article de SQLPro sur les CTEs récursives (SQL Normatif), ou l'article de Xo sur le START WITH CONNECT BY (Oracle / Postgres / DB2).
22/01/2009, 14h56
Louis-Guillaume Morand

SQL serveur.

le union All ne marcherait pas? je pensais que ca ferait hierarchiquement. je fais essayer de tester vite fait
22/01/2009, 15h02
Louis-Guillaume Morand

je confirme, la solution ne me retourne que le premier niveau des enfants, ce qui ne me convient pas :(

j'ai tenté de suivre le tuto de sqlpro mais ca me pete une erreur à l'execution (sorte de boucle infinie bloquée par SQL serveur
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 ALTER PROCEDURE [dbo].[test] @dos_id int AS BEGIN SET NOCOUNT ON; WITH tree (id) AS ( SELECT DRY_ID FROM T_DIRECTORY WHERE dry_parent = @dos_id UNION ALL SELECT DRY_ID FROM T_DIRECTORY D INNER JOIN tree t ON t.id = D.DRY_ID) SELECT * FROM tree END
j'approche, je dois juste descendre plutot que monter l'arborescence. Je vais réussir, je le dois :D

03/02/2009, 11h39
Louis-Guillaume Morand

bon je suis toujours bloqué sur cette requête toute bête, et j'aimerai vraiment éviter de passer par une fonction récursive.

J'ai beau lire et relire des tutos sur les CTE, je me tape des exceptions

Citation:

Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Le tuto de SQLPro est bien écrit et très clair et je croyais que ca s'arrêtait dès que ca ne rencontrai plus de valeur correspondant, or ma hierarchie a trois niveaux et ca plante. je suis très loin des 100 niveaux de l'erreur :/

any idea?
03/02/2009, 11h50
SQLpro

Donne nous le DDL et les INSERT de ton modèle !

Je te mettrais la requête. Mais tu es sur la bonne voie....

A +
03/02/2009, 14h25
Louis-Guillaume Morand

1 pièce(s) jointe(s)

Citation:

CREATE TABLE [dbo].[T_DIRECTORY](
[DRY_ID] [int] IDENTITY(1,1) NOT NULL,
[DRY_LABEL] [nvarchar](50) NULL,
[DRY_PARENT] [int] NULL
) ON [PRIMARY]

bref, une table somme toute très simple et ressemblant à ton article.

quant à l'insert, je pense qu'une capture parlera mieux :)

merci

l'objectif, serait au final de faire une requete qui me retourne tous les ID d'un dossier ainsi que les IDs des dossiers enfants (sur plusieurs niveaux), ce, afin de faire une recherche dans une arborescence précise
03/02/2009, 14h29
SQLpro

Une capture je peut pas la lancer dans SQL Server pour faire un jeu d'essais. !!!!!

Lis cette page : http://www.developpez.net/forums/d96...vement-poster/
tu y trouvera un lien vers un outil pour générer des INSERT

A +

génial les deux scripts mais ils marchent pas

Citation:

Msg 8155, Level 16, State 2, Line 1
No column was specified for column 1 of 'TTT'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'SQL_COMMAND'.

pour le tien (qui s'installe parfaitement pourtant)

Citation:

Checking for the existence of this procedure
Msg 2812, Level 16, State 62, Line 3
Could not find stored procedure 'master.dbo.sp_MS_upd_sysobj_category'.
Created the procedure
Msg 2812, Level 16, State 62, Line 4
Could not find stored procedure 'master.dbo.sp_MS_upd_sysobj_category'.
Granting EXECUTE permission on sp_generate_inserts to all users
Done

pour l'uatre, si je force à utiliser autre chose que la table master (sur laquelle j'ai zéro droits)

bref, le script: (ps: faut enlever l'identity de mon create table), c'est le seul moyen d'etre sûr des bons ID je crois
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 INSERT INTO [T_DIRECTORY] ([DRY_ID],[DRY_LABEL],[DRY_PARENT]) VALUES (1, '1',NULL) INSERT INTO [T_DIRECTORY] ([DRY_ID],[DRY_LABEL],[DRY_PARENT]) VALUES (2, '2',NULL) INSERT INTO [T_DIRECTORY] ([DRY_ID],[DRY_LABEL],[DRY_PARENT]) VALUES (3, '3',NULL) INSERT INTO [T_DIRECTORY] ([DRY_ID],[DRY_LABEL],[DRY_PARENT]) VALUES (4, '4',NULL) INSERT INTO [T_DIRECTORY] ([DRY_ID],[DRY_LABEL],[DRY_PARENT]) VALUES (5, '5',NULL) INSERT INTO [T_DIRECTORY] ([DRY_ID],[DRY_LABEL],[DRY_PARENT]) VALUES (6, '1-1',1) INSERT INTO [T_DIRECTORY] ([DRY_ID],[DRY_LABEL],[DRY_PARENT]) VALUES (7, '1-2',1) INSERT INTO [T_DIRECTORY] ([DRY_ID],[DRY_LABEL],[DRY_PARENT]) VALUES (8, '1-1-1',6) INSERT INTO [T_DIRECTORY] ([DRY_ID],[DRY_LABEL],[DRY_PARENT]) VALUES (9, '2-1',2) INSERT INTO [T_DIRECTORY] ([DRY_ID],[DRY_LABEL],[DRY_PARENT]) VALUES (10, '2-2',2) INSERT INTO [T_DIRECTORY] ([DRY_ID],[DRY_LABEL],[DRY_PARENT]) VALUES (11, '3-1',3) INSERT INTO [T_DIRECTORY] ([DRY_ID],[DRY_LABEL],[DRY_PARENT]) VALUES (12, '5-1',5)
j'ai un peu changé mais ca donne l'idée de l'existant que je pourrai avoir.

faudrait que je puisse selectionner le dossier "1" et que ca me retourne
1,6,7,8

Code:

1
2
3
4
5
CREATE TABLE [dbo].[T_DIRECTORY](
[DRY_ID] [int] IDENTITY(1,1) NOT NULL,
[DRY_LABEL] [nvarchar](50) NULL,
[DRY_PARENT] [int] NULL
) ON [PRIMARY]

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
SET IDENTITY_INSERT [T_DIRECTORY] ON
INSERT INTO [T_DIRECTORY] ([DRY_ID],[DRY_LABEL],[DRY_PARENT])
VALUES (1, '1',NULL)
INSERT INTO [T_DIRECTORY] ([DRY_ID],[DRY_LABEL],[DRY_PARENT])
VALUES (2, '2',NULL)
INSERT INTO [T_DIRECTORY] ([DRY_ID],[DRY_LABEL],[DRY_PARENT])
VALUES (3, '3',NULL)
INSERT INTO [T_DIRECTORY] ([DRY_ID],[DRY_LABEL],[DRY_PARENT])
VALUES (4, '4',NULL)
INSERT INTO [T_DIRECTORY] ([DRY_ID],[DRY_LABEL],[DRY_PARENT])
VALUES (5, '5',NULL)
INSERT INTO [T_DIRECTORY] ([DRY_ID],[DRY_LABEL],[DRY_PARENT])
VALUES (6, '1-1',1)
INSERT INTO [T_DIRECTORY] ([DRY_ID],[DRY_LABEL],[DRY_PARENT])
VALUES (7, '1-2',1)
INSERT INTO [T_DIRECTORY] ([DRY_ID],[DRY_LABEL],[DRY_PARENT])
VALUES (8, '1-1-1',6)
INSERT INTO [T_DIRECTORY] ([DRY_ID],[DRY_LABEL],[DRY_PARENT])
VALUES (9, '2-1',2)
INSERT INTO [T_DIRECTORY] ([DRY_ID],[DRY_LABEL],[DRY_PARENT])
VALUES (10, '2-2',2)
INSERT INTO [T_DIRECTORY] ([DRY_ID],[DRY_LABEL],[DRY_PARENT])
VALUES (11, '3-1',3)
INSERT INTO [T_DIRECTORY] ([DRY_ID],[DRY_LABEL],[DRY_PARENT])
VALUES (12, '5-1',5)
SET IDENTITY_INSERT [T_DIRECTORY] OFF

The magic querie... !

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
WITH 
T_RECURSION AS
(SELECT DRY_ID AS ORIGINE, *, 0 AS LEVEL, 
        CAST(DRY_ID AS VARCHAR(MAX)) AS CHEMIN
 FROM   T_DIRECTORY
 WHERE  DRY_PARENT IS NULL --> les racines des arbo
 UNION  ALL
 SELECT ORIGINE, Tnext.*, LEVEL + 1 AS LEVEL, 
        CHEMIN + '.' + CAST(Tnext.DRY_ID AS VARCHAR(32)) 
 FROM   T_DIRECTORY AS Tnext
        INNER JOIN T_RECURSION AS Tpred
              ON Tpred.DRY_ID = Tnext.DRY_PARENT)
SELECT REPLICATE('.', LEVEL) + CAST(DRY_ID AS VARCHAR(32)) AS INDENT, *
FROM   T_RECURSION
ORDER  BY ORIGINE, LEVEL

The result :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
INDENT        ORIGINE     DRY_ID      DRY_LABEL       DRY_PARENT  LEVEL       CHEMIN
------------ ----------- ----------- --------------- ----------- ----------- -------------
1            1           1           1               NULL        0           1
.6           1           6           1-1             1           1           1.6
.7           1           7           1-2             1           1           1.7
..8          1           8           1-1-1           6           2           1.6.8
2            2           2           2               NULL        0           2
.9           2           9           2-1             2           1           2.9
.10          2           10          2-2             2           1           2.10
3            3           3           3               NULL        0           3
.11          3           11          3-1             3           1           3.11
4            4           4           4               NULL        0           4
5            5           5           5               NULL        0           5
.12          5           12          5-1             5           1           5.12

T'as tout ce qui te faut....
A toi de voir ce que tu veut conserver !

A +

version que j'avais
Code:

1 2 3 4 5 6 7 8 9 10 11 WITH tree (id) AS ( SELECT DRY_ID FROM T_DIRECTORY WHERE dry_parent = @dos_id UNION ALL SELECT DRY_ID FROM T_DIRECTORY D INNER JOIN tree t ON t.id = D.DRY_ID) SELECT * FROM tree
il m'a suffit grâce à ton code, de changer
Code:

1 2 INNER JOIN tree t ON t.id = D.DRY_ID)
par
Code:

1 2 INNER JOIN tree t ON t.id = D.DRY_PARENt)
et ca marche parfaitement.

Ce qui m'étonne c'est que j'étais sûr de l'avoir testé. mais au final, en partant de ton code, et en le raccourcissant peu à peu, j'ai eu ce que je voulais.

En tout cas, il est génial. origine, path, etc, ca doit pas être la première fois que tu tombes sur ce cas :)

encore merci

03/02/2009, 16h54
SQLpro

Citation:

En tout cas, il est génial. origine, path, etc, ca doit pas être la première fois que tu tombes sur ce cas

Oh que non, mais entre nous c'est pas le modèle le plus performant. Le mieux serait de modéliser votre arbre de manière intervallaire comme je l'ais décrit ici :
http://sqlpro.developpez.com/cours/arborescence/

A +
03/02/2009, 16h59
Louis-Guillaume Morand

j'avais regardé l'article des arbo, mais la technique intervallaire permet-elle une mise à jour aisée? rajout de noeuds, etc?

je vois le fonctionnement des intervales mais si je rajoute un élément en plein milieu, cela ne va-t-il pas tout casser?