postgresql - How to show chain element by order

Question

I have goal to create query which return me item ids regarding position in chain. I have chain logic, each element has right and left fk and index. Chain can contains elements which can added like append and like prepend approach, regarding this id from table not help to build current chain dependencies.

This is db structure

create table public.chain_data
(
    id                    integer      not null
        constraint chain_data_pkey
            primary key,
    unique_identifiers_id integer      not null
        constraint fk_388447e52a0b191e
            references public.unique_identifiers
            on delete cascade,
    chain_data_name       varchar(255) not null,
    carriage              boolean default false,
    left_id               integer      not null
        constraint fk_388447e5e26cce02
            references public.chain_data,
    right_id              integer
        constraint fk_388447e554976835
            references public.chain_data
);

alter table public.chain_data
    owner to "universal-counter";

create index idx_388447e52a0b191e
    on public.chain_data (unique_identifiers_id);

create unique index left_right_uniq_idx
    on public.chain_data (right_id, left_id);

create unique index carriage_uniq_index
    on public.chain_data (unique_identifiers_id, carriage)
    where (carriage <> false);

and data example. this chain began from id = 10 and then was prepend new items(rows) in start of chain. Each element has left and right dependencies. So inserts:

INSERT INTO public.chain_data (id, unique_identifiers_id, chain_data_name, carriage, left_id, right_id) 
VALUES 
  (10, 8, 'dddd_2', true, 22, null),
  (22, 8, 'shuba', false, 23, 10),
  (24, 8, 'viktor', false, null, 23),
  (23, 8, 'ivan', false, 24, 22);

Regarding this query should to return ids like this

24, 23, 22, 10

because element with id = 24 present on start chain then by left and right dependencies obviously 23, 22 and 10 id= 10 is last element in chain

question from:https://stackoverflow.com/questions/66062117/how-to-show-chain-element-by-order

Answer 1

demo:db<>fiddle

You can use a recursive CTE for that:

WITH RECURSIVE chain AS (
    SELECT id, right_id                -- 1
    FROM chain_data
    WHERE left_id IS NULL
    
    UNION 
    
    SELECT cd.id, cd.right_id          -- 2
    FROM chain_data cd
    JOIN chain c ON c.right_id = cd.id
)
SELECT
    string_agg(id::text, ', ')         -- 3
FROM
    chain

1. Initial part of the recursion: The record with the NULL value
2. The recursion part: Join the current table on the previous step using the previous right_id as current id
3. Afterwards you can aggregate all fetched records with the string_agg() aggregation to return your string list.