How can I find recursively the masks that a resource have in their parents?-CodePudding

I have two tables:

resources

-- resources.resource definition

-- Drop table

-- DROP TABLE resources.resource;

CREATE TABLE resources.resource (
    id uuid NOT NULL,
    children jsonb NULL,
    ctx varchar(255) NULL,
    parentclass varchar(255) NULL,
    parentid uuid NULL,
    resourceclass varchar(255) NULL,
    resourcetype varchar(255) NULL,
    status varchar(50) NULL,
    CONSTRAINT resource_pkey PRIMARY KEY (id)
);

and userresourcemask

-- resources.userresourcemask definition

-- Drop table

-- DROP TABLE resources.userresourcemask;

CREATE TABLE resources.userresourcemask (
    id int8 NOT NULL,
    mask varchar(50) NULL,
    username varchar(255) NULL,
    resource_id uuid NULL,
    CONSTRAINT userresourcemask_pkey PRIMARY KEY (id)
);


-- resources.userresourcemask foreign keys

ALTER TABLE resources.userresourcemask ADD CONSTRAINT fk5vjgr74nhjgovctjtt2qifsu7 FOREIGN KEY (resource_id) REFERENCES resources.resource(id);

How do I recursively find all the parent masks for a given resource?

For example, with:

with recursive res as (
    select r1.id, r1.parentid from resources.resource r1 
    where r1.id = '026a1f8c-a2c5-4505-be12-3d5798d754bb'
    union all
    select r2.id, r2.parentid from resources.resource r2 inner join res rs on rs.parentid = r2.id
) select res.*from res

I can find all parents.

And with:

with recursive res as (
    select r1.id, r1.parentid from resources.resource r1 
    where r1.id = '026a1f8c-a2c5-4505-be12-3d5798d754bb'
    union all
    select r2.id, r2.parentid from resources.resource r2 inner join res rs on rs.parentid = r2.id
) select res.*, u.mask from res
inner join resources.userresourcemask u 
        on u.resource_id = res.id

I can find the resources with a mask.

Assuming that a resource have a parent with mask MASK_PARENT and a grandparent with a mask MASK_GRANDPARENT

how can I obtain

id         | mask
resourceID | MASK_PARENT, MASK_GRANDPARENT

for all resources???

EDIT:

HERE the sample data

where I expect as results

id                                    | parentid                             | mask
026a1f8c-a2c5-4505-be12-3d5798d754bb  | dcac77e0-e7a1-4db6-9e6c-bde883809711 | ADMIN, EDITOR, VIEWER
...
3ed8c0a4-93a6-466f-b120-36985e153afa  | 262ce8a9-fbf8-4333-8738-797e167a06a6 | ADMIN, VIEWER
...

CodePudding user response：

My approach would be to join to the masks and then compute paths using text[] down from the root:

with recursive join_masks as (
  select r.id, r.parentid, m.mask
    from resources.resource r
         left join resources.userresourcemask m on m.resource_id = r.id
), walk as (
  select id, parentid, array_agg(mask) as mask
    from join_masks
   where parentid is null
   group by id, parentid
  union all
  select c.id, c.parentid, 
         case 
           when c.mask is null then p.mask
           when array[c.mask] <@ p.mask then p.mask
           when p.mask = '{null}' and c.mask is not null then array[c.mask]
           else p.mask || c.mask
         end as mask
    from walk p
         join join_masks c on c.parentid = p.id
)
select * from walk;

This gives:

 id                                   | parentid                             | mask                 
 :----------------------------------- | :----------------------------------- | :--------------------
 53d0dd59-903f-4599-b6d0-3940ec3fd8c1 | null                                 | {VIEWER,ADMIN}       
 ce1d4807-f4c3-46dd-b1c5-04ab91004324 | 53d0dd59-903f-4599-b6d0-3940ec3fd8c1 | {VIEWER,ADMIN}       
 2c72c007-9d10-4218-8f80-bc9a835d6ad5 | ce1d4807-f4c3-46dd-b1c5-04ab91004324 | {VIEWER,ADMIN}       
 68d4ee65-fbb0-462d-8adf-7b295ba442a1 | 2c72c007-9d10-4218-8f80-bc9a835d6ad5 | {VIEWER,ADMIN}       
 b4d1ff43-998a-4433-b8c3-4649986aaa14 | 68d4ee65-fbb0-462d-8adf-7b295ba442a1 | {VIEWER,ADMIN}       
 59948041-0c68-453d-a93c-94dd349d627a | b4d1ff43-998a-4433-b8c3-4649986aaa14 | {VIEWER,ADMIN}       
 3db044dc-724f-46df-bc5e-41d29069dbb7 | 59948041-0c68-453d-a93c-94dd349d627a | {VIEWER,ADMIN}       
 262ce8a9-fbf8-4333-8738-797e167a06a6 | 3db044dc-724f-46df-bc5e-41d29069dbb7 | {VIEWER,ADMIN}       
 3ed8c0a4-93a6-466f-b120-36985e153afa | 262ce8a9-fbf8-4333-8738-797e167a06a6 | {VIEWER,ADMIN}       
 56f26cec-37eb-4d51-8714-58864d60a794 | 3ed8c0a4-93a6-466f-b120-36985e153afa | {VIEWER,ADMIN,EDITOR}
 e7bf49e4-ff6e-4869-998e-412b0b6e8a55 | 56f26cec-37eb-4d51-8714-58864d60a794 | {VIEWER,ADMIN,EDITOR}
 75ac78a2-579e-43c5-a202-7ea6dae6d026 | e7bf49e4-ff6e-4869-998e-412b0b6e8a55 | {VIEWER,ADMIN,EDITOR}
 1e2206dc-17b1-463d-92e8-931e3ce9fec8 | 75ac78a2-579e-43c5-a202-7ea6dae6d026 | {VIEWER,ADMIN,EDITOR}
 1b6d5d35-f266-42a0-896c-40f36a1ed1c8 | 1e2206dc-17b1-463d-92e8-931e3ce9fec8 | {VIEWER,ADMIN,EDITOR}
 892cd856-d854-4804-b856-3cf25611c93c | 1b6d5d35-f266-42a0-896c-40f36a1ed1c8 | {VIEWER,ADMIN,EDITOR}
 8c1a935f-97ca-46cf-9362-24215e588b32 | 892cd856-d854-4804-b856-3cf25611c93c | {VIEWER,ADMIN,EDITOR}
 51d99087-c0e6-4d5b-9c2e-7e3a820e2fff | 8c1a935f-97ca-46cf-9362-24215e588b32 | {VIEWER,ADMIN,EDITOR}
 364cdc26-dc53-41d1-9b76-a9bd5562e8be | 51d99087-c0e6-4d5b-9c2e-7e3a820e2fff | {VIEWER,ADMIN,EDITOR}
 03562659-8ebb-41bf-8f57-c26760eda274 | 364cdc26-dc53-41d1-9b76-a9bd5562e8be | {VIEWER,ADMIN,EDITOR}
 399e51eb-ae49-47e1-8ea4-6195213329be | 03562659-8ebb-41bf-8f57-c26760eda274 | {VIEWER,ADMIN,EDITOR}
 010348c8-cc0e-48dd-aa06-fb2bcd91bc16 | 399e51eb-ae49-47e1-8ea4-6195213329be | {VIEWER,ADMIN,EDITOR}
 dcac77e0-e7a1-4db6-9e6c-bde883809711 | 010348c8-cc0e-48dd-aa06-fb2bcd91bc16 | {VIEWER,ADMIN,EDITOR}
 026a1f8c-a2c5-4505-be12-3d5798d754bb | dcac77e0-e7a1-4db6-9e6c-bde883809711 | {VIEWER,ADMIN,EDITOR}

db<>fiddle here