Requête récursive pour obtenir le chemin à partir d'une arborescence

Version imprimable

Bonjour.

J'ai la table suivante:

Code:

1
2
3
4
5
6
7
CREATE TABLE "project" (
	"idobject" serial,
	"idparent" integer,
	"project_name" text,
	PRIMARY key (idobject),
	FOREIGN key (idparent) REFERENCES project (idobject)
) INHERITS (object);

Je cherche à obtenir le chemin des arborescences de projet.

Actuellement je fais ça :

Code:

1
2
3
4
5
6
7
SELECT n1.idobject, 
	CASE WHEN n3.project_name IS NULL THEN '' ELSE n3.project_name || ' / ' END
	|| CASE WHEN n2.project_name IS NULL THEN '' ELSE n2.project_name || ' / ' END 
	|| n1.project_name AS project_path FROM project AS n1
LEFT OUTER JOIN project AS n2 ON n1.idparent = n2.idobject
LEFT OUTER JOIN project AS n3 ON n2.idparent = n3.idobject
ORDER BY project_path;

et j'obtiens

 idobject |      project_path       
----------+-------------------------
  7546762 | prj1
  7546770 | prj1 / prj1.1
  7546772 | prj1 / prj1.2
  7546766 | prj2

Ce qui est exactement ce que je veux, sauf que j'aurais voulu faire ça avec une requête récursive afin que cela fonctionne quel que soit le nombre de niveaux (ce qui n'est pas le cas ici).

Est-ce possible ?

13/02/2018, 10h03
ced

Bonjour,

Oui, c'est possible à l'aide d'une requête récursive.
Tout est expliqué ici : http://sqlpro.developpez.com/cours/s...te-recursives/
Ça fonctionne également sous PostgreSQL.

Oui...
Code:

1 2 3 4 5 6 7 8 9 10 11 12 WITH RECURSIVE T0 AS (SELECT idobject AS id_racine, project_name FROM project WHERE idparent IS NULL --> les racines UNION ALL SELECT T0.id_racine, CONCAT(T0.project_name, ' / ', T1.project_name) FROM T0 JOIN project AS T1 ON T0.id_racine = T1.idparent) SELECT * FROM T0;
mais vous gagneriez notablement en performances en utilisation une représentation intervallaire de votre arborescence.
Lisez les article que j'ai écrit à ce sujet :
http://sqlpro.developpez.com/cours/arborescence/
https://blog.developpez.com/sqlpro/p...vallaire_proce
https://blog.developpez.com/sqlpro/p...edure_de_depla
https://blog.developpez.com/sqlpro/p...edure_de_derec
https://blog.developpez.com/sqlpro/p...-intervallaire
https://blog.developpez.com/sqlpro/p...-intervallaire

A +

13/02/2018, 10h08
SQLpro

Citation:

Envoyé par ced

Bonjour,

Oui, c'est possible à l'aide d'une requête récursive.
Tout est expliqué ici : http://sqlpro.developpez.com/cours/s...te-recursives/
Ça fonctionne également sous PostgreSQL.

Avec une petite différence : le mot clef RECURSIVE n'est pas obligatoire dans la norme, mais SQL Server l'impose....

A +
13/02/2018, 14h01
webshaker

Merci.

La requête proposée doit sans doute fonctionner car il ne semble pas y avoir de problème syntaxique,
Mais après avoir mis à plat mon serveur pendant 2 minutes, j'ai décidé de l'interrompre !

pourtant il n'y a que 20 enregistrement dans ma table project !
Je suppose qu'il doit y avoir un truc qui boucle à l'infini !
13/02/2018, 14h33
SQLpro

C'est possible... Vu que tu ne nous a pas donné un jeu d'essais pour tester, j'ai testé dans le vide.

maintenant si tu respecte la charte de postage en nous donnant des :
INSERT INTO ...
alors on pourra tester.

A +

autant pour moi

CREATE TABLE project (
    idobject serial,
    idparent integer,
    project_name text,
)

INSERT INTO project VALUES (7546762, NULL, 'Bureau Virtuel');
INSERT INTO project VALUES (7546770, 7546762, 'Agenda');
INSERT INTO project VALUES (7546772, 7546762, 'Kernel');
INSERT INTO project VALUES (7547715, 7546762, 'Gestionnaire de projet');
INSERT INTO project VALUES (7547750, NULL, 'Serveur');
INSERT INTO project VALUES (7547794, 7547750, 'Scripts');
INSERT INTO project VALUES (7547795, 7547750, 'Administration');
INSERT INTO project VALUES (7547796, NULL, 'Services Utilisateurs');
INSERT INTO project VALUES (7547797, 7547796, 'Support');
INSERT INTO project VALUES (7547798, 7547796, 'E-procurement');
INSERT INTO project VALUES (7547799, 7547796, 'Appel d''offre');
INSERT INTO project VALUES (7546766, NULL, 'Client');
INSERT INTO project VALUES (7547890, 7546766, 'Support');
INSERT INTO project VALUES (7547891, 7546766, 'Développement');

Il n'y a même que 14 lignes.

Je te donne tout ça pour voir comment on fait, mais finalement je vais précalculer le chemin complet.
Ca sera plus rapide !

Petite correction :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
WITH RECURSIVE
T0 AS
(SELECT idobject AS id_racine,  project_name
 FROM   project
 WHERE  idparent IS NULL --> les racines
 UNION ALL
 SELECT T1.idobject, CONCAT(T0.project_name, ' / ', T1.project_name)
 FROM   T0
        JOIN project AS T1
             ON T0.id_racine = T1.idparent)
SELECT *
FROM   T0

Et pour avoir uniquement les lignes "finales" :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
WITH RECURSIVE
T0 AS
(SELECT idobject  AS id_racine,  idobject AS id_object, project_name, 0 AS etape
 FROM   project
 WHERE  idparent IS NULL --> les racines
 UNION ALL
 SELECT T0.id_racine, T1.idobject, CONCAT(T0.project_name, ' / ', T1.project_name), etape + 1
 FROM   T0
        JOIN project AS T1
             ON T0.id_object = T1.idparent),
T1 AS
(SELECT *, MAX(etape) OVER(PARTITION BY id_racine) AS last_etape
 FROM   T0)
SELECT id_racine, project_name AS project_path 
FROM   T1
WHERE  etape = last_etape
ORDER  BY 1

À me lire : http://sqlpro.developpez.com/cours/s...te-recursives/

A +

13/02/2018, 17h52
webshaker

Ah ouais. ça fonctionne.
Merci.

Ca retourne pas le bon ID (moi j'aurai voulu l'ID de l'élément le plus bas)
Mais je devrais être capable de corriger moi-même.
16/02/2018, 17h25
aieeeuuuuu

Bonjour,

Citation:

Envoyé par SQLpro

Avec une petite différence : le mot clef RECURSIVE n'est pas obligatoire dans la norme, mais SQL Server l'impose....

Sans doute voulais-tu dire que PostGre l'impose (SQL Server lui, la refuse, dans tous les cas).

Par ailleurs, la norme ne l'impose pas pour la déclaration d'une CTE,

Citation:

<with clause> ::= WITH [ RECURSIVE ] <with list>

mais s'il n'est pas précisé, alors la CTE ne peut être récursive :

Citation:

a) If a <with clause> WC immediately contains RECURSIVE, then WC and its <with list
element>s are said to be potentially recursive. Otherwise they are said to be non-recursive.
b) Let n be the number of <with list element>s and let WLEi and WLEj be the i-th and j-th
<with list element>s for every (i,j) with i ranging from 1 (one) to n and j ranging from i+1
to n. If WLEi is not potentially recursive, then it shall not immediately contain the <query
name> immediately contained in WLEj.
c) If the <with clause> is non-recursive, then for all i between 1 (one) and n, the scope of the
<query name> WQN immediately contained in WLEi is the <query expression> immediately
contained in every <with list element> WLEk, where k ranges from i+1 to n, and the <query
expression body> immediately contained in <query expression>. A <table or query name>
contained in this scope that immediately contains WQN is a query name in scope.
d) If the <with clause> is potentially recursive, then for all i between 1 (one) and n, the scope
of the <query name> WQN immediately contained in WLEi is the <query expression>
immediately contained in every <with list element> WLEk, where k ranges from 1 (one) to
n, and the <query expression body> immediately contained in <query expression>. A <table
or query name> contained in this scope that immediately contains WQN is a query name in
scope.