Discussion :

[DiverSIG]

Bonjour,

J'ai une table avec 3 champs ITEM_ID, FATHER_ID et DESCRIPTION
DESCRIPTION correspond à un libellé de répertoire, ITEM_ID l'ID du répertoire et FATHER_ID l'ID du répertoire père.
Je voudrais écrire une requête qui à partir de l'ID d'un répertoire me renvoie le libellé du chemin complet.

Une idée?

Merci,
Nico

[aieeeuuuuu]

Bonjour,

Si la profondeur est variable, la requête récursive semble effectivement la solution.

[fsmrel]

Quel est le SGBD ?

[DiverSIG]

Le SGBD est Oracle.
Je parlais de requete récursive comme ça, mais je n'ai pas la moindre idée de comment on fait ça... vous auriez un exemple?

Merci,
Nico

[fsmrel]

Bonjour,


Voyez déjà ici et là.

[fsmrel]

Voici votre cas de figure, traité avec SQL Server, car je n’ai pas Oracle, mais la requête récursive est normalisée :


TABLE REPERTOIRE

Code SQL :

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

1
2
3
4
5
6
7
8
CREATE TABLE REPERTOIRE
 
 (
         ItemId            INT            NOT NULL
       , FatherId          INT            NOT NULL
       , Description       VARCHAR(64)    NOT NULL 
    , CONSTRAINT REPERTOIRE_PK PRIMARY KEY (ItemId) 
 ) ;

Un jeu d’essai:

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
INSERT INTO REPERTOIRE (ItemId, FatherId, Description) VALUES (1, 0, 'Item 1') ;
INSERT INTO REPERTOIRE (ItemId, FatherId, Description) VALUES (11, 1, 'Item 11') ;
INSERT INTO REPERTOIRE (ItemId, FatherId, Description) VALUES (111, 11, 'Item 111') ;
INSERT INTO REPERTOIRE (ItemId, FatherId, Description) VALUES (1111, 111, '1111') ;
INSERT INTO REPERTOIRE (ItemId, FatherId, Description) VALUES (1112, 111, 'Item 1112') ;
INSERT INTO REPERTOIRE (ItemId, FatherId, Description) VALUES (112, 11, 'Item 112') ;
INSERT INTO REPERTOIRE (ItemId, FatherId, Description) VALUES (1121, 112, '1121') ;
INSERT INTO REPERTOIRE (ItemId, FatherId, Description) VALUES (12, 1, 'Item 12') ;
INSERT INTO REPERTOIRE (ItemId, FatherId, Description) VALUES (121, 12, 'Item 121') ;
INSERT INTO REPERTOIRE (ItemId, FatherId, Description) VALUES (1211, 121, 'Item 1211') ;

La requête récursive (l’opérateur de concaténation de chaînes de caractères est ici "+", en Oracle il est à remplacer par "||") :

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 ARBRE(ItemId, Chemin) AS
   (
     SELECT ItemId, CAST(Description AS VARCHAR(MAX)) as Description
     FROM   REPERTOIRE
     WHERE  ItemId = 1
     
   UNION ALL

      SELECT y.ItemId, CAST(x.Chemin + '\' + y.Description AS VARCHAR(MAX)) AS Chemin      
      FROM   ARBRE AS x JOIN REPERTOIRE AS y ON x.ItemId = y.FatherId  
 )
SELECT *
FROM   ARBRE ;

Au résultat :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
ItemId    Chemin
     1    Item 1
    11    Item 1\Item 11
    12    Item 1\Item 12
   121    Item 1\Item 12\Item 121
  1211    Item 1\Item 12\Item 121\Item 1211
   111    Item 1\Item 11\Item 111
   112    Item 1\Item 11\Item 112
  1121    Item 1\Item 11\Item 112\1121
  1111    Item 1\Item 11\Item 111\1111
  1112    Item 1\Item 11\Item 111\Item 1112

[fsmrel]

Comment ça marche ?

Considérons à nouveau la requête récursive :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 WITH ARBRE(ItemId, Chemin) AS
   (
     SELECT ItemId, CAST(Description AS VARCHAR(MAX)) AS Description
     FROM   REPERTOIRE
     WHERE  ItemId = 1
     
   UNION ALL

      SELECT y.ItemId, CAST(x.Chemin + '\' + y.Description AS VARCHAR(MAX)) AS Chemin      
      FROM   ARBRE AS x JOIN REPERTOIRE AS y ON x.ItemId = y.FatherId  
 )
SELECT *
FROM   ARBRE ;

Le fonctionnement est le suivant :

Avec la clause WITH, On définit une vue temporaire (nommée Arbre dans l’exemple), dont les attributs correspondent au besoin : Un item (attribut ItemId) et le chemin sur lequel il se situe (attribut Chemin).

On trouve ensuite un 1er SELECT : il sert à amorcer la pompe récursive :

Code SQL :

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

1
2
3
SELECT ItemId, CAST(Description AS VARCHAR(MAX)) AS Description
FROM   REPERTOIRE
WHERE  ItemId = 1

La clause WHERE permet de définir l’item à considérer comme ancêtre.
L’opérateur CAST permet de garantir la compatibilité de type avec l’homologue de l’attribut Description dans le 2e SELECT. Pour assurer le coup, j’ai utilisé le type VARCHAR(MAX), mais j’aurais pu coder VARCHAR(512), à condition d’en faire autant dans le 2e SELECT.

Le 2e SELECT correspond à la partie récursive, il est uni par UNION ALL (exigence SQL) au SELECT qui joue le rôle d’amorce. La jointure entre la vue temporaire (ARBRE) et la table de base (REPERTOIRE) constitue le cœur de l’opération récursive :

ARBRE AS x JOIN REPERTOIRE AS y ON x.ItemId = y.FatherId

La jointure met en jeu un item de la vue (ItemId) et son père (FatherId) dans la table REPERTOIRE, histoire de rapatrier ce dernier dans la vue.

Quand pour chaque branche le niveau feuille est atteint, le processus est terminé et il n’y a plus qu’à exploiter le résultat, par exemple :

Code SQL :

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

1
2
SELECT *
FROM   ARBRE

[fsmrel]

Maintenant, vous m'objecterez que vous ignorez qui sont les ancêtres de l’item dont il faut déterminer le chemin. Dans ce cas, on va remonter dans la hiérarchie en comptant le nombre de niveaux parcourus.

Répondons à la question : Quel est le chemin de l‘item 1112 ?

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
WITH ARBRE(Niveau, ItemId, Chemin) AS
   (
     SELECT 1, ItemId, CAST('' AS VARCHAR(512)) as Description
     FROM   REPERTOIRE
     WHERE  ItemId = 1112
     
   UNION ALL

      SELECT Niveau + 1, y.FatherId , CAST(y.Description  + '\' + x.Chemin AS VARCHAR(512)) AS Chemin      
      FROM   ARBRE AS x JOIN REPERTOIRE AS y ON x.ItemId = y.ItemId  
  )
 SELECT Chemin from ARBRE
 WHERE  Niveau = (SELECT MAX(Niveau) from ARBRE) ;

Réponse :

Code :

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

1
2
Chemin
Item 1\Item 11\Item 111\Item 1112\ 

[Waldar]

Les toujours très détaillées réponses de fsmrel ne fonctionneront que si vous êtes au moins en 11gR2.

Oracle a une syntaxe historique pour gérer les requêtes récursives - depuis 1978.
Faites une recherche dans le forum Oracle sur les mots clef START WITH ou CONNECT BY.