select row if NOT EXISTS condition after joining tables

Question

DBMS: PostgreSql, but MySql is acceptable as well

I have 4 tables,

permissions

id	slug	allowed
1	create	true
2	edit	true
3	delete	true
4	edit	false

roles

id	slug
1	Admin
2	Manger
3	User

model_permissions

model_type	model_id	permission_id
users	1	4
roles	1	1
roles	1	2
roles	1	3

model_roles

model_type	model_id	role_id
users	1	1
users	2	1
users	3	2
users	3	3

in the first step I want to get all permissions for user (let say id=1), either 'direct' assigned or through the role, this is what I come up, it works and returns correct result

select p.*
from permissions p
         join model_permissions mp on mp.permission_id = p.id
         join model_roles mr on mr.model_type = 'users' and mr.model_id = 1
where
    (
       (mp.model_type = 'users' and mp.model_id = 1)
        or
       (mr.role_id = mp.model_id and mp.model_type = 'roles')
    )

group by p.id;

my next step is to check if user has certain permission or not BUT if there is a row with same slug and allowed=false I want to get empty result.

example: user with id=1 has edit permission (allowed=true) through role, also has direct edit permission (allowed=false) id=4 (model_permissions table),

So when I'm trying to check if user has edit permission, in this particular case I want to get empty(false) result, what I have tried

select p.*
from permissions p
         join model_permissions mp on mp.permission_id = p.id
         join model_roles mr on mr.model_type = 'users' and mr.model_id = 1
where
     (
        (mp.model_type = 'users' and mp.model_id = 1)
        or
        (mr.role_id = mp.model_id and mp.model_type = 'roles')
     )
  and
    not exists(select *
                 from permissions p2
                 where p2.allowed=false
                 and p2.slug=p.slug
                 )
  and
    p.slug = 'edit'

group by p.id;

it works as expected BUT if I delete row from model_permissions |users | 1 | 4 | it still returns empty, I want to get result because this user has 'edit' permission throug role

I've tried sql queries above, also when I use p2.id=p.id inside not exists statment it always returnes result, the one with allowed=true value: | 2 | edit | true |

Any solution without changing db architecture

UPDATE

after several hours of thinking I found this query, which does what I was looking for, but still not sure why)

select p.*
from permissions p
         join model_permissions mp on mp.permission_id = p.id
where
    mp.model_type = 'roles' and mp.model_id = 1
  and
    not exists(select p2.*
                 from permissions p2
                 join model_permissions mp2 on mp2.permission_id = p2.id
                 where p2.allowed=false
                 and p2.slug=p.slug
                 )
  and
    p.slug = 'edit'
group by p.id;

Answer 1

The revised should meet your needs

SELECT p.*
FROM permissions p
JOIN model_permissions mp ON mp.permission_id = p.id
LEFT JOIN model_permissions mp_direct ON mp_direct.permission_id = p.id AND mp_direct.model_type = 'users' AND mp_direct.model_id = 1
JOIN model_roles mr ON mr.model_type = 'users' AND mr.model_id = 1
WHERE
    -- Either direct user permission or through roles
    (
       (mp.model_type = 'users' AND mp.model_id = 1)
        OR
       (mr.role_id = mp.model_id AND mp.model_type = 'roles')
    )
    -- Ensure we're checking for the specific permission
    AND p.slug = 'edit'
GROUP BY p.id
HAVING
    -- Ensure there's no direct denial overriding the permission
    MAX(CASE WHEN mp_direct.allowed IS NOT NULL THEN mp_direct.allowed ELSE TRUE END)

Answer 2

The problem basically comes down to the fact that model_permissions is a combiantion of two completely different tables. it's a denormalized design: you cannot have a conditional foreign key on different tables. (The same goes for model_roles, although what other models there are is not clear.)

This should have instead been two separate tables user_permissions and role_permissions. Then you would just use two separate exists clauses.

Finally, to get the results per slug, you need to group by slug, then use a having to filter out if you have allowed=false anywhere in the group.

select p.slug
from permissions p
where (
  exists (select 1
      from model_permissions mp
      where mp.model_type = 'users'
        and mp.model_id = 1
        and mp.permission_id = p.id
  )
  or exists (select 1
      from model_roles mr
      join model_permissions mp on mp.permission_id = p.id
      where mr.model_type = 'users'
        and mr.model_id = 1
        and mr.role_id = mp.model_id
        and mp.model_type = 'roles'
  )
)
group by
  p.slug
having count(*) filter (where not p.allowed) = 0;

db<>fiddle