MySQL: GROUP BY with custom hierarchical functionality

Question

I've got a permission/privileges - table looking like this:

+----+----------+----------+------+-------+
| id | name     | usertype | read | write |
+----+----------+----------+------+-------+
|  1 | test     | A        |    0 |     0 |
|  2 | test     | MU       |    1 |     1 |
|  3 | test     | U        |    1 |     1 |
|  4 | apple    | A        |    1 |     1 |
|  5 | apple    | MU       |    1 |     0 |
|  6 | apple    | U        |    0 |     0 |
|  7 | flower   | A        |    0 |     0 |
|  8 | flower   | MU       |    0 |     0 |
|  9 | flower   | U        |    1 |     1 |
+----+----------+----------+------+-------+

there are 3 usertypes: A (admin), MU (maintenance user), U (standard user)
the usertypes are hierarchical: A > MU > U

(the usertypes are saved as CHAR(2) in the database, and unfortunately I can't change that)

now i want to build a query which implements the hierarchical logic of my usertypes.

e.g. usertype 'A' got no permission to read or write on stuff with the name 'test', thus usertypes 'MU' AND 'U' also should have no permission for that and their read = 1 and write = 1 should be ignored.

I know which usertype is currently logged in. I somehow have to check for the minimum of read/write rights to the name for all hierarchical predecessors, i guess. but i don't know how to check that since usertype is not a number field.

this is what I've tried so far:

SELECT 
    name,
    MIN(read),
    MIN(write),
    CASE
        WHEN usertype = 'A' THEN 0
        ELSE (CASE
            WHEN usertype = 'WU' THEN 1
            ELSE 2
        END)
    END userval
FROM
    permissions
-- WHERE usertype <= :current_usertype 
GROUP BY name

this seems to work, but i don't know how i can get my condition WHERE usertype <= :current_usertype working, so a usertype down in the hierarchy can't get more privileges on a name than a "higher" usertype.

any ideas?

thanks in advance!

Answer 1

This is how I solved my problem:

1. I added another table "permission_groups" to the database:

+----+----------+--------+
| id | usertype |  value |
+----+----------+--------+
|  1 | A        |    100 |
|  2 | MU       |     20 |
|  3 | U        |     10 |
+----+----------+--------+

2. Then I joined this table to my original table "permissions" which i showed in my question:
here i get the value of my "permission_groups" table with a subquery. this value symbolizes the hierarchical order of my different usertypes.

SELECT 
    perm.name,
    MIN(perm.`read`),
    MIN(perm.`write`),
    group .value
FROM
    permissions perm
LEFT JOIN permission_groups group ON group.usertype = perm.usertype 
WHERE
   group.value >= (SELECT value from permission_groups WHERE usertype = :current_usertype)
GROUP BY perm.name

:current_usertype is a PDO parameter in my case, which is replaced by the usertype of the current user.