MySQLでJSONを操作する方法

投稿日: 2022-04-20 2022-04-20
タグ: mysql

序章

MySQLバージョン5.7.8では、JSONドキュメントのデータにアクセスできるJSONデータ型が導入されています。

SQLデータベースは、設計が厳格になる傾向があります。その性質上、構造化照会言語はデータ型とサイズの制約を適用します。

それに比べて、NoSQLデータベースは設計の柔軟性を促進します。これらのスキーマのないデータベースでは、構造上の制限はなく、データのみが保存されます。

MySQLのJSONデータ型は、これら両方のシステムの長所を提供します。これにより、データベースの一部を構造化し、他の部分を柔軟に保つことができます。

この記事の前半では、JSONフィールドを使用してデータベースを設計します。 MySQLで使用可能な組み込み関数を使用して、行を作成、読み取り、更新、および削除する手順を説明します。

この記事の後半では、EloquentORMとLaravelを使用してデータベースと通信します。製品の表示、新しい製品の追加、既存の製品の変更、および製品の削除をサポートする管理パネルを作成します。

前提条件

この記事をフォローしたい場合は、次のものが必要になります。

MySQL5.7.8以降およびPHP7.3.24以降。 Linux、Apache、MySQL、およびPHPのインストールに関するチュートリアルを参照できます
SQLクエリにある程度精通している。
PHPの記述にある程度精通している。
Laravelにある程度精通している。
このチュートリアルでは、Composerを介したLaravelのインストールを念頭に置いています。 Composerのインストールに関するチュートリアルを参照できます。

注： Laravelは、MySQL、PHP、およびComposerで環境を構成するDockerと連携するSailと呼ばれるツールを提供するようになりました。

ローカル環境のセットアップに問題がある場合、これは代替オプションになる可能性があります。

このチュートリアルは、MySQL v8.0.23、PHP v7.3.24、Composer v2.0.9、およびLaravelv8.26.1で検証されました。

ステップ1—スキーマを定義する

このチュートリアルでは、さまざまな電子機器を販売するオンラインストアの在庫を定義するスキーマから構築します。

従来、エンティティ-属性-値モデル（EAV）パターンは、顧客が製品の機能を比較できるようにするために使用されていました。

ただし、JSONデータ型を使用すると、このユースケースへのアプローチが異なる場合があります。

データベースにはe_storeという名前が付けられ、それぞれbrands、categories、およびproductsという名前の3つのテーブルがあります。

e_storeデータベースを作成します。

CREATE DATABASE IF NOT EXISTS `e_store`
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_general_ci;

SET default_storage_engine = INNODB;

brandsテーブルとcategoriesテーブルには、それぞれidフィールドとnameフィールドがあります。

brandsテーブルを作成します。

CREATE TABLE `e_store`.`brands`(
	`id` INT UNSIGNED NOT NULL auto_increment ,
	`name` VARCHAR(250) NOT NULL ,
	PRIMARY KEY(`id`)
);

categoriesテーブルを作成します。

CREATE TABLE `e_store`.`categories`(
	`id` INT UNSIGNED NOT NULL auto_increment ,
	`name` VARCHAR(250) NOT NULL ,
	PRIMARY KEY(`id`)
);

次に、サンプルbrandsを追加します。

INSERT INTO `e_store`.`brands`(`name`)
VALUES
	('Samsung');

INSERT INTO `e_store`.`brands`(`name`)
VALUES
	('Nokia');

INSERT INTO `e_store`.`brands`(`name`)
VALUES
	('Canon');

次に、categoriesを追加します。

INSERT INTO `e_store`.`categories`(`name`)
VALUES
	('Television');

INSERT INTO `e_store`.`categories`(`name`)
VALUES
	('Mobile Phone');

INSERT INTO `e_store`.`categories`(`name`)
VALUES
	('Camera');

次に、id、name、brand_id、category_id、およびattributesフィールドを使用してproductsテーブルを作成します。

CREATE TABLE `e_store`.`products`(
	`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
	`name` VARCHAR(250) NOT NULL ,
	`brand_id` INT UNSIGNED NOT NULL ,
	`category_id` INT UNSIGNED NOT NULL ,
	`attributes` JSON NOT NULL ,
	PRIMARY KEY(`id`) ,
	INDEX `CATEGORY_ID`(`category_id` ASC) ,
	INDEX `BRAND_ID`(`brand_id` ASC) ,
	CONSTRAINT `brand_id` FOREIGN KEY(`brand_id`) REFERENCES `e_store`.`brands`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE ,
	CONSTRAINT `category_id` FOREIGN KEY(`category_id`) REFERENCES `e_store`.`categories`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE
);

このテーブル定義は、brand_idおよびcategory_idフィールドの外部キー制約を指定し、それぞれbrandsおよびcategoriesテーブルを参照することを指定します。このテーブル定義は、参照された行を削除できないように指定し、更新された場合、変更は参照にも反映される必要があります。

attributesフィールドの列タイプは、MySQLで現在利用可能なネイティブデータタイプであるJSONであると宣言されています。これにより、MySQLのattributesフィールドでさまざまなJSON関連の構成を使用できます。

作成したデータベースの実体関連図は次のとおりです。

An entity relationship diagram of the e_store database

このデータベース設計は、効率と精度の点で最高ではありません。考慮されていない一般的な実際のユースケースがいくつかあります。たとえば、productsテーブルには価格列がなく、複数のカテゴリに属する製品はサポートされていません。ただし、このチュートリアルの目的は、データベースの設計を教えることではなく、MySQLのJSON機能を使用して単一のテーブルでさまざまな性質のオブジェクトをモデル化する方法を教えることです。

ステップ2—JSONフィールドにデータを作成する

次に、INSERT INTOとVALUESを使用してデータベースに追加する製品を作成します。

次に、文字列化されたJSONオブジェクトを使用した、画面サイズ、解像度、ポート、スピーカーに関するデータを備えたテレビの例をいくつか示します。

INSERT INTO `e_store`.`products`(
	`name` ,
	`brand_id` ,
	`category_id` ,
	`attributes`
)
VALUES(
	'Prime' ,
	'1' ,
	'1' ,
	'{"screen": "50 inch", "resolution": "2048 x 1152 pixels", "ports": {"hdmi": 1, "usb": 3}, "speakers": {"left": "10 watt", "right": "10 watt"}}'
);

INSERT INTO `e_store`.`products`(
	`name` ,
	`brand_id` ,
	`category_id` ,
	`attributes`
)
VALUES(
	'Octoview' ,
	'1' ,
	'1' ,
	'{"screen": "40 inch", "resolution": "1920 x 1080 pixels", "ports": {"hdmi": 1, "usb": 2}, "speakers": {"left": "10 watt", "right": "10 watt"}}'
);

INSERT INTO `e_store`.`products`(
	`name` ,
	`brand_id` ,
	`category_id` ,
	`attributes`
)
VALUES(
	'Dreamer' ,
	'1' ,
	'1' ,
	'{"screen": "30 inch", "resolution": "1600 x 900 pixles", "ports": {"hdmi": 1, "usb": 1}, "speakers": {"left": "10 watt", "right": "10 watt"}}'
);

INSERT INTO `e_store`.`products`(
	`name` ,
	`brand_id` ,
	`category_id` ,
	`attributes`
)
VALUES(
	'Bravia' ,
	'1' ,
	'1' ,
	'{"screen": "25 inch", "resolution": "1366 x 768 pixels", "ports": {"hdmi": 1, "usb": 0}, "speakers": {"left": "5 watt", "right": "5 watt"}}'
);

INSERT INTO `e_store`.`products`(
	`name` ,
	`brand_id` ,
	`category_id` ,
	`attributes`
)
VALUES(
	'Proton' ,
	'1' ,
	'1' ,
	'{"screen": "20 inch", "resolution": "1280 x 720 pixels", "ports": {"hdmi": 0, "usb": 0}, "speakers": {"left": "5 watt", "right": "5 watt"}}'
);

この例では、5つの異なるテレビ製品を宣言しています。

または、組み込みのJSON_OBJECT関数を使用してJSONオブジェクトを作成することもできます。

JSON_OBJECT関数は、JSON_OBJECT(key1, value1, key2, value2, ... key(n), value(n))の形式でキーと値のペアのリストを受け入れ、JSONオブジェクトを返します。

JSON_OBJECT機能を使用した携帯電話の例を次に示します。

INSERT INTO `e_store`.`products`(
	`name` ,
	`brand_id` ,
	`category_id` ,
	`attributes`
)
VALUES(
	'Desire' ,
	'2' ,
	'2' ,
	JSON_OBJECT(
		"network" ,
		JSON_ARRAY("GSM" , "CDMA" , "HSPA" , "EVDO") ,
		"body" ,
		"5.11 x 2.59 x 0.46 inches" ,
		"weight" ,
		"143 grams" ,
		"sim" ,
		"Micro-SIM" ,
		"display" ,
		"4.5 inches" ,
		"resolution" ,
		"720 x 1280 pixels" ,
		"os" ,
		"Android Jellybean v4.3"
	)
);

INSERT INTO `e_store`.`products`(
	`name` ,
	`brand_id` ,
	`category_id` ,
	`attributes`
)
VALUES(
	'Passion' ,
	'2' ,
	'2' ,
	JSON_OBJECT(
		"network" ,
		JSON_ARRAY("GSM" , "CDMA" , "HSPA") ,
		"body" ,
		"6.11 x 3.59 x 0.46 inches" ,
		"weight" ,
		"145 grams" ,
		"sim" ,
		"Micro-SIM" ,
		"display" ,
		"4.5 inches" ,
		"resolution" ,
		"720 x 1280 pixels" ,
		"os" ,
		"Android Jellybean v4.3"
	)
);

INSERT INTO `e_store`.`products`(
	`name` ,
	`brand_id` ,
	`category_id` ,
	`attributes`
)
VALUES(
	'Emotion' ,
	'2' ,
	'2' ,
	JSON_OBJECT(
		"network" ,
		JSON_ARRAY("GSM" , "CDMA" , "EVDO") ,
		"body" ,
		"5.50 x 2.50 x 0.50 inches" ,
		"weight" ,
		"125 grams" ,
		"sim" ,
		"Micro-SIM" ,
		"display" ,
		"5.00 inches" ,
		"resolution" ,
		"720 x 1280 pixels" ,
		"os" ,
		"Android KitKat v4.3"
	)
);

INSERT INTO `e_store`.`products`(
	`name` ,
	`brand_id` ,
	`category_id` ,
	`attributes`
)
VALUES(
	'Sensation' ,
	'2' ,
	'2' ,
	JSON_OBJECT(
		"network" ,
		JSON_ARRAY("GSM" , "HSPA" , "EVDO") ,
		"body" ,
		"4.00 x 2.00 x 0.75 inches" ,
		"weight" ,
		"150 grams" ,
		"sim" ,
		"Micro-SIM" ,
		"display" ,
		"3.5 inches" ,
		"resolution" ,
		"720 x 1280 pixels" ,
		"os" ,
		"Android Lollipop v4.3"
	)
);

INSERT INTO `e_store`.`products`(
	`name` ,
	`brand_id` ,
	`category_id` ,
	`attributes`
)
VALUES(
	'Joy' ,
	'2' ,
	'2' ,
	JSON_OBJECT(
		"network" ,
		JSON_ARRAY("CDMA" , "HSPA" , "EVDO") ,
		"body" ,
		"7.00 x 3.50 x 0.25 inches" ,
		"weight" ,
		"250 grams" ,
		"sim" ,
		"Micro-SIM" ,
		"display" ,
		"6.5 inches" ,
		"resolution" ,
		"1920 x 1080 pixels" ,
		"os" ,
		"Android Marshmallow v4.3"
	)
);

この例では、5つの異なる携帯電話製品を宣言しています。

また、値のセットが渡されたときにJSON配列を返すJSON_ARRAY関数も利用しました。

1つのキーを複数回指定すると、最初のキーと値のペアのみが保持されます。これは、MySQLの用語でJSONの正規化と呼ばれます。また、正規化の一環として、オブジェクトキーが並べ替えられ、キーと値のペア間の余分な空白が削除されます。

さらに、組み込みのJSON_MERGE_PRESERVEまたはJSON_MERGE_PATCH関数を使用してJSONオブジェクトを作成できます。

注：以前のバージョンのMySQLでは、JSON_MERGEを使用できましたが、この関数は非推奨になりました。

'JSON_MERGE' is deprecated and will be removed in a future release. Please use JSON_MERGE_PRESERVE/JSON_MERGE_PATCH instead

このチュートリアルでは、JSON_MERGE_PRESERVE関数を使用します。この関数は複数のJSONオブジェクトを受け取り、単一の集約オブジェクトを生成します。

JSON_MERGE_PRESERVE機能を使用したカメラの例を次に示します。

INSERT INTO `e_store`.`products`(
	`name` ,
	`brand_id` ,
	`category_id` ,
	`attributes`
)
VALUES(
	'Explorer' ,
	'3' ,
	'3' ,
	JSON_MERGE_PRESERVE(
		'{"sensor_type": "CMOS"}' ,
		'{"processor": "Digic DV III"}' ,
		'{"scanning_system": "progressive"}' ,
		'{"mount_type": "PL"}' ,
		'{"monitor_type": "LCD"}'
	)
);

INSERT INTO `e_store`.`products`(
	`name` ,
	`brand_id` ,
	`category_id` ,
	`attributes`
)
VALUES(
	'Runner' ,
	'3' ,
	'3' ,
	JSON_MERGE_PRESERVE(
		JSON_OBJECT("sensor_type" , "CMOS") ,
		JSON_OBJECT("processor" , "Digic DV II") ,
		JSON_OBJECT("scanning_system" , "progressive") ,
		JSON_OBJECT("mount_type" , "PL") ,
		JSON_OBJECT("monitor_type" , "LED")
	)
);

INSERT INTO `e_store`.`products`(
	`name` ,
	`brand_id` ,
	`category_id` ,
	`attributes`
)
VALUES(
	'Traveler' ,
	'3' ,
	'3' ,
	JSON_MERGE_PRESERVE(
		JSON_OBJECT("sensor_type" , "CMOS") ,
		'{"processor": "Digic DV II"}' ,
		'{"scanning_system": "progressive"}' ,
		'{"mount_type": "PL"}' ,
		'{"monitor_type": "LCD"}'
	)
);

INSERT INTO `e_store`.`products`(
	`name` ,
	`brand_id` ,
	`category_id` ,
	`attributes`
)
VALUES(
	'Walker' ,
	'3' ,
	'3' ,
	JSON_MERGE_PRESERVE(
		'{"sensor_type": "CMOS"}' ,
		'{"processor": "Digic DV I"}' ,
		'{"scanning_system": "progressive"}' ,
		'{"mount_type": "PL"}' ,
		'{"monitor_type": "LED"}'
	)
);

INSERT INTO `e_store`.`products`(
	`name` ,
	`brand_id` ,
	`category_id` ,
	`attributes`
)
VALUES(
	'Jumper' ,
	'3' ,
	'3' ,
	JSON_MERGE_PRESERVE(
		'{"sensor_type": "CMOS"}' ,
		'{"processor": "Digic DV I"}' ,
		'{"scanning_system": "progressive"}' ,
		'{"mount_type": "PL"}' ,
		'{"monitor_type": "LCD"}'
	)
);

この例では、5つの異なるカメラ製品を宣言しています。

オブジェクトのみがJSON_MERGE_PRESERVE関数に渡されることに注意してください。それらのいくつかは、JSON_OBJECT関数を使用して構築されています。その他は有効なJSON文字列として渡されています。

JSON_MERGE_PRESERVE関数の場合、キーが複数回繰り返されると、その値は出力に配列として保持されます。

たとえば、同じnetworkキーを持つJSONオブジェクトのコレクションを次に示します。

SELECT JSON_MERGE_PRESERVE(
	'{"network": "GSM"}' ,
	'{"network": "CDMA"}' ,
	'{"network": "HSPA"}' ,
	'{"network": "EVDO"}'
);

これにより、値の配列が生成されます。

Output{"network": ["GSM", "CDMA", "HSPA", "EVDO"]}

これで、この時点で、JSON_TYPE関数を使用してフィールド値のタイプを表示することにより、クエリを検証できます。

SELECT JSON_TYPE(attributes) FROM `e_store`.`products`;

このクエリは、すべての製品（5台のテレビ、5台の携帯電話、5台のカメラ）を表す15個のOBJECT結果を生成します。

これで、JSONフィールドにデータを作成できます。

ステップ3—JSONフィールドからのデータの読み取り

データベースに使用する製品がいくつかあるので、データの読み取りを試すことができます。

JSONタイプではない一般的なMySQL値の場合、通常はWHERE句に依存します。ヒューリスティックに、JSON列を操作する場合、これは機能しません。

JSONフィールドを使用して行を選択する場合は、パス式の概念に精通している必要があります。パス式は、ドル記号（$）とターゲットオブジェクトキーを使用します。

JSON_EXTRACT関数と組み合わせて使用すると、指定した列の値を取得できます。

少なくとも1つのUSBポートと1つのHDMIポートを備えたすべてのテレビに関心があるシナリオを考えてみましょう。

SELECT
	*
FROM
	`e_store`.`products`
WHERE
	`category_id` = 1
AND JSON_EXTRACT(`attributes` , '$.ports.usb') > 0
AND JSON_EXTRACT(`attributes` , '$.ports.hdmi') > 0;

JSON_EXTRACT関数の最初の引数は、attributes列であるパス式を適用するJSONです。 $シンボルは、操作するオブジェクトをトークン化します。 $.ports.usbおよび$.ports.hdmiパス式は、それぞれ「ポートの下でusbキーを取得する」および「ポートの下でhdmiキーを取得する」に変換されます。

関心のあるキーを抽出したら、大なり記号（>）などのMySQL演算子を使用できます。

このクエリは3つの結果を生成します。

Screenshot of query results displaying the rows for Prime, Octoview, and Dreamer models of televisions

これらの3つのテレビには、少なくとも1つのUSBポートと1つのHDMIポートがあります。「ブラビア」モデルと「プロトン」モデルは、これらの条件を満たしていません。

または、JSON_EXTRACT関数には、クエリを読みやすくするために使用できるエイリアス->があります。

->エイリアスを使用するように、前のクエリを修正します。

SELECT
	*
FROM
	`e_store`.`products`
WHERE
	`category_id` = 1
AND `attributes` -> '$.ports.usb' > 0
AND `attributes` -> '$.ports.hdmi' > 0;

これで、JSONフィールドからデータを読み取ることができます。

ステップ4—JSONフィールドのデータを更新する

JSON_INSERT、JSON_REPLACE、およびJSON_SET関数を使用して、JSONフィールドのデータを更新できます。これらの関数には、JSONオブジェクトのどの部分を変更するかを指定するためのパス式も必要です。これらの関数の出力は、変更が適用された有効なJSONオブジェクトです。

まず、JSONフィールドをJSON_INSERTで更新して、すべての携帯電話に値「Qualcomm」の新しいchipsetキーを追加します。

UPDATE `e_store`.`products`
SET `attributes` = JSON_INSERT(
	`attributes` ,
	'$.chipset' ,
	'Qualcomm'
)
WHERE
	`category_id` = 2;

$.chipsetパス式は、オブジェクトのルートにあるchipsetプロパティの位置を識別します。

次のクエリを使用して、更新された携帯電話のカテゴリを調べます。

SELECT
	*
FROM
	`e_store`.`products`
WHERE
	`category_id` = 2

「Qualcomm」は現在、すべての携帯電話に搭載されています。

Screenshot of query results displaying the rows for models of mobile phones with the chipset of Qualcomm now added.

次に、JSONフィールドをJSON_REPLACEで更新して、すべての携帯電話の既存のchipsetキーを値「QualcommSnapsdragon」に変更します。

UPDATE `e_store`.`products`
SET `attributes` = JSON_REPLACE(
	`attributes` ,
	'$.chipset' ,
	'Qualcomm Snapdragon'
)
WHERE
	`category_id` = 2;

「Qualcomm」は、すべての携帯電話で「QualcommSnapdragon」に置き換えられました。

Screenshot of query results displaying the rows for models of mobile phones with the chipset of Qualcomm now changed to Qualcomm Snapdragon.

最後に、JSONフィールドをJSON_SETで更新して、すべてのテレビに値「red」の新しいbody_colorキーを追加します。

UPDATE `e_store`.`products`
SET `attributes` = JSON_SET(
	`attributes` ,
	'$.body_color' ,
	'red'
)
WHERE
	`category_id` = 1;

「赤」の色がすべてのテレビに適用されるようになりました。

Screenshot of query results displaying the rows for models of televisions with the color of red now added.

これらの機能はすべて同じように見えますが、動作に違いがあります。

JSON_INSERT関数は、プロパティがまだ存在しない場合にのみ、オブジェクトにプロパティを追加します。

JSON_REPLACE関数は、プロパティが見つかった場合にのみプロパティを置き換えます。

JSON_SET関数は、プロパティが見つからない場合はプロパティを追加し、見つからない場合はプロパティを置き換えます。

これで、JSONフィールドからデータを更新できます。

ステップ5—JSONフィールドからのデータの削除

JSON_REMOVE関数とDELETEを使用して、JSONフィールドのデータを削除できます。

JSON_REMOVEを使用すると、JSON列から特定のキー/値を削除できます。

JSON_REMOVE機能を使用すると、すべてのカメラからmount_typeキー/値ペアを削除できます。

UPDATE `e_store`.`products`
SET `attributes` = JSON_REMOVE(`attributes` , '$.mount_type')
WHERE
	`category_id` = 3;

JSON_REMOVE関数は、パス式に基づいて指定されたキーを削除した後、更新されたJSONを返します。

または、JSON列を使用して行全体をDELETEすることもできます。

DELETEとJSON_EXTRACTとLIKEを使用すると、Androidオペレーティングシステムの「Jellybean」バージョンを搭載したすべての携帯電話を削除できます。

DELETE FROM `e_store`.`products`
WHERE `category_id` = 2
AND JSON_EXTRACT(`attributes` , '$.os') LIKE '%Jellybean%';

このクエリは、携帯電話の「欲望」モデルと「情熱」モデルを削除します。

特定の属性を操作するには、JSON_EXTRACT関数を使用する必要があります。まず、携帯電話のosプロパティを抽出します。次に、LIKE演算子が、文字列Jellybeanを含むすべてのレコードDELETEに適用されます。

これで、JSONフィールドからデータを削除できます。

ステップ6—移行の作成

次に、新しいLaravelプロジェクトを作成します。

警告：このWebアプリケーションはチュートリアルのみを目的としており、実稼働環境では使用しないでください。

ターミナルウィンドウを開き、次のコマンドを実行します。

composer create-project laravel/laravel estore-example

新しく作成されたプロジェクトディレクトリに移動します。

cd estore-example

MySQLデータベースを使用するようにLaravelアプリケーションを構成します。

.envファイルを変更して、DB_DATABASE、DB_USERNAME、およびDB_PASSWORDを設定する必要がある場合があります。

brands、categories、およびproductsに対してそれぞれ3つの移行を作成します。

create_brands移行を行います：

php artisan make:migration create_brands

次のコード行を使用して、create_brands.php移行を変更します。

database / migrations /（…）create_brands.php

<?php

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

class CreateBrands extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('brands', function(Blueprint $table){
            $table->engine = 'InnoDB';
            $table->increments('id');
            $table->string('name');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('brands');
    }
}

create_categories移行を行います：

php artisan make:migration create_categories

次のコード行を使用して、create_categories.php移行を変更します。

database / migrations /（…）create_categories.php

<?php

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

class CreateCategories extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('categories', function(Blueprint $table){
            $table->engine = 'InnoDB';
            $table->increments('id');
            $table->string('name');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('categories');
    }
}

create_products移行には、インデックスと外部キーのディレクティブも含まれます。

php artisan make:migration create_products

次のコード行を使用して、create_products.php移行を変更します。

database / migrations /（…）create_products.php

<?php

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

class CreateProducts extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('products', function(Blueprint $table){
            $table->engine = 'InnoDB';
            $table->increments('id');
            $table->string('name');
            $table->unsignedInteger('brand_id');
            $table->unsignedInteger('category_id');
            $table->json('attributes');
            $table->timestamps();
            // foreign key constraints
            $table->foreign('brand_id')->references('id')->on('brands')->onDelete('restrict')->onUpdate('cascade');
            $table->foreign('category_id')->references('id')->on('categories')->onDelete('restrict')->onUpdate('cascade');
            // indexes
            $table->index('brand_id');
            $table->index('category_id');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('products');
    }
}

移行中の$table->json('attributes');ステートメントに注意してください。

注：これは、JSONデータ型をサポートするデータベースエンジンでのみ機能します。

古いバージョンのMySQLなどのエンジンは、これらの移行を実行できません。

メソッドという名前の適切なデータ型を使用して他のタイプのテーブルフィールドを作成するのと同様に、attributesという名前のjsonメソッドを使用してJSON列を作成しました。

ステップ7—モデルの作成

brands、categories、productsの3つのモデルをそれぞれ作成します。

Brandモデルを作成します。

php artisan make:model Brand

Brand.phpファイルを次のコード行で変更します。

app / Models / Brand.php

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Brand extends Model
{
    use HasFactory;

    // A brand has many products
    public function products(){
        return $this->hasMany('Product')
    }
}

Categoryモデルを作成します。

php artisan make:model Category

Category.phpファイルを次のコード行で変更します。

app / Models / Category.php

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Category extends Model
{
    // A category has many products
    public function products(){
        return $this->hasMany('Product')
    }
}

Productモデルを作成します。

php artisan make:model Product

Product.phpファイルを次のコード行で変更します。

app / Models / Product.php

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Product extends Model
{
    use HasFactory;

    public $timestamps = false;

    // Cast attributes JSON to array
    protected $casts = [
        'attributes' => 'array'
    ];

    // Each product has a brand
    public function brand(){
        return $this->belongsTo('Brand');
    }

    // Each product has a category
    public function category(){
        return $this->belongsTo('Category');
    }
}

キーattributesがarrayに設定されている$casts配列は、製品がデータベースからフェッチされるたびに、そのattributesJSONが連想配列に変換されることを確認します配列。これにより、コントローラーアクションからレコードを更新できます。

ステップ8—製品の作成

このチュートリアルの残りの部分では、カメラ製品のカテゴリに焦点を当てます。

カメラに固有のフィールドを持つフォームを使用してビューを作成します。簡潔にするために、テレビと携帯電話の製品カテゴリはカバーされませんが、デザインは非常に似ています。

カメラ製品カテゴリのコントローラを作成します。

php artisan make:controller CameraController

次のコード行でCameraController.phpを変更します。

app / Http / Controller / CameraController.php

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;

class CameraController extends Controller
{
    // creates product in database
    // using form fields
    public function store(Request $request){
        // create object and set properties
        $camera = new \App\Models\Product();
        $camera->name = $request->name;
        $camera->brand_id = $request->brand_id;
        $camera->category_id = $request->category_id;
        $camera->attributes = [
            'processor' => $request->processor,
            'sensor_type' => $request->sensor_type,
            'monitor_type' => $request->monitor_type,
            'scanning_system' => $request->scanning_system,
        ];
        // save to database
        $camera->save();
        // show the created camera
        return view('product.camera.show', ['camera' => $camera]);
    }
}

これで、カメラのstore機能は完了です。

resources/views/product/cameraディレクトリツリーにnew.blade.phpファイルを作成して、ビューを作成します。

resources / views / product / camera / new.blade.php

<form method="POST" action="/product/camera/store">
    @csrf
    <table>
        <tr>
            <td><label for="name">Name</label></td>
            <td><input id="name" name="name" type="text"></td>
        </tr>
        <tr>
            <td><label for="brand-id">Brand ID</label></td>
            <td>
                <select id="brand-id" name="brand_id">
                    <option value="1">Samsung</option>
                    <option value="2">Nokia</option>
                    <option value="3">Canon</option>
                </select>
            </td>
        </tr>
        <tr>
            <td><label for="attributes-processor">Processor</label></td>
            <td><input id="attributes-processor" name="processor" type="text"></td>
        </tr>
        <tr>
            <td><label for="attributes-sensor-type">Sensor Type</label></td>
            <td><input id="attributes-sensor-type" name="sensor_type" type="text"></td>
        </tr>
        <tr>
            <td><label for="attributes-monitor-type">Monitor Type</label></td>
            <td><input id="attributes-monitor-type" name="monitor_type" type="text"></td>
        </tr>
        <tr>
            <td><label for="attributes-scanning-system">Scanning System</label></td>
            <td><input id="attributes-scanning-system" name="scanning_system" type="text"></td>
        </tr>
    </table>
    <input name="category_id" value="3" type="hidden">
    <button type="submit">Submit</button>
</form>

brand_idは、ハードコードされたselect要素として表示され、以前にoptionとして作成された3つのブランドが含まれています。 category_idは、カメラのidに設定されたハードコードされた非表示の入力値として表示されます。

routes/web.phpのルートを変更して、カメラを表示します。

ルート/web.php

// ...

use App\Http\Controllers\CameraController;

Route::get('/product/camera/new', function() {
    return view('product/camera/new');
});

Route::post(
    '/product/camera/store',
    [CameraController::class, 'store']
);

次のコマンドを使用してアプリケーションを提供します。

php artisan serve

次に、Webブラウザでlocalhost:8000/product/camera/new）にアクセスします。新しいカメラを追加するためのフォームが表示されます。

ステップ9—製品の取得

Productモデルで以前に宣言された$casts配列は、属性を連想配列として扱うことにより、製品の読み取りと編集に役立ちます。

次のコード行でCamerasControllerを変更します。

app / Http / Controller / CameraController.php

<?php

// ...

class CameraController extends Controller
{
    // ... store ...

    // fetches a single product
    // from database
    public function show($id){
        $camera = \App\Models\Product::find($id);
        return view('product.camera.show', ['camera' => $camera]);
    }
}

これで、カメラのshow機能は完了です。

resources/views/product/cameraディレクトリツリーにshow.blade.phpファイルを作成して、ビューを作成します。

resources / views / product / camera / show.blade.php

<table>
    <tr>
        <td>Name</td>
        <td>{{ $camera->name }}</td>
    </tr>
    <tr>
        <td>Brand ID</td>
        <td>{{ $camera->brand_id }}</td>
    </tr>
    <tr>
        <td>Category ID</td>
        <td>{{ $camera->category_id }}</td>
    </tr>
    <tr>
        <td>Processor</td>
        <td>{{ $camera->attributes['processor'] }}</td>
    </tr>
    <tr>
        <td>Sensor Type</td>
        <td>{{ $camera->attributes['sensor_type'] }}</td>
    </tr>
    <tr>
        <td>Monitor Type</td>
        <td>{{ $camera->attributes['monitor_type'] }}</td>
    </tr>
    <tr>
        <td>Scanning System</td>
        <td>{{ $camera->attributes['scanning_system'] }}</td>
    </tr>
</table>

routes/web.phpのルートを変更して、カメラを表示します。

ルート/web.php

// ...

Route::get(
    '/product/camera/show/{id}',
    [CameraController::class, 'show']
);

次のコマンドを使用してアプリケーションを提供します。

php artisan serve

次に、Webブラウザでカメラ製品の有効なid（localhost:8000/product/camera/show/11など）にアクセスします。 idが「11」の製品のカメラ情報の表が表示されます。

ステップ10—製品の編集

storeとshowの手法を組み合わせて使用することで、既存の製品のeditへのビューを作成できます。

new.blade.phpと同様のフォームを作成できます。次に、show.blade.phpで使用されているものと同様の製品変数を事前に入力します。

<tr>
    <td><label for="attributes-processor">Processor</label></td>
    <td><input id="attributes-processor" name="processor" type="text" value="{{ $camera->attributes['processor'] }}"></td>
</tr>

これで、フォームに既存の値が表示され、ユーザーは更新が必要なものを簡単に確認できるようになります。

まず、idを使用してモデルを取得します。次に、リクエストの値が適用されます。最後に、新しい値がデータベースに保存されます。

ステップ11—JSON属性に基づく検索

EloquentORMを使用してJSON列をクエリすることもできます。

ユーザーが関心のある属性に基づいてカメラを検索できる検索ページについて考えてみます。

public function search(Request $request){
    $cameras = \App\Models\Product::where([
        ['attributes->processor', 'like', $request->processor],
        ['attributes->sensor_type', 'like', $request->sensor_type],
        ['attributes->monitor_type', 'like', $request->monitor_type],
        ['attributes->scanning_system', 'like', $request->scanning_system]
    ])->get();
    return view('product.camera.search', ['cameras' => $cameras]);
}

取得したレコードは、product.camera.searchビューで$camerasコレクションとして使用できるようになります。これにより、結果をループして、ユーザーの検索要求の条件を満たすカメラを表示できます。

ステップ12—製品の削除

非JSON列属性を使用すると、where句を指定してから、deleteメソッドを呼び出すことで製品を削除できます。

たとえば、idの場合です。

\App\Models\Product::where('id', $id)->delete();

JSON列の場合、単一または複数の属性を使用してwhere句を指定してから、deleteメソッドを呼び出します。

\App\Models\Product::where('attributes->sensor_type', 'CMOS')->delete();
}

この例では、このコードはsensor_type属性が「CMOS」に設定されているすべての製品を削除します。

結論

この記事では、JSONデータ型を使用してMySQLデータベースを設計し、LaravelWebアプリケーションを使用してデータベースに接続しました。

データをキーと値のペアとして別のテーブルに保存したり、エンティティの柔軟な属性を操作したりする必要がある場合は、データベース設計の圧縮に大きく貢献する可能性があるため、代わりにJSONデータ型フィールドの使用を検討する必要があります。

さらに深く掘り下げることに興味がある場合は、 MySQLドキュメントは、JSONの概念をさらに探求するための優れたリソースです。

Laravelの詳細については、 Get Started withLaravelに関するテクニカルトークを参照してください。

getdocs

8684