获取特定用户的权限

Question

我编写了一个大型的MySQL查询语句，用于检索给定用户的所有权限，最近发现在新版本的MySQL中由于ONLY_FULL_GROUP_BY模式，该查询语句无法正常工作。

用于检索给定用户已授予的所有权限的查询语句是：

SELECT
    `name`,
    `display_name`,
    `description`
FROM(
    SELECT * FROM (
        SELECT
            `p`.`id`,
            `p`.`name`,
            `p`.`display_name`,
            `p`.`description`,
            MAX(`r`.`priority`),
            IFNULL(`up`.`is_granted`, `rp`.`is_granted`) AS `is_granted`
        FROM `permissions` AS `p`
        LEFT JOIN `user_permissions` AS `up`
            ON `up`.`permission` = `p`.`id`
            AND `up`.`user` = 1
        LEFT JOIN `role_permissions` `rp`
            ON `rp`.`permission` = `p`.`id`
            AND `rp`.`role` IN(
                SELECT
                    `ur`.`role`
                FROM `user_roles` AS `ur`
                WHERE `ur`.`user` = 1
            )
        LEFT JOIN `roles` AS `r`
            ON `r`.`id` = `rp`.`role`
        GROUP BY `r`.`priority` DESC, `rp`.`permission`, `up`.`permission`
    ) AS `res`
    GROUP BY `res`.`id` ASC
) AS `res`
WHERE `res`.`is_granted` = 1

这会在较新版本启用ONLY_FULL_GROUP_BY（默认情况下启用）时导致以下错误：SQL Error (1055)：SELECT列表中的第5个表达式不在GROUP BY子句中，包含非聚合列“res.MAX(r.priority)”，其与GROUP BY子句中的列没有功能依赖性； 与sql_mode = only_full_group_by不兼容。

我尝试完全重写查询以实现更高效的工作，并启用了标志，但它并没有完全达到第一个查询的目标。

SELECT
    u.id AS user_id,
    u.email AS user_email,
    up.permission AS user_permission_id,
    up.is_granted AS user_permission_is_granted,
    upp.name AS user_permission_name,
    upp.display_name AS user_permission_display_name,
    upp.description AS user_permission_description,
    ur.role AS role_id,
    urr.name AS role_name,
    urr.display_name AS role_display_name,
    urr.priority AS role_priority,
    urp.permission AS role_permission_id,
    urp.is_granted AS role_permission_is_granted,
    urpp.name AS role_permission_name,
    urpp.display_name AS role_permission_display_name,
    urpp.description AS role_permission_description
FROM users u
LEFT JOIN user_permissions up
    ON up.user = u.id
LEFT JOIN permissions upp
    ON upp.id = up.permission
LEFT JOIN user_roles ur
    ON ur.user = u.id
LEFT JOIN roles urr
    ON urr.id = ur.role
LEFT JOIN role_permissions urp
    ON urp.role = ur.role
LEFT JOIN permissions urpp
    ON urpp.id = urp.permission
WHERE u.id = 1

我该如何做到这一点？我已经尝试了几次，但是没有找到只返回具有最高优先级的行而不禁用ONLY_FULL_GROUP_BY模式的方法。

我应该只使用较新的查询并将角色优先级应用于应用程序代码中，而不是在SQL查询中应用吗？

这里是带有数据库结构和查询的DBFiddle。

给定上面的DBFiddle中的数据，我希望结果集如下所示：

| user_id | user_email        | permission_id | permission_name | permission_display_name | permission_description                             | is_granted |
| ------- | ----------------- | ------------- | --------------- | ----------------------- | -------------------------------------------------- | ---------- |
| 1       | [e-mail] | 1             | test1           | Test permission 1       | Role permission, should ONLY be granted to user 1  | 1          |
| 1       | [e-mail] | 2             | test2           | Test permission 2       | Role permission, should ONLY be granted to user 1  | 1          |
| 1       | [e-mail] | 3             | test3           | Test permission 3       | Role permission, should ONLY be granted to user 2  | 0          |
| 2       | [e-mail] | 3             | test3           | Test permission 3       | Role permission, should ONLY be granted to user 2  | 1          |
| 1       | [e-mail] | 4             | test4           | Test permission 4       | Role permission, should be granted to user 1 and 2 | 1          |
| 2       | [e-mail] | 4             | test4           | Test permission 4       | Role permission, should be granted to user 1 and 2 | 1          |
| 1       | [e-mail] | 5             | test5           | Test permission 5       | User permission, granted to user 1                 | 1          |
| 2       | [e-mail] | 6             | test6           | Test permission 6       | User permission, granted to user 2                 | 1          |

我希望允许ID为3的权限返回is_granted = 0，而不是在结果集中不出现，这是因为用户最初被授予该权限，但随后未被授予较高优先级的角色ID 2的权限。

这个问题不是SQL select only rows with max value on a column的重复，因为它特别涉及如何修改我的查询以解决ONLY_FULL_GROUP_BY模式。

- Alexander Sagen

---

你的 GROUP BY 子句中为什么要包含 r.priority 列，但是在 SELECT 子句中也有 MAX(r.priority)？ - Progman

请编辑您的问题，包括表格的示例数据，结果集的内容以及您当前获得的结果集（请参阅https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query）。 - Progman

感谢@Progman的回复，我已经更新了问题并提供了DBFiddle和期望的结果集。我相信只需修改查询语句即可使用当前数据结构实现所需的结果集。 - Alexander Sagen

Answer 1

通过仔细查看重复的问题并理解该问题上的答案如何工作，我解决了这个问题。

我不得不创建一个视图，将role_permissions表与roles表中的priority列连接在一起。然后，我必须获取每个用户的所有最高优先级角色权限。最后，我必须获取所有未覆盖用户任何角色权限的用户权限，并使用UNION将其与角色权限连接在一起。

这是我的解决方案：

SELECT
    u.id AS user_id,
    u.email AS user_email,
    p.id AS permission_id,
    p.name AS permission_name,
    p.display_name AS permission_display_name,
    p.description AS permission_description,
    IFNULL(up.is_granted, IFNULL(rp2.is_granted, rp1.is_granted)) AS is_granted
FROM users u
LEFT JOIN role_permissions_withpriority rp1
    ON rp1.role IN(SELECT role FROM user_roles WHERE user = u.id)
LEFT OUTER JOIN role_permissions_withpriority rp2
    ON rp2.role IN(SELECT role FROM user_roles WHERE user = u.id) AND rp2.permission = rp1.permission AND rp2.priority > rp1.priority
LEFT OUTER JOIN user_permissions up
    ON up.user = u.id AND up.permission = rp1.permission
LEFT JOIN permissions p
    ON p.id = rp1.permission
GROUP BY user_id, permission_id, is_granted
UNION
SELECT
    u.id AS user_id,
    u.email AS user_email,
    p.id AS permission_id,
    p.name AS permission_name,
    p.display_name AS permission_display_name,
    p.description AS permission_description,
    up.is_granted AS is_granted
FROM users u
LEFT JOIN user_permissions up
    ON up.user = u.id
LEFT JOIN permissions p
    ON p.id = up.permission
WHERE p.id IS NOT NULL
GROUP BY user_id, permission_id, is_granted

这里是一个更新的DBFiddle，演示了解决方案正在工作。

- Alexander Sagen