Postgresqlでtree構造にWith句を使ってみる

   postgresql9.5で実行していますがマテビューは9.3以上、with句はもっと昔から使えます。

コメントやカテゴリの管理などを行う場合に使うtree構造をDBで管理するのは結構面倒くさい。
ここのnative treeの所に幾つか実装方法が書いてあるがどれもこれも結構面倒というか難しい。

一番直感的に分かりやすい実装方法はやはり親IDを持つ方法だろう。ただ、JOINを使って階層を辿ろうと思うと大変なことになる。
そもそも階層に限りが無いのでJOINを何回行えばいいかもわからなかったりするので、条件分岐も必要になってくる。

そこでWith句。しかもRECURSIVE。

例えば下記の様なテーブルとレコードがあるとする。

CREATE TABLE items (
    id INT PRIMARY KEY,
    parent_id  INT,
    name TEXT NOT NULL
);
ALTER TABLE items ADD CONSTRAINT SET FOREIGN KEY (parent_id) REFERENCES items(id) ON DELETE CASCADE;


INSERT INTO items VALUES(1, null, 'usr');
INSERT INTO items VALUES(2, null, 'etc');
INSERT INTO items VALUES(3, 1, 'bin');
INSERT INTO items VALUES(4, 3, 'zip');
INSERT INTO items VALUES(5, 1, 'lib');
INSERT INTO items VALUES(6, 2, 'passwd');
INSERT INTO items VALUES(7, 2, 'sysconfig');
INSERT INTO items VALUES(8, 7, 'network-scripts');
INSERT INTO items VALUES(9, 8, 'ifcfg-eth0');

ルートノードはparent_idがnullになっています。
これをルートから辿ったツリー構造を表示すると下記の構文で一発です。
また、自分で自分に外部キーをはってON DELETE CASCADEしておくと、配下のデータをまとめて消せるので便利。

WITH RECURSIVE r AS (
SELECT items.*, array[id] AS id_tree, '/' || name AS path, 1 AS depth FROM items WHERE parent_id IS NULL
UNION ALL
SELECT items.*, r.id_tree || items.id, r.path || '/' || items.name, r.depth + 1 FROM items INNER JOIN r ON items.parent_id = r.id
)
SELECT id,name,id_tree,path,depth FROM r;

id	name	id_tree	path	depth
1	usr	{1}	/usr	1
2	etc	{2}	/etc	1
3	bin	{1,3}	/usr/bin	2
5	lib	{1,5}	/usr/lib	2
6	passwd	{2,6}	/etc/passwd	2
7	sysconfig	{2,7}	/etc/sysconfig	2
4	zip	{1,3,4}	/usr/bin/zip	3
8	network-scripts	{2,7,8}	/etc/sysconfig/network-scripts	3
9	ifcfg-eth0	{2,7,8,9}	/etc/sysconfig/network-scripts/ifcfg-eth0	4

結局これって何やっているかというと下記の様な入れ子のSQLをグリグリ回しているだけです。

SELECT items.*, array[id] AS id_tree, '/' || name AS path, 1 AS depth FROM items WHERE parent_id IS NULL
UNION ALL
SELECT items.*, r.id_tree || items.id, r.path || '/' || items.name, r.depth + 1 FROM items INNER JOIN (
    SELECT items.*, array[id] AS id_tree, '/' || name AS path, 1 AS depth FROM items WHERE parent_id IS NULL
    UNION ALL
    SELECT items.*, r.id_tree || items.id, r.path || '/' || items.name, r.depth + 1 FROM items INNER JOIN (
        SELECT items.*, array[id] AS id_tree, '/' || name AS path, 1 AS depth FROM items WHERE parent_id IS NULL
        UNION ALL
        SELECT items.*, r.id_tree || items.id, r.path || '/' || items.name, r.depth + 1 FROM items INNER JOIN (
            SELECT items.*, array[id] AS id_tree, '/' || name AS path, 1 AS depth FROM items WHERE parent_id IS NULL
        ) as r ON items.parent_id = r.id
    ) as r ON items.parent_id = r.id
) as r ON items.parent_id = r.id;

ちなみにMySQLでWith句は使えないです。

階層が深くなり、アイテム数が増えればそれなりに重いのでマテリアライズドビューを使えばidからすぐにパスを取ることができます。アイテムが追加されるたびにリフレッシュしないといけないですが。

CREATE MATERIALIZED VIEW item_tree AS
WITH RECURSIVE r AS (
SELECT items.*, array[id] AS id_tree, '/' || name AS path, 1 AS depth FROM items WHERE parent_id IS NULL
UNION ALL
SELECT items.*, r.id_tree || items.id, r.path || '/' || items.name, r.depth + 1 FROM items INNER JOIN r ON items.parent_id = r.id
)
SELECT id,name,id_tree,path,depth FROM r;
CREATE INDEX item_tree_index0 ON item_tree(id);