網頁

2023/1/13

PostgreSQL 自我參照關聯資料表 self-referencing relationship

PostgreSQL 自我關聯資料表範例。


所謂的自我關聯(self-referencing relationship)是指自己關聯自己。一般資料表關聯都是指不同資料表間的關聯,例如部門與員工分別為departmentemployee兩張資料表並以employee.department_id關聯至department.id



而自我關聯則是同一張表自己參考自己,透過自身的欄位參考到自身的另一欄位。自我關聯通常用在階層或樹狀結構的設計,例如組織結構、目錄結構、親子結構。


自我關聯資料表

下面建立一張自我關聯資料表organization,其parent_id外鍵參考到自身的id,意思是說organization中一筆資料的parent_id會參考到另一筆(或本身)資料的id

CREATE TABLE public.organization (
    id integer NOT NULL,
    parent_id integer NOT NULL,
    "name" varchar(60) NOT NULL,
    CONSTRAINT organization_pk PRIMARY KEY (id),
    CONSTRAINT organization_fk FOREIGN KEY (parent_id) REFERENCES public.organization(id)
);




新增資料

新增階層資料如下,每筆資料的parent_id皆參考到另一筆資料的id,除了階層頂端的資料的parent_id是參考自身的id

INSERT INTO organization (id, parent_id, "name") VALUES(1, 1, 'headquarter');
INSERT INTO organization (id, parent_id, "name") VALUES(2, 1, 'branch-a');
INSERT INTO organization (id, parent_id, "name") VALUES(3, 1, 'branch-b');
INSERT INTO organization (id, parent_id, "name") VALUES(4, 2, 'branch-manager-1');
INSERT INTO organization (id, parent_id, "name") VALUES(5, 2, 'branch-manager-2');
INSERT INTO organization (id, parent_id, "name") VALUES(6, 4, 'department-manager-1');
INSERT INTO organization (id, parent_id, "name") VALUES(7, 4, 'department-manager-2');
INSERT INTO organization (id, parent_id, "name") VALUES(8, 6, 'team-a');
INSERT INTO organization (id, parent_id, "name") VALUES(9, 6, 'team-b');
INSERT INTO organization (id, parent_id, "name") VALUES(10, 8, 'team-member-1');
INSERT INTO organization (id, parent_id, "name") VALUES(11, 8, 'team-member-2');

上面的階層關係由上而下名稱設計為:

  1. headquarter(總部)
  2. branch(分公司)
  3. branch manager(分公司經理)
  4. department manager(部門經理)
  5. team(團隊)
  6. team memmber(團隊成員)





查詢以下階層

若要查詢某一筆資料以下的階層,PostgreSQL要利用CTE遞迴查詢(recursive CTE)。

例如下面查詢id=4branch-manager-1(含)以下階層的資料。

WITH RECURSIVE sub AS (
    SELECT o.*
    FROM organization o
    WHERE o.id = 4
    UNION
    SELECT oc.*
    FROM organization oc
    INNER JOIN sub s ON s.id = oc.parent_id
) 
SELECT * FROM sub;

查詢結果如下。

 id | parent_id |         name
----+-----------+----------------------
  4 |         2 | branch-manager-1
  6 |         4 | department-manager-1
  7 |         4 | department-manager-2
  8 |         6 | team-a
  9 |         6 | team-b
 10 |         8 | team-member-1
 11 |         8 | team-member-2




查詢以上階噌

查詢某一筆資料以上的階層。

例如下面查詢id=4branch-manager-1(含)以上階層的資料。

WITH RECURSIVE sup AS (
    SELECT o.*
    FROM organization o
    WHERE o.id = 4
    UNION
    SELECT op.*
    FROM organization op
    INNER JOIN sup s ON s.parent_id = op.id
) 
SELECT * FROM sup;
 id | parent_id |       name
----+-----------+------------------
  4 |         2 | branch-manager-1
  2 |         1 | branch-a
  1 |         1 | headquarter




查上一階

下面查詢id = 4branch-manager-1上一階層的資料。

SELECT op.* 
FROM organization o 
INNER JOIN organization op ON op.id = o.parent_id  
WHERE o.id = 4;
 id | parent_id |   name
----+-----------+----------
  2 |         1 | branch-a



查下一階

下面查詢id = 4branch-manager-1下一階層的資料。

SELECT oc.* 
FROM organization o 
INNER JOIN organization oc ON oc.parent_id  = o.id  
WHERE o.id = 4;
 id | parent_id |         name
----+-----------+----------------------
  6 |         4 | department-manager-1
  7 |         4 | department-manager-2


新增最下階層

若今天在最下階層的team member下要新增一階project,則將新階層的parent_id值設為上一階的id即可。

例如team-member-1(id=10)下新增project-a,則project-a的parent_id=10

INSERT INTO organization (id, parent_id, "name") VALUES(12, 10, 'project-a');




新增中間階層

若今天在中間階層branch manager與department manager間插入一階department,則除了新增階層的parent_id值設為上一階的id,還必須把原本下一階層的parent_id設為新階層的id

例如branch-manager-1(id=4)與department-manaager-1(id=6)、department-manager-2(id=7)中間新增department-a,則department-a的parent_id=4;department-manager-1及department-manager-2更新parent_id=12

INSERT INTO organization (id, parent_id, "name") VALUES(12, 4, 'department-a');
UPDATE 
  organization
SET 
    parent_id = 12
WHERE
    parent_id = 4 AND id != 12;




沒有留言:

張貼留言