Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
0
Help us understand the problem. What is going on with this article?
@Ishidall

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

More than 1 year has passed since last update.

目的

  • ナイーブツリーで構成されている多階層テーブルにおいて
  • 任意の階層に所属しているノード群(= 親ノード)に所属している末端ノードの一覧を
  • 親ノードとの関連が分かるように
  • 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 -- 末端ノードであり、かつ親レコードではないことを条件とする
0
Help us understand the problem. What is going on with this article?
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
Ishidall
何でも屋さんになりつつある。サーバーサイドを中心から全体に染み出している。Node.jsとAngular1系、Vue.js、Express.jsに慣れている。CSS雰囲気で書くマン。ML/DL回りを勉強したくて転職したが、なぜかSwift書く必要がでてきた上に最終的にはTypeScriptを書いていたので辞めて起業しました。結局TS書いてる問題が常に勃発。

Comments

No comments
Sign up for free and join this conversation.
Sign Up
If you already have a Qiita account Login
0
Help us understand the problem. What is going on with this article?