J'ai le tableau suivant

CREATE TABLE descriptor_value (
    id bigint NOT NULL,
    value varchar(250),
    parent_id bigint
) ;

La colonne id est la clé primaire.

Et j'ai trois rangées

id | value | parent_id
----------------------
1  | foo   | null
2  | bar   | 1
3  | baz   | 2

Et je dois créer une vue qui montrera le chemin complet du nœud every dans l'ordre inverse. Par exemple, si nous considérons uniquement baz, il doit y avoir la sortie suivante:

child_id | parent_id | level
----------------------------
 3       | 3         | 1
 3       | 2         | 2    
 3       | 1         | 3

Quelqu'un pourrait-il dire comment le faire dans PgSQL 12?

1
Shtirlits 18 avril 2020 à 21:45

2 réponses

Meilleure réponse

Je pense que vous voulez parcourir l'arbre des feuilles au (x) nœud (s). Ce serait:

with recursive cte as (
    select id child_id, id parent_id, 1 lvl, parent_id real_parent_id
    from descriptor_value dv
    where not exists (select 1 from descriptor_value dv1 where dv1.parent_id = dv.id)
    union all
    select c.child_id, dv.id parent_id, lvl + 1, dv.parent_id
    from cte c
    inner join descriptor_value dv on dv.id = c.real_parent_id
)
select child_id, parent_id, lvl from cte order by lvl

L'ancre de la requête récursive commence à partir de lignes qui ne sont parents d'aucun autre nœud. Puis nous grimpons dans l'arbre en incrémentant lvl à chaque pas, jusqu'à atteindre la racine.

Démo sur DB Fiddle :

child_id | parent_id | lvl
-------: | --------: | --:
       3 |         3 |   1
       3 |         2 |   2
       3 |         1 |   3

Si vous voulez partir de n'importe quel nœud, alors c'est encore plus simple: il suffit de changer la définition de l'ancre:

with recursive cte as (
    select id child_id, id parent_id, 1 lvl, parent_id real_parent_id
    from descriptor_value dv
    union all
    select c.child_id, dv.id parent_id, lvl + 1, dv.parent_id
    from cte c
    inner join descriptor_value dv on dv.id = c.real_parent_id
)
select child_id, parent_id, lvl from cte order by child_id, lvl

Rendements :

child_id | parent_id | lvl
-------: | --------: | --:
       1 |         1 |   1
       2 |         2 |   1
       2 |         1 |   2
       3 |         3 |   1
       3 |         2 |   2
       3 |         1 |   3
1
GMB 18 avril 2020 à 21:02

Utilisez un CTE récursif:

with recursive cte as (
      select dv.id, dv.value, dv.parent_id, dv.parent_id as root, 1 as lev,
             dv.id::text as path
      from descriptor_value dv
      where dv.parent_id is null
      union all
      select dv.id, dv.value, dv.parent_id, cte.root, lev + 1,
             cte.path || '-->' || (dv.id::text)
      from cte join
           descriptor_value dv
           on dv.parent_id = cte.id
    )
select cte.id, cte.value, cte.parent_id, cte.root, lev,
       max(lev) over (partition by cte.root) - lev + 1 as level
from cte
order by root, lev;

Voici un violon db <>.

0
Gordon Linoff 18 avril 2020 à 18:54