SQLでツリーのルートを知りたい
前提
PostgreSQL(WITH RECURSIVE使える)
一つのテーブルに複数のツリーが格納されている
code:create-table.sql
create table trees(
id integer primary key,
parent_id integer -- 親ノードのid。trees.idを参照する
);
code:insert-data.sql
insert into trees values (1, null), (2, 1), (3, 2), (4, null), (5, 4), (6, 2);
↑はこんな形の木構造だと見做す
code:tree
1
└── 2
└── 3
└── 6
4
└── 5
テーブル構造変えてもいいのでもっといいデータの持ち方あったら知りたい
やりたいこと
各行について、「自分の属するツリーのルートノードのID」という列を追加して全件を表示したい
ルート自身は自分のルート欄はNULLでもまあいい
こんな感じの結果にしたい
code:result
id | root_id
----+---------
1 | 1
2 | 1
3 | 1
4 | 4
5 | 4
6 | 1
解決策
データ入れる時に階層が分かるならよさそう
分からない時はスクリプトとかで別途計算するんですかね
でもスクリプト使っていいならそもそもroot_idは計算じゃなくて最初からそのカラム持てばいい話
スクリプトを使って何回もやる