SQL query help needed: How to exclude or prioritize rows in SQL in the same table

Question

PROBLEM I HAVE

I have an application with Users, Roles, Clients and Systems. I have a table named 'policies' that contains the 22 policies/rules my application relies on. Each one of the 22 can be defined at one or more levels. The levels can be: System, Client, Role or User. A system has N clients, a client has N roles and a role has N users. By default, all 22 policies are defined at the System level, but they can be OVERWRITTEN at any lower level.

See the table below as an example. There are 22 policies defined for the system (system_id). Notice how the policy_1 is also set at the client, role and user level (with the corresponding id's).

            name             |  value   |                level                 
-----------------------------+----------+--------------------------------------
policy_1                     |    1     |                 system_id
policy_2                     |    4     |                 system_id
policy_3                     |    6     |                 system_id

[policies 4 to 21]           |   ...    |                    ...

policy_22                    |    9     |                 role_id
policy_1                     |    2     |                 client_id
policy_1                     |    9     |                 role_id
policy_1                     |    7     |                 user_id
(22 rows)

Even though the policy_1 is set at the System level, when setting it at the Client level, that's the policy's value that matters. Same happening again when setting it at Role level, and again at User level.

QUERY INPUT

• system_id, client_id, role_id, user_id

QUERY OUTPUT The 22 policies set at the LOWEST level. That is, the policies should be prioritized on the lowest level they are set.

THINGS I'VE TRIED

• A combination of LEFTJOINs with NOT INTO clauses

Answer 1

Thanks to the answer from Gordon Linoff, I could find a good solution following his strategy (distinct on + order by + case). This may not be the most elegant or efficient but it works. Let me know if it can be improved further.

select distinct on (name) *
from policies
where level = $system_id  or
      level = $client_id or
      level = $role_id  or
      level = $user_id
order by name,
         (case when level = $system_id then 1 when level = $client_id then 2 when level = $role_id then 3 when level = $user_id then 4 end) desc;

Answer 2

Hmmmm . . . If I understand correctly you can use distinct on. The key is filtering and ordering:

select distinct on (name) p.*
from policies p
where level = 'system_id' and value = $system_id or
      level = 'client_id' and value = $client_id or
      level = 'role_id' and value = $role_id or
      level = 'user_id' and value = $user_id
order by p.name,
         (case when level when 'system_id' then 1 when 'client_id' then 2 when 'role_id' then 3 when 'user_id' then 4 end) desc