How to write this in SQL?

Question

I've been trying to find this answer by myself, but i'm just too bad for that, so i come here hoping you can help me.

I have the following database tables :

user(
    int unsigned id,
    varchar      login
)
group(
    int unsigned id,
    varchar      label
)
right(
    int unsigned id,
    varchar      label
)
user_group(
    int unsigned userId,
    int unsigned groupId
)
user_right(
    int unsigned userId,
    int unsigned rightId,
    boolean      granted
)
group_right(
    int unsigned groupId,
    int unsigned rightId,
    boolean      granted
)

And 3 configuration parameters (PHP defines) :

GRANTED_BY_DEFAULT that can be TRUE or FALSE. It indicates, when a right is not specified for a group or a user (i.e not in the association table), if the right is granted or not.
RESULTING_RIGHTS that can be MIN_RIGHTS or MAX_RIGHTS. It indicates whether to use "AND" or "OR" operator when determining resulting rights. For exemple, if for right "r1", the group "g1" has TRUE and the groups "g2" and "g3" have FALSE, with "MIN_RIGHTS" you get FALSE, and with "MAX_RIGHTS" you get TRUE.
RIGHTS_PRIORITY that can be USER_OVER_GROUP or GROUP_OVER_USER. It indicates if the user's own rights override his groups's rights or if the groups's rights override the user's own rights.

I would like to come out with a single query that would find the rights a user effectivly has, considering these 3 parameters and his groups, but i really have no idea about how to do it. I could also make multiple queries and do a part of the work on PHP side, but i would like to avoid having to many queries as i want my app to be fast enough for things like AJAX auto completions and other stuff like that.

I'd understand if no one wants or has the time to help me :p Thanks for reading.

EDIT : Some sample datas :

user (id, login) :
1 "Admin"

group (id, label) :
1 "g1"
2 "g2"
3 "g3"

right (id, label) :
1 "r1"
2 "r2"
3 "r3"

user_group (userId, groupId) :
1 1
1 2

user_right (userId, rightId) :
1 1 true
1 2 false

group_rights (groupId, rightId) :
1 1 true
1 2 false
2 1 false
2 3 true

And the output would be a list of (right_id, right_label) rows depending on the parameters and the rows of other tables.

The user ID and / or login is considered as known when retrieving the rights.

Can you please post some sample data and the desired output? — Mahmoud Gamal, Nov 13 '12 at 13:26
Any particular reason it has to all be jammed into a single query? — Jeremy Harris, Nov 13 '12 at 13:30
Yes, i would like to have a short execution time. But if running 2 / 3 queries is not much slower, then it would be fine too. — Virus721, Nov 13 '12 at 13:32
sometimes running 2 or 3 simple queries is faster than 1 big complicated query ya know.... just saying. — itachi, Nov 13 '12 at 13:35
Even if i am using prepared queries in PDO ? I'll see what i can do with several more queries, thanks. — Virus721, Nov 13 '12 at 13:36
You might want to consider a user_rights_cache table that would contain the calculated rights for a user. Then have code that updates the cache whenever it is dirty (relative to the factors that impact it such as change to user_rights or group_rights table). Look-ups to the cache will be very quick for things like auto-complete. A more complicated route than a few queries in PHP, so make sure you think about where your performance/scale bottlenecks might be beforehand. It rights calculation is expensive, this is a viable option. — John, Nov 13 '12 at 15:46

score 2 · Accepted Answer · answered Nov 13 '12 at 16:21

DECLARE @USERID INT = 1,
        @GRANTED_BY_DEFAULT INT = 0,
        @RESULTING_RIGHTS VARCHAR(20) = 'MIN_RIGHTS',
        @RIGHTS_PRIORITY VARCHAR(20) = 'USER_OVER_GROUP'

SELECT 
    R.ID,
    R.NAME,
    CASE
        WHEN MAX(UR.Value) IS NULL AND MAX(GR.Value) IS NULL THEN
            @GRANTED_BY_DEFAULT
        WHEN MAX(UR.Value) IS NOT NULL AND MAX(GR.Value) IS NOT NULL THEN
            CASE
                WHEN @RIGHTS_PRIORITY = 'USER_OVER_GROUP' THEN
                    MAX(UR.Value)
                ELSE
                    CASE 
                        WHEN @RESULTING_RIGHTS = 'MIN_RIGHTS' THEN
                            MIN(GR.Value)
                        ELSE
                            MAX(GR.Value)
                    END
            END
        WHEN MAX(GR.Value) IS NULL THEN
            MAX(UR.Value)
        ELSE
            CASE
                WHEN @RESULTING_RIGHTS = 'MIN_RIGHTS' THEN
                    MIN(GR.Value)
                ELSE
                    MAX(GR.Value)
            END
    END VALUE
FROM
    [Right] R
    CROSS JOIN [User] U
    LEFT JOIN [User_Right] UR
        ON R.ID = UR.RightID
        AND UR.UserID = U.ID
    LEFT JOIN [User_Group] UG
        ON UG.UserID = U.ID
    LEFT JOIN [Group_Right] GR
        ON UG.GroupID = GR.GroupID 
        AND GR.RightID = R.ID
WHERE
    U.ID = @USERID
GROUP BY 
    R.ID,
    R.NAME

Here is a SQL fiddle example SQL Fiddle

Thanks a lot for taking the time to answer me :) I'll try this ! — Virus721, Nov 13 '12 at 16:46

How to write this in SQL?

1 Answers1