前回の閉包テーブルに道程(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 |
子の取得
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;
孫の取得
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。
INSERT INTO folders VALUES (6, 'フォルダ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)の算出は容易にできる。
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;