カテゴリー
SQL

ツリーの閉包モデル リーフの移動、追加で自分への補足 「SQLアンチパターン」より

1月にオライリー・ジャパンより「SQLアンチパターン」が発売されました。
この本で紹介されているツリー構造をRDBMSで扱う手法の一つ「閉包モデル」は初めて知ったのですが、最後の方に出てくるpath_lengthを追加した場合のSQLが掲載されていなかったので、自分のために補足として書き残しておきます。

以下は本に掲載されているリーフを追加する際のSQLですが…

INSERT INTO TreePaths (ancestor, descendant)
SELECT t.ancestor, 8
FROM TreePaths AS t
WHERE t.descendant = 5
UNION ALL
SELECT 8, 8;

このままではpath_lengthが追加されません。
そこで、以下のようにサブクエリ部分のフィールドで既存レコードのpath_lengthに+1して返すようにすれば、追加されるリーフのpath_lengthは自動で計算されることになります。

INSERT INTO TreePaths (ancestor, descendant, path_length)
SELECT t.ancestor, t.path_length + 1, 8
FROM TreePaths AS t
WHERE t.descendant = 5
UNION ALL
SELECT 8, 8;

また、リーフを移動する際のSQLは以下のように掲載されていますが…

INSERT INTO TreePaths (ancestor, descendant)
SELECT supertree.ancestor, subtree.descendant
FROM TreePaths AS supertree
CROSS JOIN TreePaths AS subtree
WHERE supertree.descendant = 3
AND subtree.ancestor = 6;

これを以下のようにサブクエリ部分のフィールドでsupertree.path_length + subtree.path_length + 1を返せば、自動的にpath_lengthが計算された状態でリーフが移動されます。

INSERT INTO TreePaths (ancestor, descendant,path_length)
SELECT supertree.ancestor, subtree.descendant, supertree.path_length + subtree.path_length + 1
FROM TreePaths AS supertree
CROSS JOIN TreePaths AS subtree
WHERE supertree.descendant = 3
AND subtree.ancestor = 6;

以上、試してないけど、たぶんこれでいけるはず。

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください