Closure Tableで階層の深さが動的なカテゴリ構造を扱う

LaravelとMySQL5.7で多階層データ構造を扱う方法について紹介します。「Closure Table」を利用します。

MySQL5.7で多階層データ構造を扱う方法

MySQL8.0で 再帰クエリ を利用できるようになりました。しかし、MySQL5.7以前では、再帰クエリ を利用できません。そこで、 Closure Table を利用して多階層データ構造を扱います。

Laravelでは、以下のライブラリを活用すると Closure Table を簡単に導入できます。

補足1. 階層的なデータ構造
SQLアンチパターン の「2章 ナイーブツリー(素朴な木)」で詳しく解説されており、参考になります。
補足2. MySQL8.0 再帰クエリ

パッケージインストール

Laravel5.7を利用しています。closure-table をインストールします。

$ composer require franzose/closure-table

インストールできました。

$ composer show -i | grep closure-table
franzose/closure-table                v5.1.1   Adjacency List’ed Closure Table database design pattern implementation for Laravel

ModelとMigrationを生成

コマンドで自動生成

ModelとMigrationを自動生成するコマンドが提供されています。

以下のようにオプションを指定して、実行しました。

$ php artisan closuretable:make --entity=category --models-path=./app/Models/Category --namespace=App\\Models\\Category
      create  2019_02_04_154049_create_categories_table_migration
      create  Category
      create  CategoryInterface
      create  CategoryClosure
      create  CategoryClosureInterface

Model(×2)Model用のInterface(×2)Migration のソースが生成されました。

$ ls -l app/Models/Category/
total 16
-rw-r--r-- 1 xxx xxx 167 Feb  4 06:40 CategoryClosureInterface.php
-rw-r--r-- 1 xxx xxx 298 Feb  4 06:40 CategoryClosure.php
-rw-r--r-- 1 xxx xxx 148 Feb  4 06:40 CategoryInterface.php
-rw-r--r-- 1 xxx xxx 418 Feb  4 06:40 Category.php

生成ファイルを調整

Migrationを調整

生成されたMigrationには、多階層データ構造を扱うためのカラムのみ設定されています。
適宜必要なカラムを追加します。今回は categoriesテーブルnameカラム を追加します。

Migrationファイルは以下のようになりました。

<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateCategoriesTableMigration extends Migration
{
    public function up()
    {
        Schema::create('categories', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name', 30);
            $table->integer('parent_id')->unsigned()->nullable();
            $table->integer('position', false, true);
            $table->integer('real_depth', false, true);
            $table->softDeletes();

            $table->foreign('parent_id')
                ->references('id')
                ->on('categories')
                ->onDelete('set null');
        });

        Schema::create('category_closure', function (Blueprint $table) {
            $table->increments('closure_id');

            $table->integer('ancestor', false, true);
            $table->integer('descendant', false, true);
            $table->integer('depth', false, true);

            $table->foreign('ancestor')
                ->references('id')
                ->on('categories')
                ->onDelete('cascade');

            $table->foreign('descendant')
                ->references('id')
                ->on('categories')
                ->onDelete('cascade');
        });
    }

    public function down()
    {
        Schema::table('category_closure', function (Blueprint $table) {
            Schema::dropIfExists('category_closure');
        });

        Schema::table('categories', function (Blueprint $table) {
            Schema::dropIfExists('categories');
        });
    }
}

CategoryClosure.phpを調整

$fillable のみ追記しました。

<?php
namespace App\Models\Category;

use Franzose\ClosureTable\Models\Entity;

class category extends Entity implements categoryInterface
{
    /**
     * The table associated with the model.
     *
     * @var string
     */
    protected $table = 'categories';

    protected $fillable = ['name'];
    
    /**
     * ClosureTable model instance.
     *
     * @var categoryClosure
     */
    protected $closure = 'App\Models\Category\categoryClosure';
}

Migration実行

Migrationを実行します。

$ php artisan migrate

作成されたテーブルを確認します。

mysql> SHOW COLUMNS FROM `categories`;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name       | varchar(30)      | NO   |     | NULL    |                |
| parent_id  | int(10) unsigned | YES  | MUL | NULL    |                |
| position   | int(10) unsigned | NO   |     | NULL    |                |
| real_depth | int(10) unsigned | NO   |     | NULL    |                |
| deleted_at | timestamp        | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

mysql> SHOW COLUMNS FROM `category_closure`;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| closure_id | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| ancestor   | int(10) unsigned | NO   | MUL | NULL    |                |
| descendant | int(10) unsigned | NO   | MUL | NULL    |                |
| depth      | int(10) unsigned | NO   |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

テーブル構造をER図で確認

作成したテーブルをER図で表すと以下のようになります。

599-laravel-closure-table_er.png

categories.parent_id
categories.id を参照します。

category_closure.ancestor
categories.id を参照します。

category_closure.descendant
categories.id を参照します。

データ登録

最終的に以下カテゴリ構造となるようにデータを挿入していきます。

暮らし
├── 料理
│   ├── 和食
│   │   ├── 肉じゃが
│   │   ├── 味噌汁
│   │   └── 天ぷら
│   ├── 中華
│   ├── イタリアン
│   └── フレンチ
├── ペット
└── ファッション

tinker上で作業します。子カテゴリを登録するには addChildメソッド を利用します。

# php artisan tinker
Psy Shell v0.9.9 (PHP 7.2.4-1+ubuntu16.04.1+deb.sury.org+1 — cli) by Justin Hileman
>>> 
>>> $rootCategory = new \App\Models\Category\Category(['name' => '暮らし']);
=> App\Models\Category\category {#2932
     name: "暮らし",
     real_depth: 0,
   }
>>> $rootCategory->save();
=> true
>>> 
>>> $cookCategory = new \App\Models\Category\Category(['name' => '料理']);
=> App\Models\Category\category {#2945
     name: "料理",
     real_depth: 0,
   }
>>> $rootCategory->addChild($cookCategory);
=> App\Models\Category\category {#2932
     name: "暮らし",
     real_depth: 0,
     position: 0,
     id: 1,
   }

この時点でDBの状態は以下のようになります。

mysql> SELECT * FROM `categories`;
+----+-----------+-----------+----------+------------+------------+
| id | name      | parent_id | position | real_depth | deleted_at |
+----+-----------+-----------+----------+------------+------------+
|  1 | 暮らし     |      NULL |        0 |          0 | NULL       |
|  2 | 料理      |         1 |        0 |          1 | NULL       |
+----+-----------+-----------+----------+------------+------------+
2 rows in set (0.00 sec)

mysql> 
mysql> SELECT * FROM `category_closure`;
+------------+----------+------------+-------+
| closure_id | ancestor | descendant | depth |
+------------+----------+------------+-------+
|          1 |        1 |          1 |     0 |
|          2 |        1 |          2 |     1 |
|          3 |        2 |          2 |     0 |
+------------+----------+------------+-------+
3 rows in set (0.00 sec)

作業を続けます。最終的に以下処理をtinker上で実行しました。

$rootCategory = new \App\Models\Category\Category(['name' => '暮らし']);
$rootCategory->save();

// 子
$cookCategory = new \App\Models\Category\Category(['name' => '料理']);
$petCategory = new \App\Models\Category\Category(['name' => 'ペット']);
$fashionCategory = new \App\Models\Category\Category(['name' => 'ファッション']);

$rootCategory->addChild($cookCategory);
$rootCategory->addChild($petCategory);
$rootCategory->addChild($fashionCategory);

// 孫
$japaneseFoodCategory = new \App\Models\Category\Category(['name' => '和食']);
$chinaCategory = new \App\Models\Category\Category(['name' => '中華']);
$italianCategory = new \App\Models\Category\Category(['name' => 'イタリアン']);
$frenchCategory = new \App\Models\Category\Category(['name' => 'フレンチ']);

$cookCategory->addChild($japaneseFoodCategory);
$cookCategory->addChild($chinaCategory);
$cookCategory->addChild($italianCategory);
$cookCategory->addChild($frenchCategory);

// ひ孫
$japaneseFoodCategory->addChild(new \App\Models\Category\Category(['name' => '肉じゃが']));
$japaneseFoodCategory->addChild(new \App\Models\Category\Category(['name' => '味噌汁']));
$japaneseFoodCategory->addChild(new \App\Models\Category\Category(['name' => '天ぷら']));

DB確認

categoriesテーブルcategory_closureテーブル には以下のようにデータが格納されました。

mysql> SELECT * FROM `categories`;
+----+--------------------+-----------+----------+------------+------------+
| id | name               | parent_id | position | real_depth | deleted_at |
+----+--------------------+-----------+----------+------------+------------+
|  1 | 暮らし              |      NULL |        0 |          0 | NULL       |
|  2 | 料理               |         1 |        0 |          1 | NULL       |
|  3 | ペット              |         1 |        1 |          1 | NULL       |
|  4 | ファッション          |         1 |        2 |          1 | NULL       |
|  5 | 和食               |         2 |        0 |          2 | NULL       |
|  6 | 中華               |         2 |        1 |          2 | NULL       |
|  7 | イタリアン           |         2 |        2 |          2 | NULL       |
|  8 | フレンチ            |         2 |        3 |          2 | NULL       |
|  9 | 肉じゃが            |         5 |        0 |          3 | NULL       |
| 10 | 味噌汁             |         5 |        1 |          3 | NULL       |
| 11 | 天ぷら             |         5 |        2 |          3 | NULL       |
+----+--------------------+-----------+----------+------------+------------+
11 rows in set (0.00 sec)
mysql> SELECT * FROM `category_closure`;
+------------+----------+------------+-------+
| closure_id | ancestor | descendant | depth |
+------------+----------+------------+-------+
|          1 |        1 |          1 |     0 |
|          2 |        1 |          2 |     1 |
|          3 |        2 |          2 |     0 |
|          5 |        1 |          3 |     1 |
|          6 |        3 |          3 |     0 |
|          8 |        1 |          4 |     1 |
|          9 |        4 |          4 |     0 |
|         11 |        1 |          5 |     2 |
|         12 |        2 |          5 |     1 |
|         13 |        5 |          5 |     0 |
|         14 |        1 |          6 |     2 |
|         15 |        2 |          6 |     1 |
|         16 |        6 |          6 |     0 |
|         17 |        1 |          7 |     2 |
|         18 |        2 |          7 |     1 |
|         19 |        7 |          7 |     0 |
|         20 |        1 |          8 |     2 |
|         21 |        2 |          8 |     1 |
|         22 |        8 |          8 |     0 |
|         23 |        1 |          9 |     3 |
|         24 |        2 |          9 |     2 |
|         25 |        5 |          9 |     1 |
|         26 |        9 |          9 |     0 |
|         30 |        1 |         10 |     3 |
|         31 |        2 |         10 |     2 |
|         32 |        5 |         10 |     1 |
|         33 |       10 |         10 |     0 |
|         37 |        1 |         11 |     3 |
|         38 |        2 |         11 |     2 |
|         39 |        5 |         11 |     1 |
|         40 |       11 |         11 |     0 |
+------------+----------+------------+-------+
31 rows in set (0.00 sec)

データ取得

再度カテゴリ構造を示します。

暮らし
├── 料理
│   ├── 和食
│   │   ├── 肉じゃが
│   │   ├── 味噌汁
│   │   └── 天ぷら
│   ├── 中華
│   ├── イタリアン
│   └── フレンチ
├── ペット
└── ファッション

子供を取得

子供を取得するには、 getChildrenメソッド を利用します。

>>> \App\Models\Category\Category::where('name', '料理')->first()->getChildren()->pluck('name')
=> Illuminate\Support\Collection {#2976
     all: [
       "和食",
       "中華",
       "イタリアン",
       "フレンチ",
     ],
   }

子供を取得する際には、以下クエリが実行されていました。

>>> $queries = \DB::getQueryLog();
=> [
     [
       "query" => "select * from `categories` where `name` = ? limit 1",
       "bindings" => [
         "料理",
       ],
       "time" => 1.98,
     ],
     [
       "query" => "select * from `categories` where `parent_id` = ? order by `position` asc",
       "bindings" => [
         2,
       ],
       "time" => 2.0,
     ],
   ]

子孫を取得

子孫を取得するには、 getDescendantsメソッド を利用します。

>>> \App\Models\Category\Category::where('name', '料理')->first()->getDescendants()->pluck('name')
=> Illuminate\Support\Collection {#2981
     all: [
       "和食",
       "中華",
       "イタリアン",
       "フレンチ",
       "肉じゃが",
       "味噌汁",
       "天ぷら",
     ],
   }

子孫を取得する際には、以下クエリが実行されていました。

>>> $queries = \DB::getQueryLog();
=> [
     [
       "query" => "select * from `categories` where `name` = ? limit 1",
       "bindings" => [
         "料理",
       ],
       "time" => 0.88,
     ],
     [
       "query" => "select * from `categories` inner join `category_closure` on `category_closure`.`descendant` = `categories`.`id` where `category_closure`.`ancestor` = ? and `category_closure`.`depth` > ?",
       "bindings" => [
         2,
         0,
       ],
       "time" => 1.16,
     ],
   ]

親を取得

親を取得するには、 getParentメソッド を利用します。

>>> \App\Models\Category\Category::where('name', '肉じゃが')->first()->getParent()->name
=> "和食"

祖先を取得

祖先を取得するには、 getAncestorsメソッド を利用します。

>>> \App\Models\Category\Category::where('name', '肉じゃが')->first()->getAncestors()->pluck('name')
=> Illuminate\Support\Collection {#2956
     all: [
       "暮らし",
       "料理",
       "和食",
     ],
   }

その他

その他にも、ツリー状で取得するためのメソッドなど提供されています。詳しくはソースを確認してみてください。

参考

https://github.com/franzose/ClosureTable

わくわくBank.
フリーランスのエンジニアとして活動してます。ここでは、ソフトウェア開発で必要とされる技術、用語、概念を整理しています。