Help us understand the problem. What is going on with this article?

ナイーブツリーにおける任意階層にある複数ノード配下の末端ノードを1クエリで全て取得する

目的

  • ナイーブツリーで構成されている多階層テーブルにおいて
  • 任意の階層に所属しているノード群(= 親ノード)に所属している末端ノードの一覧を
  • 親ノードとの関連が分かるように
  • 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 -- 末端ノードであり、かつ親レコードではないことを条件とする
Ishidall
何でも屋さんになりつつある。サーバーサイドを中心から全体に染み出している。Node.jsとAngular1系、Vue.js、Express.jsに慣れている。CSS雰囲気で書くマン。ML/DL回りを勉強したくて転職したが、なぜかSwift書く必要がでてきた上に最終的にはTypeScriptを書いていたので辞めて起業しました。結局TS書いてる問題が常に勃発。
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした