階層問合せを使ってみる - Stellaqua

ちょっとツリー掲示板をサクっと作ってみたくって、実装を考えてました。
まずは単純な発想で、親子関係テーブルを作って、PostgreSQL側でツリー構造を返す問合せができれば、後はPHPでごにょごにょやって、Smartyに渡してごにょごにょすれば、あら不思議…ツリー掲示板のできあがり！ってな具合で…。(笑)

前にどこかで、「PostgreSQLはconnectby()関数っていうので階層問合せができる」ってのを見た事があったので、ちょっと調べて試してみました。以下、覚書きの為のメモ…。

まずは、connectby()関数が使えるようにモジュール組み込み。

$ cd src/postgres/contrib/tablefunc
$ make
$ make install

続いて、connectby()を使いたいDBに組み込み。

$ psql -U postgres treebbs -f ./tablefunc.sql

で、テーブルを準備して、適当にデータを入れる。

treebbs=# create table tree (cid int,pid int);
CREATE TABLE
treebbs=# insert into tree values(1,null);
INSERT 0 1
treebbs=# insert into tree values(2,1);
INSERT 0 1
treebbs=# insert into tree values(3,2);
INSERT 0 1
treebbs=# insert into tree values(4,1);
INSERT 0 1
treebbs=# insert into tree values(5,2);
INSERT 0 1

んで、connectby()で階層問合せしてみる。

treebbs=# select * from connectby('tree','cid','pid',1,0,'-')
treebbs-# as t(cid int,pid int,level int,tree text);
 cid | pid | level | tree
-----+-----+-------+-------
   1 |     |     0 | 1
   2 |   1 |     1 | 1-2
   3 |   2 |     2 | 1-2-3
   5 |   2 |     2 | 1-2-5
   4 |   1 |     1 | 1-4
(5 rows)

ふむ…良い感じでツリーが取得できますな…。
後は、この結果をPHPで取得して、この順番に記事を並べて、level列の値を使ってインデントしてやれば記事ツリーにできそうですね。

ちなみに、connectby()に渡す引数は以下の通りです。
connectby(親子関係表のテーブル名,キーとなるIDの列名,親のIDの列名,ツリーのルートになるキーの値,ツリーの深さ,ツリー文字列の区切り文字)

これに別名を定義して、出力される表の列名などを決めるようです。
as 表の別名(キーID列名型,親ID列名型,深さ列名型,ツリー文字列型)

※参考にした記事

他の実装も考えてみて、どういう実装が処理が早いのか試してみたりしてみようかしらん…。