目的
- ナイーブツリーで構成されている多階層テーブルにおいて
- 任意の階層に所属しているノード群(= 親ノード)に所属している末端ノードの一覧を
- 親ノードとの関連が分かるように
- 1クエリで
取得したい。
つまり
| 親ノードid | 末端ノードid |
|---|---|
| 3 | 20 |
| 3 | 21 |
| 3 | 22 |
| 4 | 25 |
| 4 | 26 |
| 4 | 27 |
みたいな結果を取得したい。
環境
MySQL 8~
MySQLだと8系以降でしか再帰クエリが使えないため。
他DBMSでも再帰クエリが使えれば同じことはできるはず。多分。
前提のテーブル構造
tableName: naive_tree
| id | parent_id | depth | is_leaf |
|---|---|---|---|
| プライマリ | 親id | 階層 | 末端ノードならtrue |
再帰クエリについて
ざっくり。
-- ここから再帰クエリ。`temporary_table_name`という名前で、hoge, fuga, piyo, ...の構造を持つ一時的なテーブルを作成する
WITH RECURSIVE <temporary_table_name> (<hoge>, <fuga>, <piyo>, ...) AS (
-- ここから非再帰項
-- 最初に実行され、再帰項を実行するベースデータを取得する
SELECT <hoge>, <fuga>, <piyo>, ... -- 上記で定義したものと同じ構造である必要がある
-- ここまで非再帰項
UNION ALL -- 非再帰項と再帰項それぞれで取得できた結果をつなげる
-- ここから再帰項
-- 非再帰項の取得結果をベースに実行される
SELECT <hoge>, <fuga>, <piyo>, ... -- 上記で定義したものと同じ構造である必要がある
FROM <temporary_table_name> WHERE <condition> -- 一時テーブル自身(temporary_table_name)を使う
-- ここまで再帰項
)
-- ここまで再帰クエリ
SELECT *
FROM temporary_table_name
クエリ
SET @target_parent_depth = 1; -- 階層数を指定
WITH RECURSIVE tree(id, parent_id, depth, is_leaf, target_depth_parent_id) AS (
-- 指定された階層のレコードを全て取得
-- 自分自身が親なのでtarget_depth_parent_idは0としておく
SELECT id, parent_id, depth, is_leaf, 0 as target_depth_parent_id
FROM naive_tree
WHERE id IN (
SELECT n.id
FROM naive_tree n
WHERE n.depth = @target_parent_depth
)
UNION ALL
SELECT
n2.id,
n2.parent_id,
n2.depth,
n2.is_leaf,
(
CASE
WHEN n2.depth = @target_parent_depth + 1 THEN n2.parent_id
ELSE t.target_depth_parent_id
END
) as target_depth_parent_id
-- ここがキモ
-- JOINされたレコードが非再帰項で取得された階層の「次の」階層だった場合には、JOINされた側のレコードが持つparent_idを使う。
-- それ以外のケースでは、JOIN元のレコードのtarget_depth_parent_idをそのまま使う。
FROM tree t
INNER JOIN naive_tree n2 ON t.id = n2.parent_id
)
SELECT
t.id,
t.target_depth_parent_id
FROM tree t
WHERE t.is_leaf = 1 AND target_depth_parent_id != 0 -- 末端ノードであり、かつ親レコードではないことを条件とする