四表关联查询指定member_id对应角色及权限的SQL实现方案

涉及表结构说明 #

四张表关联链路为：成员 -> 团队成员绑定记录 -> 成员角色关联关系 -> 角色信息 -> 角色对应权限，各表核心字段如下：

• team_member：团队成员绑定关系表，核心字段id、team_id、member_id
• role：角色定义表，核心字段id、team_id、name、slug
• team_member_role：成员与角色多对多关系中间表，核心字段team_member_id、role_id
• role_ability：角色权限表，核心字段id、role_id、action、subject

查询规则说明 #

• 唯一入参：member_id（示例入参值为1）
• 返回字段：成员ID、角色名称、角色对应的权限集合（abilities，值为权限表action字段内容）
• 示例数据校验逻辑：member_id=1对应team_member表中id=92的记录，关联角色ID为1、2；其中角色ID=1拥有read、create、edit三项workspace_members维度权限
• 原有写法问题：未关联team_member表导致无法用member_id过滤，role_ability关联条件错误导致结果不准

正确SQL写法 #

聚合权限为数组的写法（返回单角色单条记录，权限为数组格式） #

根据使用的数据库选择对应聚合函数，MySQL用JSON_ARRAYAGG，PostgreSQL用ARRAY_AGG，示例写法：

SELECT
  tm.member_id,
  r.name AS role_name,
  JSON_ARRAYAGG(ra.action) AS abilities
FROM team_member tm
INNER JOIN team_member_role tmr
  ON tm.id = tmr.team_member_id
INNER JOIN role r
  ON tmr.role_id = r.id
  AND r.team_id = tm.team_id -- 增加团队维度匹配，避免跨团队脏数据
INNER JOIN role_ability ra
  ON r.id = ra.role_id
WHERE tm.member_id = 1 -- 替换为实际入参值
GROUP BY tm.member_id, r.id, r.name;

平铺结果写法（单条权限对应一条记录） #

如果不需要聚合权限，直接返回平铺的明细结果，用以下写法即可：

SELECT
  tm.member_id,
  r.name AS role_name,
  ra.action AS ability
FROM team_member tm
INNER JOIN team_member_role tmr ON tm.id = tmr.team_member_id
INNER JOIN role r ON tmr.role_id = r.id AND r.team_id = tm.team_id
INNER JOIN role_ability ra ON r.id = ra.role_id
WHERE tm.member_id = 1;

内容的提问来源于stack exchange，提问作者yaraw69