【実装メモ】隣接リストモデルに基づくクエリサンプル

概要

特定の組織のすべての子孫を取得するクエリ:
隣接リストモデルでは、すべての子孫を取得するには再帰的なクエリやプログラムが必要です。SQL標準では再帰的なクエリをWITH RECURSIVEという構文で実行できますが、すべてのデータベースシステムがこれをサポートしているわけではありません。

特定の組織のすべての先祖を取得するクエリ:
こちらもWITH RECURSIVEを使用した再帰的なクエリが必要ですが、全てのデータベースで可能ではないため、アプリケーションコードで階層をたどることが一般的です。

隣接リストモデルは、単純な親子関係のクエリでは効果的ですが、複雑な階層クエリを実行するには再帰的な操作が必要であり、クエリが複雑かつ実行時間が長くなる可能性があります。閉包テーブルは、このような複雑な階層クエリを効率的に実行できるように設計されています。

実際のサンプル1

隣接リストモデルを用いた場合、特定の組織のすべての子孫や先祖を取得するためのSQLクエリは、データベースが再帰クエリをサポートしている場合には、再帰的共通テーブル式（Recursive Common Table Expressions: Recursive CTEs）を使用して実行できます。以下は、再帰クエリを使ったサンプルです。

特定の組織のすべての子孫を取得するクエリ:

WITH RECURSIVE Subordinates AS (
    SELECT org_id, org_name, parent_org_id
    FROM organizations
    WHERE org_id = 2 -- 例として財務部の子孫を取得
    UNION ALL
    SELECT o.org_id, o.org_name, o.parent_org_id
    FROM organizations o
    INNER JOIN Subordinates s ON o.parent_org_id = s.org_id
)
SELECT org_name FROM Subordinates WHERE org_id != 2;

特定の組織のすべての先祖を取得するクエリ:

WITH RECURSIVE Ancestors AS (
    SELECT org_id, org_name, parent_org_id
    FROM organizations
    WHERE org_id = 4 -- 例として経理の先祖を取得
    UNION ALL
    SELECT o.org_id, o.org_name, o.parent_org_id
    FROM organizations o
    INNER JOIN Ancestors a ON o.org_id = a.parent_org_id
)
SELECT org_name FROM Ancestors WHERE org_id != 4;

これらのクエリは、PostgreSQLやMySQL 8.0以降など、再帰クエリをサポートするデータベースシステムで動作します。WITH RECURSIVEは再帰的共通テーブル式を構築し、UNION ALLを使用して再帰的に自己を参照します。

実際のサンプル2

再帰的共通テーブル式（Recursive CTE）は、あるレコードに関連するレコードを階層的にクエリする際に使用されるSQLの強力な機能です。一般的な用途としては、階層データやグラフデータの探索に使われます。

再帰クエリは2部分から成り立っています：

アンカー部分：
再帰の基点となる初期クエリです。通常、階層の最上位レベルまたは特定の出発点を選択します。

再帰部分：
アンカー部分から得られた結果を使用して、自身を再帰的に参照するクエリです。

以下は、階層データを再帰的にクエリするためのシンプルな例です。
この例では、会社の組織構造をクエリするために使用されます。
会社には「CEO」がおり、その下に「部門マネージャー」がいて、さらに「チームリーダー」がいます。

WITH RECURSIVE EmployeeHierarchy AS (
    SELECT
        EmployeeID,
        EmployeeName,
        ManagerID,
        0 AS Depth -- アンカー部分での深さは0
    FROM
        Employees
    WHERE
        ManagerID IS NULL -- 最上位の従業員（例えばCEO）

    UNION ALL

    SELECT
        e.EmployeeID,
        e.EmployeeName,
        e.ManagerID,
        eh.Depth + 1 -- 再帰部分での深さは、一つ上のレベルの深さに1を加える
    FROM
        Employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT * FROM EmployeeHierarchy;

このクエリは、まずManagerIDがNULLである従業員（CEO）を選択します（アンカー部分）。その後、UNION ALLを使用して、Employeesテーブルに再帰的に参加し、各従業員のManagerIDが前の結果のEmployeeIDと一致するすべての従業員を選択します（再帰部分）。
Depthフィールドは階層の深さを表し、各再帰ステップで1ずつ増加します。

このクエリの結果は、企業の全従業員をCEOから最下位の従業員までの階層構造として返します。各従業員のレコードには、その従業員のID、名前、上司のID、およびその従業員が
階層内のどの深さにいるかが含まれます。