table récursivement jointe à elle-même [Résolu]

[rstck]

Bonjour,

j'ai un petit problème à vous soumettre. Soit un ensemble E d'objet représentés de façon biunivoque par des entiers, tels que chaque objet a un parent au plus ; les parents font eux-mêmes partie de E. On représente dans une table nommée 'Objets' à deux colonnes (id | parent_id) les liens de filiation entre les objets. L'objet numéroté id a comme objet parent celui représenté par parent_id. Ma question : comment récupérer, pour chaque objet, la suite d'entiers représentant ses 'aïeux' ?

J'ai une solution 'finie', en se disant, par exemple, qu'un objet n'a au plus que 2 aïeux :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
-- réplique de la table Objets
CREATE TEMPORARY VIEW objets1 AS
	SELECT id AS parent1, parent_id AS parent_parent1
		FROM Objets;
 
-- réplique de la table Objets
CREATE TEMPORARY VIEW objets2 AS
	SELECT id AS parent1, parent_id AS parent_parent2
		FROM Objets;
 
SELECT id, parent1, parent2
	FROM 
	Objets,
	objets1,
	objets2
        WHERE 
        Objets.parent_id = objets1.parent1 AND
        objets1.parent_parent1 = objets2.parent2;

Mais je me demande s'il y a plus malin et plus 'générique'... Quelqu'un aurait une idée ? Y a-t-il des fonctions "search" ou "find" qu'on puisse utiliser ? Je ne sais pas trop comment mieux prendre le problème, ne maîtrisant pas bien PostgreSQL...

Merci pour votre aide !

[vmolines]

Bonjour,

vous avez besoin de récursivité ce qui ne peut se faire sans CTE (http://sqlpro.developpez.com/cours/s...te-recursives/). Postgre implémente apparemment les CTE, je vous laisse le soin de digérer la doc :

http://www.postgresql.org/docs/8.4/s...ries-with.html

[rstck]

Merci pour votre réponse ! Je me mets de suite à la lecture...

[bhamp0]

A voir si ça fonctionne avec une vue, mais je ne promets rien et je n'ai pas les moyens de tester.

Code :

1
2
3
4
5
6
7
8
CREATE VIEW V_Objects_Parents (id, parent_id, level) AS
(
(SELECT o.id, o.parent_id, '1' FROM Objets o)
UNION
(SELECT o.id, v.parent_id, v.level + 1
FROM Objets o
JOIN V_Objets_Parents v ON v.id = o.parent_id)
)

[rstck]

Merci ! Votre code ne fonctionne pas avec une vue, le voilà déguisé :

Code :

1
2
3
4
5
6
7
8
9
10
 
WITH RECURSIVE V_Objects_Parents (id, parent_id, level, chem) AS
(
(SELECT o.id, o.parent_id, 1, o.name FROM Objets o)
UNION
(SELECT o.id, v.parent_id, v.level + 1, v.chem || '/' || o.name
FROM Objets o
JOIN V_Objects_Parents v ON v.id = o.parent_id)
)
SELECT * FROM V_Objects_Parents

Merci à tous les deux ! NB : la doc postgre est sybilline, mais le cours sur les CTE récursives est super didactique