閉包テーブル (Closure Table) は RDB で階層構造を表現する際に使われるデータモデルです。数年前にとある案件で使ったのですが、内容を忘れかけてるので復習します。備忘録。
閉包テーブルでは直接の親子関係だけでなく、階層全体の関係を持つ。閉包テーブルを利用すると階層構造をシンプルに表現できる。特にクエリがシンプルになるのが利点。ただし、階層全体の関係を持つため、階層が深くなるほどデータサイズは大きくなる。
ルートアクセス
ルート直下 (最上位階層) のフォルダを取得する場合、祖先フォルダIDにルートのIDを指定して、深さ 1 で検索する。ルートは特別な扱いになるため、IDには何かしらの固定値を用いる。
SELECT * FROM FolderPath as fp JOIN Folder as f ON fp.祖先フォルダID = f.id WHERE fp.祖先フォルダID = {ルート} AND fp.深さ = 1
サブフォルダアクセス
ルートアクセスと同様。祖先フォルダIDにカレントのフォルダIDを指定して、深さ 1 で検索する。
-- フォルダAを基点にする場合 SELECT * FROM FolderPath as fp JOIN Folder as f ON fp.祖先フォルダID = f.id WHERE fp.祖先フォルダID = {フォルダA} AND fp.深さ = 1
パンくずリスト
カレントフォルダまでのパス。子孫フォルダIDにカレントのフォルダIDを指定して検索する。深さを降順で並べ替えるとパンくずリストになる。
-- フォルダCを基点にする場合 SELECT f.* FROM FolderPath as fp JOIN Folder as f ON fp.祖先フォルダID = f.id WHERE fp.子孫フォルダID = {フォルダC} ORDER BY fp.深さ DESC
フォルダ削除
閉包テーブルでは階層全体の関係を持つ。フォルダ削除では、子孫フォルダIDに対象のフォルダIDを持つすべての階層データを削除する。
-- フォルダDを削除する場合 DELETE FROM FolderPath as fp WHERE fp.子孫フォルダID = {フォルダD}
フォルダ追加
閉包テーブルでは階層全体の関係を持つ。フォルダ追加では、子孫フォルダIDに対象のフォルダIDを持つすべての階層データを作成する。対象のフォルダ自身を深さ 0 として、親フォルダのパンくずリストでルートまで辿りながら深さを +1 する。以下はフォルダE配下にフォルダXを追加する場合。