Requête récursive : ordre des éléments par rapport au parent

Version imprimable

Bonjour,

J'ai la requete recursive suivante:

Code:

1
2
3
4
5
6
7
8
9
10
11
 
WITH recursive enfant(idEnfant,nomEnfant,parent,level) AS
(
    SELECT d.id,d.nom,d.parent,0
    FROM domaine d where d.parent is null
  union ALL
    SELECT d.id,d.nom,d.parent,e.level+1
    FROM domaine d,enfant e
    where d.parent=e.idEnfant
)
SELECT idEnfant,nomEnfant,parent,level FROM enfant;

Elle me retourne une série de domaine:

Code:

1
2
3
4
5
6
7
8
9
 
idenfant	nomenfant	parent	 level
DOM21455 	DOM1		NULL	          0
DOM45123 	DOM2		DOM21455    1 
DOM12421 	DOM3		DOM21455    1
DOM95423 	DOM4		DOM21455    1
DOM02354 	DOM5		DOM12421    2
DOM75136 	DOM6		DOM45123    2
DOM04452 	DOM7		DOM45123    2

Je souhaiterai que la requete me retourne les domaines dans l'ordre suivant :

Code:

1
2
3
4
5
6
7
8
9
 
idenfant	nomenfant	parent	 level
DOM21455 	DOM1		NULL	 0
DOM45123 	DOM2		DOM21455 1 
	DOM75136 	DOM6		DOM45123 2
	DOM04452 	DOM7		DOM45123 2
DOM12421 	DOM3		DOM21455 1
	DOM02354 	DOM5	        DOM12421 2
DOM95423 	DOM4		DOM21455 1

Ma question est la suivante : est il possible de faire cela dans la requête ?

Merci d'avance pour toutes infos.

Chris

10/10/2013, 22h18
SQLpro

C'est ce que l'on apelle un "sibling ordering".
Ce n'est pas actuellement supporté par la norme SQL et donc pas par PG.
J'ai publié une solution dans le forum SQL Server il y a un moment... Cherchez là, mais c'est assez complexe !

A +

Bonjour,

Merci pour votre réponse.
J'y suis finalement arrivée.
Voici le résultat avec le chemin pour chacun et le nombre d'enfant

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
WITH RECURSIVE child AS
					(
			                SELECT d.id,d.nom,d.parent, 0 AS level, ARRAY[CAST(id AS VARCHAR)] AS path,x.noeud
			                FROM domaine d,( select d.parent,count(*) as noeud from domaine d group by d.parent)x
			                WHERE d.id='DOM87544915'
			                and x.parent=d.id
				        UNION ALL
			                SELECT d.id,d.nom,d.parent,c.level + 1 AS level, path || CAST(d.id AS VARCHAR),x.noeud
			                FROM child c JOIN domaine d ON d.parent = c.id left join ( select d.parent,count(*) as noeud from domaine d group by d.parent)x on x.parent=d.id
					)
					SELECT c.id,c.parent,c.nom,level,path,noeud
					FROM child c
					ORDER BY path

11/10/2013, 13h08
estofilo
A mon sens c'est le nom plutôt que l'ID que tu veux mettre dans le tableau
puisque sur ce morceau de résultat attendu:
```
idenfant	nomenfant	parent	 level
..
DOM75136 	DOM6		DOM45123 2
DOM04452 	DOM7		DOM45123 2
...
```
on voit qu'à un même niveau de hiérarchie, c'est trié par nomenfant croissant et non idenfant croissant.

Mais à part ça la technique du tableau est effectivement celle qui va bien pour résoudre ce problème.

Dans l’idéal j'aimerai bien que cela soit rangé par nom mais lorsque je remplace id par nom

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
WITH RECURSIVE children AS
					(
			                SELECT d.id,d.nom as name,d.parent, 0 AS level, ARRAY[CAST(d.nom AS VARCHAR)] AS path,x.child
			                FROM domaine d,( select d.parent,count(*) as child from domaine d group by d.parent)x
			                WHERE d.id='DOM87544915'
			                and x.parent=d.id
				        UNION ALL
			                SELECT d.id,d.nom as name,d.parent,c.level + 1 AS level, path || CAST(d.nom AS VARCHAR),x.child
			                FROM children c JOIN domaine d ON d.parent = c.id left join ( select d.parent,count(*) as child from domaine d group by d.parent)x on x.parent=d.id
					)
					SELECT c.id,c.parent,c.name,level,path,child
					FROM children c
					ORDER BY path

j'ai cette erreur :

Code:

1
2
3
4
5
6
 
ERREUR:  dans la requête récursive « children », la colonne 5 a le type character(1)[] dans le terme non
récursif mais le type global bpchar[]
LINE 3: ...  SELECT d.id,d.nom as name,d.parent, 0 AS level, ARRAY[CAST...
                                                             ^
HINT:  Convertit la sortie du terme non récursif dans le bon type.

le champ nom est de type character varying(100)

11/10/2013, 14h47
estofilo

Quels sont les types de données de domaine.nom et domaine.id ?
11/10/2013, 14h57
chris0938

domaine.id : character(12)
domaine.nom : character varying(100)
11/10/2013, 15h53
estofilo

Il faudrait faire le CAST vers le type TEXT au lieu de VARCHAR.

D'ailleurs d'une manière générale TEXT est toujours préférable à VARCHAR sauf quand on veut impérativement limiter la taille de la donnée.