PostgreSQLでツリー式のメッセージを管理する

# 目的

ツリー構造を[ltree](https://www.postgresql.jp/docs/9.4/ltree.html)に頼らずRDBで管理しつつ、祖先・子孫要素を含めたデータ取り出しを`with recursive`で実現してみたかった。

# 確認環境
* PostgreSQL 16.8（おそらくPostgreSQL 8.4以降で動作）

# テーブルの構築

メッセージを管理するテーブルを作成します。
全てのメッセージは、返信先のメッセージID（`reply_to`）を持ち、返信先が無い場合はNULLを入れて管理します。
メッセージID（`id`）はSERIAL型なので、`id`と`reply_to`を比較した場合、常に`reply_to`の値の方が小さくなります。

```SQL
CREATE TABLE messages (
    id SERIAL PRIMARY KEY, -- メッセージID
    reply_to INTEGER DEFAULT NULL, -- 返信先のメッセージID。返信先が無ければNULL。
    subject TEXT, -- メッセージタイトル
    content TEXT -- メッセージ本文
    -- 必要に応じて、メッセージ投稿者や投稿日時なども管理
);

-- 自己参照になるため、後から外部キー制約を付ける
ALTER TABLE messages
    ADD constraint reply_to_fkey FOREIGN KEY (reply_to) REFERENCES messages(id) ON DELETE SET NULL;
```


# データの挿入

こんな関係性のメッセージスレッドを実現するようにデータを挿入します。

```plantuml
@startuml
json "**message1**" as message1{
    "id": "**1**",
    "subject": "**Main**",
    "reply_to": "**null**",
    "...":"..."
}
json "**message2**" as message2 {
    "id": "**2**",
    "subject" : "**Re: Main**",
    "reply_to": "**1**",
    "...": "..."
}
json "**message3**" as message3 {
    "id": "**3**",
    "subject": "**Re:Re: Main**",
    "reply_to": "**2**",
    "...": "..."
}
json "**message4**" as message4 {
    "id": "**4**",
    "subject": "**Re:Re: Main**",
    "reply_to": "**2**",
    "...": "..."
}
json "**message5**" as message5 {
    "id": "**5**",
    "subject" : "**Re: Main**",
    "reply_to": "**1**",
    "...": "..."
}
message1  -- message2
message2  -- message3
message2  -- message4
message1  -- message5


@enduml
```

```sql
INSERT INTO messages(reply_to, subject, content)
VALUES
    (NULL, 'Main', 'This is the main message'), -- message1
    (1, 'Re: Main', 'This is the first reply to message id = 1'), -- message2
    (2, 'Re:Re: Main', 'This is the first reply to message id = 2'), -- message3
    (2, 'Re:Re: Main', 'This is the second reply to message id = 2'), -- message4
    (1, 'Re: Main', 'This is the second reply to message id = 1'); -- message5
```

# データの参照メッセージの選択

想定シチュエーション毎に検索クエリを書いていきます。

## ①全選択
特に考えることも無いですね。
おまけで、メッセージの被参照情報（`replied_from`）を配列として追加しています。

```plantuml
@startuml

json "**message1**" as message1 #implementation{
    "id": "**1**",
    "subject": "**Main**",
    "reply_to": "**null**",
    "...": "..."
}
json  "**message2**" as message2 #implementation{
    "id": "**2**",
    "subject" : "**Re: Main**",
    "reply_to": "**1**",
    "...": "..."
}
json  "**message3**" as message3 #implementation{
    "id": "**3**",
    "subject": "**Re:Re: Main**",
    "reply_to": "**2**",
    "...": "..."
}
json  "**message4**" as message4 #implementation{
    "id": "**4**",
    "subject": "**Re:Re: Main**",
    "reply_to": "**2**",
    "...": "..."
}
json  "**message5**" as message5 #implementation{
    "id": "**5**",
    "subject" : "**Re: Main**",
    "reply_to": "**1**",
    "...": "..."
}
message1  -- message2
message2  -- message3
message2  -- message4
message1  -- message5

@enduml
```

```sql
SELECT 
    id, reply_to, subject, content, 
    ARRAY(SELECT id FROM messages WHERE msg.id = reply_to) AS replied_from 
FROM messages AS msg;

-- 出力結果
--  id | reply_to |      subject      |                  content                   | replied_from
-- ----+----------+-------------------+--------------------------------------------+--------------
--   1 |          | Main              | This is the main message                   | {2,5}
--   2 |        1 | Re: Main          | This is the first reply to message id = 1  | {3,4}
--   3 |        2 | Re:Re: Main       | This is the first reply to message id = 2  | {}
--   4 |        2 | Re:Re: Main       | This is the second reply to message id = 2 | {}
--   5 |        1 | Re: Main          | This is the second reply to message id = 1 | {}
```

## ②特定IDのメッセージを起点とした返信メッセージを全て選択する

再帰SQL `WITH RECURSIVE` を使って、指定したIDのメッセージとそれにぶら下がるメッセージ（返信）を全て選択します。（[参考：再帰SQL](https://lets.postgresql.jp/documents/technical/with_recursive/1)）

### id = 1の場合

```plantuml
@startuml
skinparam MapBorderColor #RED

json "**message1**" as message1 #salmon{
    "id": "**1**",
    "subject": "**Main**",
    "reply_to": "**null**",
    "...": "..."
}
note right: 起点
json  "**message2**" as message2 #implementation{
    "id": "**2**",
    "subject" : "**Re: Main**",
    "reply_to": "**1**",
    "...": "..."
}
json  "**message3**" as message3 #implementation{
    "id": "**3**",
    "subject": "**Re:Re: Main**",
    "reply_to": "**2**",
    "...": "..."
}
json  "**message4**" as message4 #implementation{
    "id": "**4**",
    "subject": "**Re:Re: Main**",
    "reply_to": "**2**",
    "...": "..."
}
json  "**message5**" as message5 #implementation{
    "id": "**5**",
    "subject" : "**Re: Main**",
    "reply_to": "**1**",
    "...": "..."
}
message1  -- message2
message2  -- message3
message2  -- message4
message1  -- message5

@enduml
```

```sql
WITH RECURSIVE 
    r AS (
        SELECT 
            msg.id, msg.reply_to, msg.subject, msg.content, 
            ARRAY(SELECT id FROM messages WHERE msg.id = reply_to) AS replied_from 
        FROM messages AS msg 
        WHERE msg.id = 1 -- 取得するメッセージはid = 1とそれにぶら下がるメッセージ
    UNION ALL
        SELECT 
            msg2.id, msg2.reply_to, msg2.subject, msg2.content, 
            ARRAY(SELECT id FROM messages WHERE msg2.id = reply_to) AS replied_from 
        FROM messages AS msg2, r 
        WHERE r.id = msg2.reply_to
    )
SELECT id, reply_to, subject, content, replied_from FROM r ORDER BY id;

-- 出力結果
--  id | reply_to |   subject   |                  content                   | replied_from
-- ----+----------+-------------+--------------------------------------------+--------------
--   1 |          | Main        | This is the main message                   | {2,5}
--   2 |        1 | Re: Main    | This is the first reply to message id = 1  | {3,4}
--   3 |        2 | Re:Re: Main | This is the first reply to message id = 2  | {}
--   4 |        2 | Re:Re: Main | This is the second reply to message id = 2 | {}
--   5 |        1 | Re: Main    | This is the second reply to message id = 1 | {}
```

### id = 2の場合

```plantuml
@startuml
skinparam MapBorderColor #RED

json "**message1**" as message1 {
    "id": "**1**",
    "subject": "**Main**",
    "reply_to": "**null**",
    "...": "..."
}
json  "**message2**" as message2 #salmon{
    "id": "**2**",
    "subject" : "**Re: Main**",
    "reply_to": "**1**",
    "...": "..."
}
note left: 起点
json  "**message3**" as message3 #implementation{
    "id": "**3**",
    "subject": "**Re:Re: Main**",
    "reply_to": "**2**",
    "...": "..."
}
json  "**message4**" as message4 #implementation{
    "id": "**4**",
    "subject": "**Re:Re: Main**",
    "reply_to": "**2**",
    "...": "..."
}
json  "**message5**" as message5 {
    "id": "**5**",
    "subject" : "**Re: Main**",
    "reply_to": "**1**",
    "...": "..."
}
message1  -- message2
message2  -- message3
message2  -- message4
message1  -- message5

@enduml
```

```sql
WITH RECURSIVE 
    r AS (
        SELECT 
            msg.id, msg.reply_to, msg.subject, msg.content, 
            ARRAY(SELECT id FROM messages WHERE msg.id = reply_to) AS replied_from 
        FROM messages AS msg 
        WHERE msg.id = 2 -- 取得するメッセージはid = 2とそれにぶら下がるメッセージ
    UNION ALL
        SELECT 
            msg2.id, msg2.reply_to, msg2.subject, msg2.content, 
            ARRAY(SELECT id FROM messages WHERE msg2.id = reply_to) AS replied_from 
        FROM messages AS msg2, r 
        WHERE r.id = msg2.reply_to
    )
SELECT id, reply_to, subject, content, replied_from FROM r ORDER BY id;

-- 出力結果
--  id | reply_to |   subject   |                  content                   | replied_from
-- ----+----------+-------------+--------------------------------------------+--------------
--   2 |        1 | Re: Main    | This is the first reply to message id = 1  | {3,4}
--   3 |        2 | Re:Re: Main | This is the first reply to message id = 2  | {}
--   4 |        2 | Re:Re: Main | This is the second reply to message id = 2 | {}
```

## ③特定IDのメッセージを起点とした返信先メッセージを全て選択する

同様に、指定したIDのメッセージと、その親となるメッセージ（返信先）を辿って全て選択します。

### id = 2の場合

```plantuml
@startuml
skinparam MapBorderColor #RED

json "**message1**" as message1 #implementation{
    "id": "**1**",
    "subject": "**Main**",
    "reply_to": "**null**",
    "...": "..."
}
json  "**message2**" as message2 #salmon{
    "id": "**2**",
    "subject" : "**Re: Main**",
    "reply_to": "**1**",
    "...": "..."
}
note left: 起点
json  "**message3**" as message3 {
    "id": "**3**",
    "subject": "**Re:Re: Main**",
    "reply_to": "**2**",
    "...": "..."
}
json  "**message4**" as message4 {
    "id": "**4**",
    "subject": "**Re:Re: Main**",
    "reply_to": "**2**",
    "...": "..."
}
json  "**message5**" as message5 {
    "id": "**5**",
    "subject" : "**Re: Main**",
    "reply_to": "**1**",
    "...": "..."
}
message1  -- message2
message2  -- message3
message2  -- message4
message1  -- message5

@enduml
```

```sql
WITH RECURSIVE 
    r AS (
        SELECT 
            msg.id, msg.reply_to, msg.subject, msg.content, 
            ARRAY(SELECT id FROM messages WHERE msg.id = reply_to) AS replied_from 
        FROM messages AS msg 
        WHERE msg.id = 2 -- 取得するメッセージはid = 2とその返信先メッセージ
    UNION ALL
        SELECT 
            msg2.id, msg2.reply_to, msg2.subject, msg2.content, 
            ARRAY(SELECT id FROM messages WHERE msg2.id = reply_to) AS replied_from 
        FROM messages AS msg2, r 
        WHERE msg2.id = r.reply_to
    )
SELECT id, reply_to, subject, content, replied_from FROM r ORDER BY id;

-- 出力結果
--  id | reply_to |   subject   |                  content                   | replied_from
-- ----+----------+-------------+--------------------------------------------+--------------
--   1 |          | Main        | This is the main message                  | {2,5}
--   2 |        1 | Re: Main    | This is the first reply to message id = 1 | {3,4}
```

### id = 3の場合

```plantuml
@startuml
skinparam MapBorderColor #RED

json "**message1**" as message1 #implementation{
    "id": "**1**",
    "subject": "**Main**",
    "reply_to": "**null**",
    "...": "..."
}
json  "**message2**" as message2 #implementation{
    "id": "**2**",
    "subject" : "**Re: Main**",
    "reply_to": "**1**",
    "...": "..."
}
json  "**message3**" as message3 #salmon{
    "id": "**3**",
    "subject": "**Re:Re: Main**",
    "reply_to": "**2**",
    "...": "..."
}
note right: 起点
json  "**message4**" as message4 {
    "id": "**4**",
    "subject": "**Re:Re: Main**",
    "reply_to": "**2**",
    "...": "..."
}
json  "**message5**" as message5 {
    "id": "**5**",
    "subject" : "**Re: Main**",
    "reply_to": "**1**",
    "...": "..."
}
message1  -- message2
message2  -- message3
message2  -- message4
message1  -- message5

@enduml
```

```sql
WITH RECURSIVE 
    r AS (
        SELECT 
            msg.id, msg.reply_to, msg.subject, msg.content, 
            ARRAY(SELECT id FROM messages WHERE msg.id = reply_to) AS replied_from 
        FROM messages AS msg 
        WHERE msg.id = 3 -- 取得するメッセージはid = 3とその返信先メッセージ
    UNION ALL
        SELECT 
            msg2.id, msg2.reply_to, msg2.subject, msg2.content, 
            ARRAY(SELECT id FROM messages WHERE msg2.id = reply_to) AS replied_from 
        FROM messages AS msg2, r 
        WHERE msg2.id = r.reply_to
    )
SELECT id, reply_to, subject, content, replied_from FROM r ORDER BY id;

-- 出力結果
--  id | reply_to |   subject   |                  content                  | replied_from
-- ----+----------+-------------+-------------------------------------------+--------------
--   1 |          | Main        | This is the main message                  | {2,5}
--   2 |        1 | Re: Main    | This is the first reply to message id = 1 | {3,4}
--   3 |        2 | Re:Re: Main | This is the first reply to message id = 2 | {}
```

## ④特定IDのメッセージが含まれるスレッドのメッセージ全体を取得する

特定メッセージの含まれるスレッド全体を取得したい場合。
どのメッセージを起点としても、③の操作でスレッドの頂点となるメッセージを見つけつつ、②の操作で頂点以下のメッセージすべてを拾ってくるイメージです。

```plantuml
@startuml

json "**message1**" as message1 #implementation{
    "id": "**1**",
    "subject": "**Main**",
    "reply_to": "**null**",
    "...": "..."
}
json  "**message2**" as message2 #salmon{
    "id": "**2**",
    "subject" : "**Re: Main**",
    "reply_to": "**1**",
    "...": "..."
}
note left: 起点
json  "**message3**" as message3 #implementation{
    "id": "**3**",
    "subject": "**Re:Re: Main**",
    "reply_to": "**2**",
    "...": "..."
}
json  "**message4**" as message4 #implementation{
    "id": "**4**",
    "subject": "**Re:Re: Main**",
    "reply_to": "**2**",
    "...": "..."
}
json  "**message5**" as message5 #implementation{
    "id": "**5**",
    "subject" : "**Re: Main**",
    "reply_to": "**1**",
    "...": "..."
}

message1  -- message2
message2  -- message3
message2  -- message4
message1  -- message5

@enduml
```

```sql
WITH RECURSIVE 
    r AS (
        SELECT 
            msg.id, msg.reply_to, msg.subject, msg.content, 
            ARRAY(SELECT id FROM messages WHERE msg.id = reply_to) AS replied_from 
        FROM messages AS msg 
        WHERE id = 2 -- 取得するメッセージはid = 2が含まれるスレッドのメッセージ全体
    UNION ALL
        SELECT 
            msg2.id, msg2.reply_to, msg2.subject, msg2.content, 
            ARRAY(select id FROM messages WHERE msg2.id = reply_to) AS replied_from 
        FROM messages as msg2, r 
        WHERE msg2.id = r.reply_to
    ),
    r2 as (
        SELECT 
            msg.id, msg.reply_to, msg.subject, msg.content, 
            ARRAY(SELECT id FROM messages WHERE msg.id = reply_to) AS replied_from 
        FROM messages AS msg 
        WHERE id = (SELECT MIN(id) FROM r) -- idが一番小さいメッセージがスレッドの頂点
    UNION ALL
        SELECT 
            msg2.id, msg2.reply_to, msg2.subject, msg2.content, 
            ARRAY(SELECT id FROM messages WHERE msg2.id = reply_to) AS replied_from 
        FROM messages AS msg2, r2 
        WHERE r2.id = msg2.reply_to
    )
SELECT id, reply_to, subject, content, replied_from FROM r2 ORDER BY id;

-- 出力結果
--  id | reply_to |   subject   |                  content                   | replied_from
-- ----+----------+-------------+--------------------------------------------+--------------
--   1 |          | Main        | This is the main message                   | {2,5}
--   2 |        1 | Re: Main    | This is the first reply to message id = 1  | {3,4}
--   3 |        2 | Re:Re: Main | This is the first reply to message id = 2  | {}
--   4 |        2 | Re:Re: Main | This is the second reply to message id = 2 | {}
--   5 |        1 | Re: Main    | This is the second reply to message id = 1 | {}
```

# 最後に
正直、コメントツリーの管理だけなら非RDBかも。（例が悪かった…）
`組織`や`カテゴリー`など、整合性の求められる階層構造であれば使えなくはないかも。