CODEWITHSUNDEEP
mysqlsql
Ja22
Ja22

Reputation: 1722

sql permission grouping

i am trying to get allowed permissions for a user for given modules, by user_id and department_id

per_type = 1/user permissions; 2/department permissions.

priority is given to per_type 1. if it is not set per_type 2. should group by mod_id.

if per_type 1 ==> 'id' is for user_id ex- [72 user id] [5 department id ] if per_type 2 ==> 'id' is for department

db table:

enter image description here

here is my current code:

SELECT tp.*,pm.mod_name FROM tbl_permissions tp 
                INNER JOIN (SELECT mod_id, min(per_type) AS min_per_type 
                FROM tbl_permissions GROUP BY mod_id) groupedtt ON tp.mod_id = groupedtt.mod_id 
        inner JOIN perm_modules pm ON pm.mod_id = tp.mod_id         
AND tp.per_type = groupedtt.min_per_type 
                AND ((per_type=1 AND id=72) OR (per_type=2 AND id=5))

output for user 72:

enter image description here

my problem is that for user 72 / mod_id 2 results do not show

Upvotes: 0

Views: 48

Answers (1)

Shadow
Shadow

Reputation: 34231

The issue is with the subquery:

SELECT mod_id, min(per_type) AS min_per_type 
            FROM tbl_permissions GROUP BY mod_id

The problem is that this subquery determines the minimum per_type independently from user, so for mod_id=2, the subquery returns 1 as a minimum because of row 7. But user 72 does not have access to this mod through its own user id.

Change the subquery as follows:

SELECT mod_id, min(per_type) AS min_per_type 
            FROM tbl_permissions
            WHERE ((per_type=1 AND id=72) OR (per_type=2 AND id=5))
            GROUP BY mod_id

Upvotes: 1

Related Questions