Postgresql select column with minimum value of hierarchical parents

Having a table like this

                       Table "public.access_level"
  Column   |  Type   | Modifiers | Storage  | Stats target | Description
-----------+---------+-----------+----------+--------------+-------------
 uid       | uuid    | not null  | plain    |              |
 parent_id | integer | not null  | plain    |              |
 child_id  | integer | not null  | plain    |              |
 level     | integer | not null  | plain    |              |
 entity    | text    |           | extended |              |

and rows like this (uid column eliminated)

 parent_id | child_id | level | entity
-----------+----------+-------+--------
        11 |       22 |     4 | a
        22 |       33 |     5 | a
        33 |       44 |     6 | a
        11 |       22 |     7 | b
        22 |       33 |     4 | b
        33 |       44 |     5 | b

I would like an output which returns level value for each row based on minimum value of level of parents distinctive to each entity. Here is my desired output:

 parent_id | child_id | level | entity
-----------+----------+-------+--------
        11 |       22 |     4 | a
        22 |       33 |     4 | a
        33 |       44 |     4 | a
        11 |       22 |     7 | b
        22 |       33 |     4 | b
        33 |       44 |     4 | b

A recursive approach is desirable as hierarchies depth is not fixed.

Note:(parent_id,child_id,entity) is unique in the table

Actually parent_id and child_id are users. a parent gives a child a level of access for an entity. Then the child user can give a level of access to another child of its own. At some point, the parent of a parent may change the access level if its child. now all deeper children have to have access level not more than that. It could not be implemented using a trigger to update the corresponding rows because the parent of parent may rollback changes.

A Scenario:

11,22,7,b means user-11 gives user-22 the level of 7 to b entity.
Now user-22 gives the user-33 level of 5 to b entity at some point
then user-33 give user-44 the level of 5 to b entity.
Important: user-22 changes access level of b to 4 for the user-33 which is what you see in example table
access level of user-33 to user-44 for b entity should remain 5 in the table

But I want the a query which will return 4 for that column as if I do it recursively for all children of user-22 which got the level more than 4.

thanks

Answer

The initial part of the recursive query finds roots (nodes without parents), in the recursive part we simply choose a less level for an entity:

with recursive cte as (
    select parent_id, child_id, level, entity
    from access_level t
    where not exists (
        select from access_level l
        where l.child_id = t.parent_id)
union all
    select t.parent_id, t.child_id, least(c.level, t.level), t.entity
    from cte c
    join access_level t
    on t.parent_id = c.child_id and t.entity = c.entity
)
select *
from cte
order by entity, parent_id

Db<>fiddle.

Advertisement

Answer