Trouble with join query

Question

Hello overflowers ;)

I'm implementing home brew ACL system in web app and it gives me hard time. I believe this is trivial, just can't get my head around it.

I have 3 tables in one to many relationship:
resource, perms_group, perms_users where perms_group is a key table while perms_users allowes me to fine tune access per user basis if required (it adds up to perms_users or just simply overrides it, both tables store permissions as boolean values in separate columns).

I'm trying to get stuff from resource as well as get permissions in single query:

SELECT *
FROM resource
LEFT OUTER JOIN perms_groups ON resource.id = perms_group.res_id
LEFT OUTER JOIN perms_users ON resource.id = perms_users.res_id
WHERE resource.id = 1
    AND perms_group.group_id = 2
    AND perms_users.user_id = 3

Now trouble is that fine permission for user will be set rarely and above query will return 0 rows in such case. What I'm trying to tell is get me back perms for group 2 and for user 3 if any are set. Basically I'm missing AND_IF_EXISTS operator ;). I could break it in two queries easily but since it will run with every page refresh I need it as lean as possible. Currently I'm using MySQL but would like to switch to PostgreSQL later on so ideal solution would be db independent. Any ideas?

Thanks

Answer 1

SELECT *
FROM resource
LEFT OUTER JOIN perms_groups ON resource.id = perms_group.res_id 
                             AND perms_group.group_id = 2
LEFT OUTER JOIN perms_users ON resource.id = perms_users.res_id 
                            AND perms_users.user_id = 3
WHERE resource.id = 1

In fact, left outer join is useless if you write a condition on the table when you don't know if you will have data.

Answer 2

You can use CASE or COALESCE to implement what you need, if you join (left outer joins) your permissions into one row.

Answer 3

you could try moving the WHERE clause into the join for the users & groups...

SELECT *
FROM   resource
       LEFT OUTER JOIN perms_groups ON resource.id = perms_group.res_id AND perms_group.group_id = 2
       LEFT OUTER JOIN perms_users ON resource.id = perms_users.res_id AND perms_users.user_id = 3
WHERE  resource.id = 1

Answer 4

If your user permissions override the group permissions, use this:

SELECT  permission
FROM    resource
JOIN    perms_users
ON      perms_users.res_id = resource.id
WHERE   resource.id = 1
        AND user_id = 3
UNION ALL
SELECT  permission
FROM    resource
LEFT JOIN
        perms_groups
ON      perms_group.res_id = resource.id
        AND group_id = 2
WHERE   resource.id = 1
LIMIT 1

This will be more efficient, as this query will not even look into perms_groups if it finds a record in perms_users.

Answer 5

For better understanding of outer joins read this:

Ask the Experts: Terry Purcell on Outer Joins