LaravelとMySQL5.7で多階層データ構造を扱う方法について紹介します。「Closure Table」を利用します。
目次
MySQL5.7で多階層データ構造を扱う方法
MySQL8.0で 再帰クエリ を利用できるようになりました。しかし、MySQL5.7以前では、再帰クエリ を利用できません。そこで、 Closure Table を利用して多階層データ構造を扱います。
Laravelでは、以下のライブラリを活用すると Closure Table を簡単に導入できます。
補足1. 階層的なデータ構造
SQLアンチパターン の「２章 ナイーブツリー(素朴な木)」で詳しく解説されており、参考になります。
補足2. MySQL8.0 再帰クエリ
あわせて読みたい
Recursive CTEで多階層カテゴリのレコードを取得
MySQL8.0からRecursive CTE(Common Table Expressions)を利用できるようになりました。ここでは、再帰クエリの動作確認をします。多階層カテゴリを持つデータ構造から、特定カテゴリの子孫カテゴリをまとめて取得します。
再帰クエリについては上記で取り上げています。
パッケージインストール
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 LaravelModelと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 CategoryClosureInterfaceModel(×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図で表すと以下のようになります。
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: [
"暮らし",
"料理",
"和食",
],
}その他
その他にも、ツリー状で取得するためのメソッドなど提供されています。詳しくはソースを確認してみてください。
