Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version


PostgreSQL 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit



SELECT 1


CREATE TABLE assets (
  id serial,
  name text,
  owner text,
  PRIMARY KEY(id)
);

CREATE TABLE users (
  id serial,
  username text,
  ref1 text,
  ref2 text,
  PRIMARY KEY(id)
);

INSERT INTO assets (name, owner) VALUES
  ('#1', 'a'),
  ('#2', 'b'),
  ('#3', 'c'),
  ('#4', 'a'),
  ('#5', 'c'),
  ('#6', 'd'),
  ('#7', 'e'),
  ('#8', 'd'),
  ('#9', 'a'),
  ('#10', 'a'),
  ('#11', 'z')
;

INSERT INTO users (username, ref1, ref2) VALUES
  ('bobo', 'a',	'd'),
  ('toto', 'b',	'e'),
  ('momo', 'c',	'd'),
  ('lolo', 'a',	'f'),
  ('popo', 'c', 'f');


-- For result comparison
CREATE TABLE results (
  ids int[],
  usernames text[],
  refs1 text[],
  refs2 text[],
  asset_ids int[],
  asset_count int
);

INSERT INTO results (ids, usernames, refs1, refs2, asset_ids, asset_count) VALUES
  (array[1,3,4,5], array['bobo', 'momo', 'lolo', 'popo'], array['a', 'c'], array['d', 'f'], array[1,3,4,5,6,8,9,10], 8),
  (array[2], array['toto'], array['b'], array['e'], array[2,7], 2),
  (null, null, null, array['z'], array[11], 1);

CREATE TABLE



CREATE TABLE



INSERT 0 11



INSERT 0 5



CREATE TABLE



INSERT 0 3


with recursive 
    nodes as (
        select u.id, r.ref
        from users u 
        cross join lateral ( values (u.ref1), (u.ref2) ) r(ref)
    ),
    edges as (
        select distinct n1.id as id1, n2.id as id2
        from nodes n1 
        inner join nodes n2 on n1.ref = n2.ref
    ),
    rcte as (
        select id1, id2, array[id1] as visited from edges where id1 = id2
        union all
        select r.id1, e.id2, r.visited || e.id2
        from rcte r
        inner join edges e on e.id1 = r.id2
        where e.id2 <> all(r.visited) 
    ),
    groups as (
        select id1 as id, array_agg(distinct id2 order by id2) as ids
        from rcte
        group by id1
    )
select * from groups order by id


id
ids


1
{1,3,4,5}

2
{2}

3
{1,3,4,5}

4
{1,3,4,5}

5
{1,3,4,5}



SELECT 5


with recursive 
    nodes as (
        select u.id, r.ref
        from users u 
        cross join lateral ( values (u.ref1), (u.ref2) ) r(ref)
    ),
    edges as (
        select distinct n1.id as id1, n2.id as id2
        from nodes n1 
        inner join nodes n2 on n1.ref = n2.ref
    ),
    rcte as (
        select id1, id2, array[id1] as visited from edges where id1 = id2
        union all
        select r.id1, e.id2, r.visited || e.id2
        from rcte r
        inner join edges e on e.id1 = r.id2
        where e.id2 <> all(r.visited) 
    ),
    groups as (
        select id1 as id, array_agg(distinct id2 order by id2) as ids
        from rcte
        group by id1
    )
select g.ids,
    array_agg(distinct u.username) as usernames,
    array_agg(distinct u.ref1) as refs1,
    case when g.ids is null then array_agg(distinct a.owner) else array_agg(distinct u.ref2) end as refs2,
    array_agg(distinct a.id) as asset_ids,
    count(distinct a.id) as asset_count
from assets a
left join users u on a.owner in (u.ref1, u.ref2)
left join groups g on g.id = u.id
group by g.ids


ids
usernames
refs1
refs2
asset_ids
asset_count


{1,3,4,5}
{bobo,lolo,momo,popo}
{a,c}
{d,f}
{1,3,4,5,6,8,9,10}
8

{2}
{toto}
{b}
{e}
{2,7}
2

null
{NULL}
{NULL}
{z}
{11}
1



SELECT 3
ids	usernames	refs1	refs2	asset_ids	asset_count
{1,3,4,5}	{bobo,lolo,momo,popo}	{a,c}	{d,f}	{1,3,4,5,6,8,9,10}	8
{2}	{toto}	{b}	{e}	{2,7}	2
null	{NULL}	{NULL}	{z}	{11}	1