Discussion :

[Louis-Guillaume Morand]

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

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

[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?)

[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

[pacmann]

Salut !

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

Code :

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

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 :

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

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

[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).

[Louis-Guillaume Morand]

SQL serveur.


le union All ne marcherait pas? je pensais que ca ferait hierarchiquement. je fais essayer de tester vite fait

[Louis-Guillaume Morand]

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

[Louis-Guillaume Morand]

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 :

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

[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

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?

[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 +

[Louis-Guillaume Morand]

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

[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 +

[Louis-Guillaume Morand]

génial les deux scripts mais ils marchent pas

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)

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 :

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

[SQLpro]

Code :

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

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 :

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

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

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

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 +

[Louis-Guillaume Morand]

version que j'avais

Code :

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

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 :

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

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

par

Code :

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

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

[SQLpro]

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 +

[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?