Postgresql

遞歸地連接兩個基於 ltree 的樹表

  • December 30, 2018

我有兩個表roles和permissions，它們都ltree用於維護樹結構。我還有一個數據透視表，roles_permissions用於連接這兩個表。我怎樣才能以一種合理有效的方式將它們彼此連接起來——並引入所有的關係？

在這個系統中，祖先決定了 arole或的能力permission。下面的角色building_access將繼承其父級的權利和權限。我可以很容易地查詢roles或permissions確定給定記錄的整個血統。

我要做的是查詢一個角色並確定該角色的完整權限樹。我還嘗試查詢所有角色並為每個角色確定相同的權限列表，並將其聚合成一個字元串。

SELECT r.name AS "role", CONCAT('["', string_agg(p.name, '", "' ORDER BY p.id), '"]') AS "permissions"
FROM roles r
JOIN roles_permissions rp ON rp.role = r.id
JOIN permissions p ON p.id = rp.permission
WHERE r.path @> 'company_employee.warehouse_employee'
GROUP BY r.id;

Actual Results:
---------------------+-------------------------------------------------
       role         |                   permissions
---------------------+-------------------------------------------------
company employee    | ["building access", "break room access"]
warehouse employee  | ["warehouse access", "warehouse stock access"]

Intended Results:
---------------------+-------------------------------------------------
       role         |                   permissions
---------------------+-------------------------------------------------
company employee    | ["building access", "break room access"]
warehouse employee  | ["building access", "break room access", "warehouse access", "warehouse stock access"]

此查詢功能為我提供每個角色的直接權限，但它沒有獲得這些權限的祖先來建立角色的實際總權限。

我的範例數據庫如下所示：

CREATE EXTENSION ltree;
CREATE EXTENSION tablefunc;

CREATE TABLE roles (
 id   int PRIMARY KEY,
 name text NOT NULL,
 path ltree
);
CREATE INDEX roles_path_idx ON roles USING gist (path);

INSERT INTO roles (id, name, path) VALUES
 (1, 'company employee', 'company_employee'),
 (2, 'warehouse employee', 'company_employee.warehouse_employee'),
 (3, 'warehouse manager', 'company_employee.warehouse_employee.warehouse_manager');

CREATE TABLE permissions (
 id int PRIMARY KEY,
 name text NOT NULL,
 path ltree
);
CREATE INDEX permissions_path_idx ON permissions USING gist (path);

INSERT INTO permissions (id, name, path) VALUES
 (1, 'building access', 'building_access'),
 (2, 'break room access', 'building_access.break_room_access'),
 (3, 'warehouse access', 'building_access.warehouse_access'),
 (4, 'warehouse stock access', 'building_access.warehouse_access.warehouse_stock_access'),
 (5, 'warehouse security access', 'building_access.warehouse_access.warehouse_security_access');

CREATE TABLE roles_permissions (
 role       int REFERENCES roles,
 permission int REFERENCES permissions,
 PRIMARY KEY (role, permission)
);

INSERT INTO roles_permissions (role, permission)
VALUES (1, 1), (1, 2), (2, 3), (2, 4), (3, 5);

內連接返回特定路徑的所有祖先，我用來array_agg(distinc避免重複。

       select
           array_agg(distinct p2.name) permissions
       from
           permissions p1
       join
           permissions p2
           on  p2.path @> p1.path

然後我將它用於與路徑匹配的每個角色company_employee.warehouse_employee

select r.name,
      (select array_agg(distinct p2.name) permissions
       from   permissions p1
       join   permissions p2 on  p2.path @> p1.path
       join   roles_permissions rp on rp.permission = p1.id
       where  rp.role in (select distinct r2.id
                          from   roles r1
                          join   roles r2 on r2.path @> r1.path
                          where  r1.id = r.id)) permissions
from   roles r
where  r.path @> 'company_employee.warehouse_employee';

| "company employee"   | "{"building access","break room access"}"                         |
|----------------------|-------------------------------------------------------------------|
| "warehouse employee" | "{"building access","break room access","warehouse access","warehouse stock access"}" |

在這裡重新測試

正如埃文卡羅爾所指出的

  • 如果角色 XYZ 從 XY 繼承權限，因此從 X
  • 並且權限 ABC 從 AB 和 A 繼承

您的橋接表應避免將這些行分配兩次。

給定下一個數據：

ID | Roles    ID | Perm  
---+-----     ---+------ 
1  | X        1  | A     
2  | X.Y      2  | A.B   
3  | X.Y.Z    3  | A.C   
             4  | A.C.D 
             5  | A.C.E

您的橋接表連接 (1,1) 和 (1,2)：

X => A            <-- Redundant
X => A.B ~> A

在這種情況下，(1,1) 是多餘的，因為 A 權限是通過 AB 上的繼承給出的

和 (2,3), (2,4) 一樣：

                          Inherits from role
X.Y => A.C                 ~> X ~> A.B ~> A     <-- Redundant
X.Y => A.C.D ~> A.C ~> A   ~> X ~> A.B ~> A

本質上，你說的是

  • 一個角色x.y.z, 擁有授予 parentx.y和 grandparent的所有權限x
  • 的權限x.y.z, 擁有授予 parentx.y和 grandparent的所有權限x

所以本質上左邊的所有東西，都應該擁有右邊組合組的所有權限，

SELECT r1.name, array_agg(r2.name) AS role_list
FROM roles AS r1
INNER JOIN roles AS r2
 ON r2.path @> r1.path
GROUP BY r1.name;
       name        |                           role_list                           
--------------------+---------------------------------------------------------------
company employee   | {"company employee"}
warehouse manager  | {"company employee","warehouse employee","warehouse manager"}
warehouse employee | {"company employee","warehouse employee"}
(3 rows)

從那時起

SELECT r1.name, array_agg(p.name) AS perm_list
FROM roles AS r1
INNER JOIN roles AS r2 ON r2.path @> r1.path
INNER JOIN roles_permissions AS rp ON rp.role = r2.id
INNER JOIN permissions AS p ON rp.permission = p.id
GROUP BY r1.name;
       name        |                                                    perm_list                                                    
--------------------+-----------------------------------------------------------------------------------------------------------------
company employee   | {"building access","break room access"}
warehouse manager  | {"building access","break room access","warehouse access","warehouse stock access","warehouse security access"}
warehouse employee | {"building access","break room access","warehouse access","warehouse stock access"}
(3 rows)

您可以在WHERE子句中彈回…

SELECT r1.name, array_agg(p.name) AS perm_list
FROM roles AS r1
INNER JOIN roles AS r2 ON r2.path @> r1.path
INNER JOIN roles_permissions AS rp ON rp.role = r2.id
INNER JOIN permissions AS p ON rp.permission = p.id
WHERE r1.path @> 'company_employee.warehouse_employee'
GROUP BY r1.name;

請注意，在此方案中，我們僅用於role_permissions映射到權限，它們本身並不分層工作。無論如何，這不存在於測試數據中。如果你看一下你的映射，(1,1)那是一個奇怪的映射。如果您有休息室使用權，您是否還需要明確知道您有公司使用權？如果沒有，那麼您需要雙方都進行自聯接。

我的診斷是，您的架構很可能是不必要的複雜，您應該選擇分層權限或 M2M 映射。兩者兼而有之是瘋狂的邊緣。

引用自：https://dba.stackexchange.com/questions/225865

相關問答

Postgresql

二叉樹“獲取打開列表”SQL查詢

  • June 8, 2017
檢視問答
Postgresql

如何優化5000萬行表的自連接視圖

  • April 28, 2022
檢視問答
Postgresql

如何以擴展的樹狀方式對遞歸查詢的結果進行排序？

  • February 9, 2022
檢視問答
Postgresql

從遞歸樹結構創建 JSON 對象

  • June 13, 2021
檢視問答
Postgresql

如何將一組扁平的樹變成一棵有多個葉子的樹？

  • June 12, 2021
檢視問答
Postgresql

在循環的情況下如何限制樹遍歷？

  • May 31, 2021
檢視問答