php - 具有多个子查询的 MySQL 查询，每个子查询具有不同的连接类型

Question

首先让我给你一张我的 mysql 查询中使用的表之间关系的图表:

_{(来源:r717.net)}

我有一个如下所示的查询:

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 是当前用户的用户表中的 ID。我将结果中的权限名称存储到一个数组中，该数组表示根据他/她的角色和他/她的 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); 产生如下所示的输出:

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
)

我想做的是还将所有用户的角色存储到另一个数组中，而不进行第二次查询。我想我需要为子查询使用别名，所以我尝试了这个查询:

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

并且，使用类似于上述代码的代码...

<?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;
?>

...我收到此警告:

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

但是，我想 print_r($user_roles); 并生成如下所示的输出:

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

谁能告诉我我做错了什么，或者建议一种更好的方法来将我需要的数据从一个查询获取到2数组中？

编辑: 经过仔细考虑，我更改了我的代码，按照 ImreL 的建议使用 2 个查询。生成的代码运行良好并且执行速度很快。我编辑了我的答案以显示我使用的最终代码并添加了支持代码来演示我如何使用这 2 个查询。非常感谢 ImreL!

Answer 1

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)

我看到你的意图是好的，但让我告诉你:

“查询次数”不是衡量网站性能的正确方法。

很多时候，2 个简单查询使用的资源少于 1 个复杂查询。

还有其他方法可以加速您的网站:

• 评估您是否真的需要在每个请求中加载所有这些角色和权限。也许只查询所需的角色/权限就足够了。
• 有适当的索引
• 利用缓存技术来减少负载(网站内容)

所以最后，我试着提出查询来满足所问的问题:

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

但这在性能上并不好。有 2 个查询会更好:第一个是获取用户的所有权限

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';

第二个获得所有角色。