MySQL多表连接查询问题

业务场景

1. 角色： creater、owner、author
2. 权限：assign owner、assign author
3. 对应关系：
   • creater --> assign owner / assign author ;
   • owner --> assign owner / assign author ;
   • author --> assign author
4. 主题业务： creater 上传一份protocol（主文档），主文档下面有多个section（子文档），每个子文档有一个owner、多个author，在点击主文档连接的时候，就要去加载所有的子文档，以及子文档上对应的owner、author、以及当前登录者对这些子文档的操作权限。

技术

• node js + jquery + bootstrap
• 单页面显示

具体实现

数据库设计

• user(id, name, email) -- 用户表
• role (id, name)       -- 角色表
• activity(id, name, desc)    -- 权限表
• role_activity(roleId, activityId) -- 角色-权限关系表
• document(id, type, name, status) -- 文档表，type区分是protocol还是section
• assets(protocolId, sectionId, sequance, sectionName) -- protocol和section关联表
• assignment(docId, roleId, userId) -- 权限分配表

简要思路

1. 初始化页面的时候，加载所有的protocol，同时加载当前登录用户对每个protocol的权限。
2. 点击每个protocol的时候，根据protocolID去加载protocol下所有的section、section上的owner和authors、当前登录用户对每个section的权限列表。

SQL语句

3.1、初始化protocol列表：

SELECT 
    d.id,
    d.name,
    d.status,
    r.name AS role_name,
    a.name AS activity_name
FROM 
    document d
JOIN 
    assignment a ON d.id = a.docId
JOIN 
    role_activity ra ON a.roleId = ra.roleId AND a.activityId = ra.activityId
JOIN 
    role r ON ra.roleId = r.id
WHERE 
    a.userId = CURRENT_USER();