Obtenez tous les enfants des parents

Version imprimable

Bonjour,

J'utilise la version 11 de postgresql.

Je veux obtenir la liste des collèges dont le parentid est nul et pour chacun obtenir la liste de tous ses enfants avec toutes les informations (identifiant, nom et numéro préféré).

ce qui signifie par exemple si on a 3 collèges avec parentid null comme celui-ci

Code:

1
2
3
college1 :  (id : B1  , name : college1 , favoriteNbr: 1 )
 college2  : (id : B2  , name : college2 , favoriteNbr: 8 )
 college3 :  (id : B3 , name : college3 , favoriteNbr: 5 )

et si collège 1 a deux enfants et collège 2 a un enfant et collège 3 a trois enfants
le résultat final devrai être comme celle ci

Code:

1
2
3
B1,college1,1  -->  B01,college01,0 --> B001,college001,1
B2,college2,8  -->  B02,college02,3
B3,college3,5  -->  B03,college03,1 --> B003,college003,4 --> B0003,college0003,1

seulement 3 lignes (le college qui a le parent null c'est a dire niveau 1 avec tous ces enfants)

J'essaie avec cette requête sans succès

Code:

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
27
28
with recursive nodes as    (
 select college.id AS value, CAST(collegeL.name  AS varchar) AS text,cast (ARRAY[college.id,collegeL.name,
 cast((select count(*) from favorite fav where fav.id_college_favorite = college.id and fav.id_emp = 'EM-1') as varchar(10) )] as TEXT )
 as parents,college.app_order,
cast((select count(*) from favorite fav where fav.id_college_favorite = college.id and fav.id_emp = 'EM-1') as varchar(10) ) as favoriteNbr
 
 FROM public.college college  
 LEFT OUTER JOIN public.collegeLang collegeL ON collegeL.college_id = college.id
 Where college.status ='1' and parentid is null and college.categ ='C' 
 
 
 
 union all  
 
 select o.id AS value, CAST(collegeL.name  AS varchar)   AS text ,parents || '->' ||  cast (ARRAY[o.id,collegeL.name,
  cast((select count(*) from favorite fav where fav.id_college_favorite = o.id and fav.id_emp = 'EM-1') as varchar(10) )] as TEXT ),o.app_order  ,
     cast((select count(*) from favorite fav where fav.id_college_favorite = o.id and fav.id_emp = 'EM-1') as varchar(10) ) as favoriteNbr
 
 FROM college o  
  join nodes n on n.value = o.parentid  
  LEFT OUTER JOIN collegeLang collegeL ON collegeL.college_id = o.id 
  where o.status ='1' and o.categ !='M'
 
 
 
 )  
 select  CAST(text  AS varchar) as text,value as value, parents as children,favoriteNbr from nodes  
 order by app_order asc

cette requête affiche tous les collèges (9 collèges) et affiche pour chacun le chemin comme celui-ci

Code:

1
2
3
4
5
6
7
8
9
B1,college1,1                 B1,college1,1
B2,college2,8                 B2,college2,8
B3,college3,5                 B3,college3,5
B01,college01,0               B1,college1,1  -->  B01,college01,0
B02,college02,3               B2,college2,8  -->  B02,college02,3
B03,college03,1               B3,college3,5 -->   B03,college03,1
B001,college001,1             B1,college1,1 -->   B01,college01,0   -->   B001,college001,1
B003,college003,4             B3,college3,5  -->  B03,college03,1  -->   B003,college003,4
B0003,college0003,1           B3,college3,5  -->  B03,college03,1  -->   B003,college003,4   --> B0003,college0003,1

merci d'avance pour vos aides

07/01/2020, 09h40
JeanYvette
Bonjour bonjour,

Pour tout avoir sur la même ligne, regardez ce lien, qui pourrait être utile :
https://www.postgresqltutorial.com/p..._agg-function/

Il suffira ensuite (en vulgarisant bien sûr) une requête de ce genre :
Code:

1 2 3 4 SELECT string_agg(college.id || ',' || college.name {...} , ',') from {...} where {...}
Bisous bisous
07/01/2020, 12h35
francois255

merci pour votre réponse,
cette fonction concatenate une liste des strings.
je veux l'utiliser.
mais mon but est d'afficher seulement en revenant à mon exemple trois lignes
et parmi les colonnes je veux afficher une colonne qui contient la liste des enfants
c'est a dire trois lignes ( qui est lié au collège avec parentid null) et chaque ligne contient une une colonne qui contient la liste des enfants

bonjour,

Votre requete semble correcte, c'est juste qu'elle renvoie toutes les étapes de recursion, et que vous ne voulez que la dernière pour chaque "parent initial"

Vous pouvez identifier cette dernière étape par un ROW_NUMBERquelque chose comme (pas testé) :

Code:

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
27
28
29
30
31
32
33
34
35
36
 
with recursive nodes as    (
 select college.id AS value, CAST(collegeL.name  AS varchar) AS text,cast (ARRAY[college.id,collegeL.name,
 cast((select count(*) from favorite fav where fav.id_college_favorite = college.id and fav.id_emp = 'EM-1') as varchar(10) )] as TEXT )
 as parents,college.app_order,
cast((select count(*) from favorite fav where fav.id_college_favorite = college.id and fav.id_emp = 'EM-1') as varchar(10) ) as favoriteNbr
 , college.id AS ini
 , 1 AS niveau
 FROM public.college college  
 LEFT OUTER JOIN public.collegeLang collegeL ON collegeL.college_id = college.id
 Where college.status ='1' and parentid is null and college.categ ='C' 
 
 
 
 union all  
 
 select o.id AS value, CAST(collegeL.name  AS varchar)   AS text ,parents || '->' ||  cast (ARRAY[o.id,collegeL.name,
  cast((select count(*) from favorite fav where fav.id_college_favorite = o.id and fav.id_emp = 'EM-1') as varchar(10) )] as TEXT ),o.app_order  ,
     cast((select count(*) from favorite fav where fav.id_college_favorite = o.id and fav.id_emp = 'EM-1') as varchar(10) ) as favoriteNbr
	, n.ini
	, n.niveau + 1
 FROM college o  
  join nodes n on n.value = o.parentid  
  LEFT OUTER JOIN collegeLang collegeL ON collegeL.college_id = o.id 
  where o.status ='1' and o.categ !='M'
 
 
 
 )  ,
 ord AS (
	SELECT *, ROW_NUMBER() OVER(PARTITION BY ini ORDER BY Niveau DESC) AS RN
	FROM nodes
)
SELECT value 
FROM ord
WHERE RN = 1

salut,
merci pour votre réponse.

je pense qu'il y'a une erreur dans la requête en utilisant ROW_NUMBER puisque elle affiche c'est vrai 3 lignes mais ils ne correspond pas aux collèges qui ont parentid null

aussi une autre erreur qui est la plus importante

la colonne children affiche seulement le chemin de collège pour cette ligne

c'est à dire

le résultat qui correspond à :

Code:

1
2
3
 
select  CAST(text  AS varchar) as text,value as value, parents as children,favoriteNbr from nodes  
 order by app_order asc

C'est comme :

Code:

1
2
3
text          value         children                                                                     favoriteNbr 
 
college001    B001          { B1,college1,1} -->   {B01,college01,0}   -->  { B001,college001,1}         1

le résultat final que je veux avoir avec la requête doit être comme celle ci

Code:

1
2
3
4
5
text          value         children                                                                          favoriteNbr 
 
college1       B1          {B01,college01,0}   -->   {B001,college001,1}                                       1
college2       B2          ici tous les enfants pour B2 {.....}                                                8
college3       B3          ici tous les enfants pour B3 {.....}                                                5

09/01/2020, 14h20
aieeeuuuuu

si dans la requete finale vous reprenez la colonne value, alors forcément, elle contiendra le dernier enfant du chemin.

si vous voulez garder séparément le parent, alors il faut, dans la partie recursive, réutiliser la colonne value de nodes, et non reprendre la valeur du college enfant...
09/01/2020, 19h25
francois255

encore merci pour votre réponse,

mais pouvez vous m'aider à corriger ma requête.
franchement je n’arrive pas à comprendre comment appliquer vos remarques dans ma requête