如何在MySQL中基于用户组获取用户可访问的文档？

现有表结构 #

用户表（Users） #

组表（Groups） #

用户组关联表（User_Groups） #

John和Martin属于Accounts Group，Kane属于IT Group

文档表（Documents） #

• share_type为all的文档对所有用户可见
• share_type为group的文档仅对指定组开放访问权限

文档组关联表（Document_Groups） #

文档2仅对Accounts Group（组ID：1）开放，即John和Martin可访问

需求期望结果 #

• 登录用户John时，返回：

• 登录用户Kane时，返回：

尝试的SQL代码 #

SELECT *
from documents 
LEFT Join document_groups
  on document_groups.document_id = documents.id 
JOIN user_groups
  on document_groups.group_id = user_groups.group_id 
where documents.share_type = "all" or user_groups.user_id = 1

修正后的SQL代码 #

SELECT DISTINCT d.id AS document_id, d.document_name
FROM documents d
LEFT JOIN document_groups dg ON d.id = dg.document_id
LEFT JOIN user_groups ug ON dg.group_id = ug.group_id
WHERE 
  d.share_type = 'all'
  OR (d.share_type = 'group' AND ug.user_id = ?)

逻辑说明 #

1. 用DISTINCT避免因文档关联多组、用户属多组导致的重复记录
2. 两次LEFT JOIN确保share_type='all'的文档不会因关联表无数据被过滤
3. 条件分两类：
   • 直接匹配所有全局可见的文档
   • 对组权限文档，仅匹配当前用户所属组关联的文档
4. 执行时将?替换为当前登录用户ID（如John对应1，Kane对应3）

验证结果 #

• 替换?为1（John），返回2条记录，符合期望
• 替换?为3（Kane），仅返回Policy Document，符合期望

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