Search code examples
phpmysqlsubqueryleft-joininner-join

MySQL query which has multiple subqueries, each with different join types

Let me start by giving you a diagram of the relationships between the tables used in my mysql query:

table relationship
(source: r717.net)

I have a query that looks like this:

SELECT  * 
FROM    `permissions`
WHERE   `id` IN (
    SELECT      pr.perm_id 
    FROM        `user_roles` as ur 
    LEFT JOIN   `permissions_role` as pr 
    ON          ur.role_id = pr.role_id
    WHERE       ur.user_id = '$userid'
)
OR      `id` IN (
    SELECT      `perm_id` 
    FROM        `permissions_user` 
    WHERE       `user_id` = '$userid'
)

$userid is the id from the user table for the current user. I store the permission name from the result into an array which represents all permissions assigned to the user based on his/her role and his/her id:

<?php
$user_perms = array();
if(mysql_num_rows($query) > 0):
    while($result = mysql_fetch_array($query):
        $user_perms[] = $result('name');
    endwhile;
endif;
?>

print_r($user_perms); produces an output that looks like:

Array ( 
    [0] => ACCESS_TELEPHONELIST_PAGE
    [1] => ACCESS_VACATIONSCHED_PAGE
    [2] => ACCESS_TOURSCHED_PAGE
    [3] => ACCESS_WORKSCHED_PAGE
    [4] => ACCESS_RESOURCES_LINKS
    [5] => ACCESS_PMTOOL_PAGE
    [6] => ACCESS_TOOLSTOOL_PAGE
    [7] => ACCESS_SHOPTOOLLIST_PAGE
    [8] => ACCESS_TOOLINVENTORY_PAGE
    [9] => ACCESS_MANAGETOOLLIST_PAGE
    [10] => ACCESS_TOOLREPORTS_PAGE
    [11] => ACCESS_JOBSLIST_LINKS
    [12] => MAIN_TAB_TOOLSTOOL
    [13] => ADMIN_TAB_PODMANAGEMENT
    [14] => TOOL_TAB_SHOPTOOLLIST
)

What I want to do is to also store all of the user's roles into another array without making a second query. I thought I needed to use aliases for the sub queries, so I tried this query:

SELECT      permissions.*, usersroles.role_id 
FROM        `permissions`
INNER JOIN  (
    SELECT      ur.user_id, pr.perm_id, ur.role_id
    FROM        `user_roles` as ur
    LEFT JOIN   `permissions_role` as pr 
    ON          ur.role_id = pr.role_id
    WHERE       ur.user_id = '$userid'
) AS usersroles ON usersroles.perm_id = permissions.id
INNER JOIN  (
    SELECT      `perm_id`, `user_id` 
    FROM        `permissions_user` 
    WHERE       `user_id` = '$userid'
) AS userperms ON userperms.user_id = usersroles.user_id
              AND userperms.perm_id = permissions.id

and, using code similar to the code above...

<?php
$user_perms = array();
$user_roles = array();
if(mysql_num_rows($query) > 0):
    while($result = mysql_fetch_array($query):
        $user_perms = $result('name');
        $user_roles = $result('role_id');
    endwhile;
endif;
?>

... I get this warning:

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given

But, I would like to print_r($user_roles); and produce an output that looks like this:

Array (
    [0] => administrator
    [1] => humanresources
    [2] => podmanager
)

Can anyone tell me what I am doing wrong, or suggest a better way to get the data I need from one query into 2 arrays?

EDIT : After careful consideration, I changed my code to use 2 queries as ImreL suggested. The resulting code works nicely and executes quickly. I have edited my answer to show the final code I used and added supporting code to demonstrate how I am using the 2 queries. Big thanks to ImreL!

Solution

  • The query will be required to run on every page the user loads and we have over 30,000 permissions, and 3,000 roles. I am just trying very hard to keep my number of queries to a minimum. We also host our 7 sites on a server in our office, and the server doesn't seem to be capable of handling the amount of traffic we generate (unfortunately I have no control over this)

    I see that your intentions are good, but let me tell you this:

    The "number of queries" is not the right way to measure your site's performance.

    Many times 2 simple queries use less resources than 1 complex.

    Also there are other ways to speed up your sites:

    • Assess if you really need all those roles and permissions loaded on each request. Maybe querying for only the role/permission needed is enough.
    • Have proper indexes
    • Utilize caching techniques to reduce the load (site content)

    So finally, I tried to come up with query to satisfy what was asked: 

    select * from (
    select ur.role_id, p.*
    from user_roles ur 
    left join permissions_role pr on ur.role_id =  pr.role_id
    left join permissions p on p.id = pr.perm_id
    where ur.user_id = '$userid'
  union all
    select null as role_id, p.*
    from permissions_user pu
    join permissions p on p.id = pu.perm_id
    where pu.user_id = '$userid'
) sub
group by ifnull(name,role_id) -- group by to eliminate duplicates

    But this is not good on performance. You are much better off with 2 queries: 1st to get all permissions for user

    select p.* from permissions p
join permissions_role pr on pr.perm_id = p.id
join user_roles ur on ur.role_id =  pr.role_id and ur.user_id = '$userid'
union
select p.* from permissions p
join  permissions_user pu on pu.perm_id = p.id and pu.user_id = '$userid';

    and 2nd to get all roles.