sql - 目录要加权限控制，SQL得怎么写？

Question

项目有个目录,需要加权限,页面上只渲染当前用户拥有的"巡检方案"所在的目录节点(子节点及它以上的所有父节点)。这是我渲染的目录所有的节点。各位大佬,请问sql该怎么写呀？
注:目录结构是动态的,不是固定的,后面公司的人可能会再加子节点

集团
    潮州
        工厂1
            办公楼
                男洗手间巡检方案
                杂物间巡检方案
            食堂
        工厂2
            技术楼
            食堂
    昆山
        工厂1
            办公楼
            研发楼

现在要加上权限控制,假如我拥有 "潮州 -> 工厂1 -> 办公楼" 下的巡检方案, 那我进来目录只渲染这样就好

集团
    潮州
        工厂1
            办公楼
                男洗手间巡检方案
                杂物间巡检方案

目录表在数据库里结构是
id, parent_id, name

权限表的是
id directory_id(目录的主键) user_id

我程序查出来的数据明细大致是, 然后用递归的方法生成前端目录需要的结构,返回到前端渲染。
1 0 集团
2 1 潮州
3 1 昆山
4 2 工厂1
5 4 办公楼
...

前端我是用element-ui的el-tree,所以结构是这样的。数据库查出来的明细,也希望按这个上面的明细一样。

[
    'label': '集团',
    'id': 0,
    children:[
        ...
    ]
]

Answer 1

从权限表中找到当前用户拥有权限的所有目录节点及其所有父节点，然后根据这些数据生成前端需要的树状结构。
directory 表结构：

CREATE TABLE directory (
    id INT PRIMARY KEY,
    parent_id INT,
    name VARCHAR(255)
);

permissions 表结构：

CREATE TABLE permissions (
    id INT PRIMARY KEY,
    directory_id INT,
    user_id INT
);

当前用户的 user_id 为 :user_id。首先，我们需要递归地找到当前用户拥有权限的所有目录节点及其所有父节点。

WITH RECURSIVE DirectoryTree AS (
    -- 获取当前用户有权限的目录节点
    SELECT d.id, d.parent_id, d.name
    FROM directory d
    JOIN permissions p ON d.id = p.directory_id
    WHERE p.user_id = :user_id
    UNION ALL
    -- 递归地获取父节点
    SELECT d.id, d.parent_id, d.name
    FROM directory d
    JOIN DirectoryTree dt ON d.id = dt.parent_id
)
SELECT DISTINCT id, parent_id, name
FROM DirectoryTree;

不需要再建一个表。WITH RECURSIVE 是一个CTE（Common Table Expression），用于临时生成一个结果集，这个结果集可以在后续的SELECT查询中使用。

下面是完整的SQL查询语句，适用于SQL Server，来获取当前用户拥有权限的所有目录节点及其所有父节点：

-- 定义用户ID
DECLARE @user_id INT = 123; -- 替换成实际的用户ID

-- 递归CTE，找到所有有权限的节点及其父节点
WITH DirectoryTree AS (
    -- 获取当前用户有权限的目录节点
    SELECT d.id, d.parent_id, d.name
    FROM directory d
    JOIN permissions p ON d.id = p.directory_id
    WHERE p.user_id = @user_id
    UNION ALL
    -- 递归地获取父节点
    SELECT d.id, d.parent_id, d.name
    FROM directory d
    JOIN DirectoryTree dt ON d.id = dt.parent_id
)
-- 获取去重后的结果
SELECT DISTINCT id, parent_id, name
FROM DirectoryTree;

DECLARE @user_id INT = 123;：定义当前用户的ID，你需要将 123 替换为实际的用户ID。
WITH DirectoryTree AS 定义了一个递归CTE，首先从permissions表中获取当前用户有权限的目录节点。
UNION ALL 用于递归地获取这些目录节点的所有父节点。
最终的 SELECT DISTINCT 去重，得到所有相关的目录节点。
这个查询将返回当前用户有权限的所有目录节点及其所有父节点，你可以根据这些数据生成前端需要的树状结构。

在前端使用 element-ui 的 el-tree 组件时，可以将这个查询结果转换成适合 el-tree 组件的格式。