前回の閉包テーブルに道程(path_length)を追加する。
道程を追加することで、子や孫を取得しやすくなる。
サンプルテーブル
CREATE TABLE ctable ( ancestor BIGINT NOT NULL , descendant BIGINT NOT NULL , path_length INT NOT NULL -- これを追加 , PRIMARY KEY (ancestor, descendant) , FOREIGN KEY (ancestor) REFERENCES folders(folder_id) , FOREIGN KEY (descendant) REFERENCES folders(folder_id) );
サンプルデータ
ancestor | descendant | path_length |
---|---|---|
1 | 1 | 0 |
1 | 2 | 1 |
1 | 3 | 2 |
1 | 4 | 1 |
1 | 5 | 2 |
2 | 2 | 0 |
2 | 3 | 1 |
3 | 3 | 0 |
4 | 4 | 0 |
4 | 5 | 1 |
5 | 5 | 0 |
子の取得
-- フォルダ1の子 SELECT f.* FROM folders AS f INNER JOIN ctable AS ct ON f.folder_id = ct.descendant WHERE ct.ancestor = 1 AND path_length = 1;
孫の取得
-- フォルダ1の孫 SELECT f.* FROM folders AS f INNER JOIN ctable AS ct ON f.folder_id = ct.descendant WHERE ct.ancestor = 1 AND path_length = 2;
子の追加
追加先のフォルダへの道程(path_length)はすでにあるので、取得した道程に1を加算するだけでOK。
-- フォルダ6を追加 INSERT INTO folders VALUES (6, 'フォルダ6'); -- フォルダ5にフォルダ6を追加 INSERT INTO ctable (ancestor, descendant, path_length) SELECT ct.ancestor, 6, path_length + 1 FROM ctable AS ct WHERE ct.descendant = 5 UNION ALL SELECT 6, 6, 0;
サブツリーの移動
この場合も、道程(path_length)の算出は容易にできる。
-- フォルダ4をフォルダ3に移動する DELETE FROM ctable WHERE descendant IN (SELECT descendant FROM ctable WHERE ancestor = 4) AND ancestor IN (SELECT ancestor FROM ctable WHERE descendant = 4 AND ancestor != descendant); INSERT INTO ctable (ancestor, descendant, path_length) SELECT super.ancestor , sub.descendant , super.path_length + sub.path_length + 1 FROM ctable AS super CROSS JOIN ctable AS sub WHERE super.descendant = 3 AND sub.ancestor = 4;