LoginSignup
1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

AIエージェントの知見を共有しよう！
@takesue

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

Posted at

目的

ツリー構造をltreeに頼らずRDBで管理しつつ、祖先・子孫要素を含めたデータ取り出しをwith recursiveで実現してみたかった。

確認環境

  • PostgreSQL 16.8（おそらくPostgreSQL 8.4以降で動作）

テーブルの構築

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

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;

データの挿入

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

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）を配列として追加しています。

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）

id = 1の場合

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の場合

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の場合

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の場合

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のメッセージが含まれるスレッドのメッセージ全体を取得する

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

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かも。（例が悪かった…）
組織やカテゴリーなど、整合性の求められる階層構造であれば使えなくはないかも。

1
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?