閉包テーブル(ClosureTable)を用いて階層構造をRDBで表現する

閉包テーブル(ClosureTable)とは

RDBで階層構造を表現する際に用いられるデータモデルです。
フォルダやグループ等で要件として階層が必要となる場合に有用です。

テーブル定義

フォルダ構成を実装する想定でテーブル定義を作成します。
使用しているRDBはMySQL 5.7.18です。

CREATE TABLE folder (
  id INT(11) NOT NULL auto_increment,
  name VARCHAR(255) NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE folder_path (
  id INT(11) NOT NULL auto_increment,
  ancestor_id INT(11) NOT NULL,
  descendant_id INT(11) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY u_idx_ancestor_id_descendant_id(ancestor_id, descendant_id)
);

ルートフォルダを追加する

INSERT INTO folder (name)
VALUES ('親フォルダ');

INSERT INTO folder_path (ancestor_id, descendant_id)
VALUES (LAST_INSERT_ID(), LAST_INSERT_ID());

子フォルダを追加する

SET @parent_id=1;

INSERT INTO folder (name)
VALUES ('子フォルダ');

INSERT INTO folder_path (ancestor_id, descendant_id)
SELECT ancestor_id, LAST_INSERT_ID()
FROM folder_path
WHERE descendant_id = @parent_id
UNION ALL SELECT LAST_INSERT_ID(), LAST_INSERT_ID();

フォルダの深さを取得する

SET @folder_id=1;

SELECT
    DISTINCT folder.id
  , folder.name
  , COUNT(folder.id) as depth
FROM folder
INNER JOIN folder_path
ON folder.id = folder_path.ancestor_id
LEFT OUTER JOIN folder_path AS fp
ON fp.descendant_id = folder_path.ancestor_id
WHERE folder.id = @folder_id
GROUP BY folder_path.id;

フォルダのパスを取得する

SET @folder_id=2;

SELECT
    folder_path.descendant_id AS id
  , CONCAT("/", GROUP_CONCAT(folder_path.name ORDER BY folder_path.depth ASC SEPARATOR "/")) AS path
FROM (
    SELECT
        folder_path.ancestor_id
      , folder_path.descendant_id
      , folder_path.depth
      , folder.name
    FROM (
        SELECT
            folder_path.id
          , folder_path.ancestor_id
          , folder_path.descendant_id
          , COUNT(folder_path.id) as depth
        FROM folder_path
        LEFT OUTER JOIN folder_path AS fp
        ON fp.descendant_id = folder_path.ancestor_id
        WHERE folder_path.descendant_id = @folder_id
        GROUP BY folder_path.id
    ) folder_path
    INNER JOIN folder
    ON folder.id = folder_path.ancestor_id
) AS folder_path
GROUP BY folder_path.descendant_id;