1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

laravelとmysqlでJSON型のカラムを保存する時のバリデーションを3つ考えてみた

Posted at

私はlaravel初心者です。一般的な方法などあれば教えていただけるとありがたいです :bow:

json型のfooカラムがあるテーブル

create table hoges (
 `id` int unsigned not null primary key auto_increment,
 `foo` json,
 `created_at` datetime default null,
 `updated_at` datetime default null
);

1. ミューテタとValidatorを使う

class Hoge extends Model
{
    /**
     * @var array
     */
    protected $fillable = ['foo'];

    /**
     * @var array
     */
    protected $casts = ['foo' => 'json'];

    /**
     * @param $value
     */
    public function setFooAttribute($value)
    {
        $validator = Validator::make($value, [
            'title' => 'required|max:30',
            'body' => 'required',
        ]);
        throw_if($validator->fails(), new \Exception("不正"));
        $this->attributes['foo'] = json_encode($value);
    }
}

これで↓のように保存しようとしても、'body'がないためエラーになってくれました

Hoge::create([
   'foo' => ['title' => 'タイトル'],
]);

保存もできたのを確認できました

Screen Shot 2020-06-22 at 3.02.55.png

2. ミューテタとJSON Schemaを使う

# ググって上の方に出たライブラリを使いました
$ composer require justinrainbow/json-schema

あまり詳しくないですが、このような感じにしてみました

class Hoge extends Model
{
    /**
     * @var array
     */
    protected $fillable = ['foo'];

    /**
     * @var array
     */
    protected $casts = ['foo' => 'json'];

    /**
     * @param $value
     */
    public function setFooAttribute($value)
    {
        $jsonSchema = <<<'JSON'
        {         
            "$schema": "http://json-schema.org/draft-04/schema#",
                "definitions": {
                    "todo": {
                        "definitions": {
                        "id": {
                            "description": "TODOのID",
                            "type": "integer"
                        },
                        "naiyou": {
                            "description": "TODOの内容",
                            "type": "string"
                        }
                    }
                }
            },
            "type": "object",
            "properties": {
                "todos": {
                    "type": "array",
                    "items": {
                        "type": "object",
                        "properties": {
                            "id": {
                                "$ref": "#/definitions/todo/definitions/id"
                            },
                            "naiyou": {
                                "$ref": "#/definitions/todo/definitions/naiyou"
                            }
                        }
                    }
                }
            }
        }
        JSON;
        $jsonSchemaObject = json_decode($jsonSchema);
        $validator = new \JsonSchema\Validator();
        $validator->validate($value, $jsonSchemaObject);
        throw_if(!$validator->isValid(), new \Exception("不正"));
        $this->attributes['foo'] = json_encode([$value]);
    }
}
        Hoge::create([
            'foo' => (object) [
                'todos' => [
                    (object) ['id' => 1, 'naiyou' => '買い物'],
                    (object) ['id' => 2, 'naiyou' => '映画'],
                ],
            ],
        ]);

Screen Shot 2020-06-22 at 3.50.01.png

mysql8のcheck制約とJSON Schemaでバリデーションする

laravelを使わないで、mysqlの機能だけでやってみようと思います

(chekc制約は、mysql8.0.16から)

json_schema_validでtrueになったデータのみ入れられる

create table hoges (
 `id` int unsigned not null primary key auto_increment,
 `foo` json,
 `created_at` datetime default null,
 `updated_at` datetime default null,
 check(
   json_schema_valid(
    '{         
        "$schema": "http://json-schema.org/draft-04/schema#",
            "definitions": {
                "todo": {
                    "definitions": {
                    "id": { "description": "TODOのID", "type": "integer"} ,
                    "naiyou": { "description": "TODOの内容", "type": "string" }
                }
            }
        },
        "type": "object",
        "properties": {
            "todos": {
                "type": "array",
                "items": {
                    "type": "object",
                    "properties": {
                        "id": { "$ref": "#/definitions/todo/definitions/id" },
                        "naiyou": { "$ref": "#/definitions/todo/definitions/naiyou"}
                    }
                }
            }
        }
    }',
    foo
   )
 )
);
-- OK
insert into hoges(foo) values('{"todos": [{"id": 1, "naiyou": "買い物"}, {"id": 2, "naiyou": "映画"}]}')

-- Error Code: 3819. Check constraint 'hoges_chk_1' is violated.
insert into hoges(foo) values('{"todos":1}');

みていただいてありがとうございましたm(_ _)m

1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?