Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Requêtes PostgreSQL Discussion :

Obtenez tous les enfants des parents


Sujet :

Requêtes PostgreSQL

  1. #1
    Membre à l'essai
    Obtenez tous les enfants des parents
    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 :Sélectionner tout -Visualiser dans une fenêtre à part
    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 :Sélectionner tout -Visualiser dans une fenêtre à part
    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 :Sélectionner tout -Visualiser dans une fenêtre à part
    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 :Sélectionner tout -Visualiser dans une fenêtre à part
    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

  2. #2
    Membre averti
    Bonjour bonjour,

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


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

    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
     
    SELECT string_agg(college.id || ',' || college.name {...} , ',') 
    from {...}
    where {...}


    Bisous bisous

  3. #3
    Membre à l'essai
    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

  4. #4
    Modérateur

    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_NUMBER

    quelque chose comme (pas testé) :

    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    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

  5. #5
    Membre à l'essai
    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 :Sélectionner tout -Visualiser dans une fenêtre à part
    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 :Sélectionner tout -Visualiser dans une fenêtre à part
    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 :Sélectionner tout -Visualiser dans une fenêtre à part
    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

  6. #6
    Modérateur

    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...

  7. #7
    Membre à l'essai
    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

###raw>template_hook.ano_emploi###