PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – select row if NOT EXISTS condition after joining tables

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

Tags:

2

Answers


  1. 0

    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)
    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search