README

Query2ValidationException会将mysql QueryException与业务逻辑相关的异常转换为ValidationException。
例如
500列 ':attribute' 不能为空
转换为
422 :attribute字段是必需的。

安装

composer require asmgit/query2validation-exception

快速开始

app/Exception/Handler.php

use Asmgit\ValidationException;
...
public function report(Exception $exception)
{
    if (Query2ValidationException::check($exception)) {
        $this->dontReport[] = QueryException::class;
        return (new Query2ValidationException($exception))->render();
    }
...

现在你可以从代码中移除大多数常见的Validation，数据库将负责你的数据。

哪些异常捕获Query2ValidationException

安装后，最常见的异常将转换为ValidationException

ER_BAD_NULL_ERROR, 1048, (列 '%s' 不能为空) 转换为 The :attribute字段是必需的。
ER_DUP_ENTRY, 1062, (对于键 %d的重复条目 '%s') 转换为 The :attribute已经被占用。
ER_DATA_TOO_LONG, 1406, (第 %ld行列 '%s' 数据太长) 转换为 The :attribute太长。
WARN_DATA_TRUNCATED, 1265, (第 %ld行列 '%s' 数据被截断) 转换为 The selected :attribute是无效的。
ER_TRUNCATED_WRONG_VALUE_FOR_FIELD. 1366, (列 '%s' 的值 '%s' 不正确: '%s' 在第 %ld行) 转换为 The :attribute必须是 :field_type类型。

你可以获取所有内置消息

dd(Query2ValidationException::getMessageTemplates());

输出

array:5 [
  1048 => array:3 [
    "orig" => "Column '(.*?)' cannot be null"
    "new" => "The :attribute field is required."
    "params" => array:1 [
      1 => "attribute"
    ]
  ]
  1062 => array:4 [
    "orig" => "Duplicate entry '(.*?)' for key '(.*?)'"
    "new" => "The :attribute has already been taken."
    "params" => array:2 [
      1 => "value"
      2 => "index_name"
    ]
    "params_post_process" => Closure(&$ex) {#736
      class: "App\Exceptions\Query2ValidationException"
    }
  ]
  1406 => array:3 [
    "orig" => "Data too long for column '(.*?)' at row ([0-9]+)"
    "new" => "The :attribute is too long."
    "params" => array:2 [
      1 => "attribute"
      2 => "rownum"
    ]
  ]
  1265 => array:3 [
    "orig" => "Data truncated for column '(.*?)' at row ([0-9]+)"
    "new" => "The selected :attribute is invalid."
    "params" => array:2 [
      1 => "attribute"
      2 => "rownum"
    ]
  ]
  1366 => array:3 [
    "orig" => "Incorrect (.*?) value: '(.*?)' for column '(.*?)' at row ([0-9]+)"
    "new" => "The :attribute must be an :field_type type."
    "params" => array:4 [
      1 => "field_type"
      2 => "value"
      3 => "attribute"
      4 => "rownum"
    ]
  ]
]

如何为异常设置自定义消息

Query2ValidationException::addValidationMessage(
    'Please, fill your first name.', // new message
    Query2ValidationException::ER_BAD_NULL_ERROR, // error type
    'first_name' // field
);
$user->save();
array_pop(Query2ValidationException::$customValidationMessages);

将返回给ajax请求

{ 
    "message":"The given data was invalid.",
    "errors":{ 
        "first_name":[ 
            "Please, fill your first name."
        ]
    }
}

如何处理复合唯一键异常

将为唯一键中的所有字段生成消息。
例如，你需要为每个 account_id 设置唯一的用户 full_name。
创建唯一约束

ALTER TABLE users
ADD UNIQUE INDEX users_account_id_full_name_unique (account_id,full_name)

获取验证异常

{ 
    "message":"The given data was invalid.",
    "errors":{ 
        "account_id":[ 
            "The account_id,full_name has already been taken."
        ],
        "full_name":[ 
            "The account_id,full_name has already been taken."
        ]
    }
}

如何为复合唯一键异常设置自定义消息

一种方法

声明唯一键的 COMMENT

ALTER TABLE users
DROP INDEX users_account_id_full_name_unique
, ADD UNIQUE INDEX users_account_id_full_name_unique (account_id,full_name)
  COMMENT 'User must be unique for each account. :value has already been taken.'

{ 
    "message":"The given data was invalid.",
    "errors":{ 
        "account_id":[ 
            "User must be unique for each account. 1-John Smith has already been taken."
        ],
        "full_name":[ 
            "User must be unique for each account. 1-John Smith has already been taken."
        ]
    }
}

你可以使用参数，如 :value。查看所有参数（输出以上）

dd(Query2ValidationException::getMessageTemplates());

另一种方法（更改字段目标）

public function store(Request $request)
{
    ...
    Query2ValidationException::addValidationMessage(
        'User already exists in this account.', // new message
        Query2ValidationException::ER_DUP_ENTRY, // error type
        'account_id,full_name', // all fields in composite unique key
        'first_name,last_name' // new field/fields
    );
    $user->save();
    array_pop(Query2ValidationException::$customValidationMessages);
...
public function update($id, Request $request)
{
    ...
    Query2ValidationException::addValidationMessage(
        'User already exists in this account.', // new message
        Query2ValidationException::ER_DUP_ENTRY, // error type
        'account_id,full_name', // all fields in composite unique key
        'first_name,last_name' // new field/fields
    );
    $user->save();
    array_pop(Query2ValidationException::$customValidationMessages);

{ 
    "message":"The given data was invalid.",
    "errors":{ 
        "first_name":[ 
            "User already exists in this account."
        ],
        "last_name":[ 
            "User already exists in this account."
        ]
    }
}

如何捕获新的全局mysql异常

检查mysql文档网站中的错误格式
https://dev.mysqlserver.cn/doc/refman/8.0/en/server-error-reference.html
初始化内置消息

Query2ValidationException::$messageTemplates = Query2ValidationException::getMessageTemplates();

创建新的消息模板

Query2ValidationException::$messageTemplates[<errorNo>] = ...

你也可以更改内置消息

示例：mysql >= 8.0.16，支持检查约束
app/Exception/Handler.php

use Asmgit\ValidationException;
...
public function report(Exception $exception)
{
    // setup builtin messageTemplates
    Query2ValidationException::$messageTemplates = Query2ValidationException::getMessageTemplates();
    // Error number: 3819; Symbol: ER_CHECK_CONSTRAINT_VIOLATED; SQLSTATE: HY000 Message: Check constraint '%s' is violated.
    Query2ValidationException::$messageTemplates[3819] = [
        'orig' => "Check constraint '(.*?)\\|(.*?)\\|(.*)' is violated.",
        'new' => "Check constraint is violated: :message",
        'params' => [1 => 'table_name', 2 => 'attribute', 3 => 'message']
    ];
    if (Query2ValidationException::check($exception)) {
        $this->dontReport[] = QueryException::class;
        return (new Query2ValidationException($exception))->render();
    }
...

创建并引发任何 CHECK CONSTRAINT

ALTER TABLE users
ADD CONSTRAINT `users|first_name,last_name|first name must be longer than last name.` CHECK (first_name > last_name);

{ 
    "message":"The given data was invalid.",
    "errors":{ 
        "first_name":[ 
            "Check constraint is violated: first name must be longer than last name."
        ],
        "last_name":[ 
            "Check constraint is violated: first name must be longer than last name."
        ]
    }
}

Query2ValidationException解决方案的缺点是什么？

Laravel Validation可以一次性引发所有错误，而Query2ValidationException只引发一个，因为MySQL只引发第一个错误并停止执行。

Query2ValidationException解决方案的优点是什么？

如果你使用Laravel Validation，你需要描述数据库中的约束并在应用程序层（YourController@store，YourController@update）中重复验证逻辑。使用Query2ValidationException，你只需要在数据库中描述约束。
一些异常你无法在应用程序层中捕获。例如，唯一约束。此代码在单用户应用程序中运行良好。

public function store(Request $request)
{
    $validatedData = $request->validate([
        'full_name' => 'required|unique:users',
    ]);
    sleep(20);
    $user->full_name = $request->input('full_name');
    $user->save();
...

但是，如果你运行此代码两次，新的 unique full_name，第一次会保存会话数据，第二次会得到500异常（如果正确配置了唯一的数据库约束，没有约束，你会在数据库中得到错误数据）

asmgit / query2validation-exception

维护者

详细信息