In Laravel 9, verify the uniqueness of the field value in the table and have to pass the request input to the compromise of the Ignore method
1. The request parameters are as follows, you need to ensure the uniqueness of logistics_freight_no in the table. Since batch overriding writes are supported, the final implementation is to clear all records of RETURN_ORDER_ID of 28, and then rewrite them all.
{
"return_order_id": 28,
"data": [
{
"logistics_company": 65656,
"logistics_freight_no": 1
},
{
"logistics_company": 65656,
"logistics_freight_no": 2
}
]
}
2. The verification rules are implemented as follows
$validator = Validator::make(
$params,
[
'return_order_id' => [
'required',
'numeric',
'integer',
Rule::exists('Modules\Order\Models\ReturnOrder', 'id')
],
'data' => [
'required',
'array'
],
'data.*.logistics_company' => 'max::255',
'data.*.logistics_freight_no' => [
'required',
'distinct',
'max::255',
Rule::unique('Modules\Order\Models\ReturnOrderLogistic'),
]
],
[
'return_order_id.required' => '请选择退货订单ID',
'return_order_id.numeric' => '退货订单ID必须是数字',
'return_order_id.integer' => '退货订单ID必须是整数',
'return_order_id.exists' => '退货订单ID不存在',
'data.required' => '请填写退货订单物流信息',
'data.array' => '退货订单物流信息必须是数组',
'data.*.logistics_company.max' => '物流公司最大长度是255',
'data.*.logistics_freight_no.required' => '请填写货运单号',
'data.*.logistics_freight_no.distinct' => '货运单号不允许重复',
'data.*.logistics_freight_no.max' => '货运单号最大长度是255',
'data.*.logistics_freight_no.unique' => '货运单号已被占用'
]
);
3. When there is a duplicate value in the logistics_freight_no in the table, the verification fails, and it prompts that the waybill number has been occupied. as shown in Figure 1
Figure 1
4. Reference:Force Unique Rule to ignore a given ID . However, note: you should not pass any user-controlled request input to the ignore method. Instead, you should only pass the unique ID of the system generated by the system, such as the automatic incrementing ID or UUID from the Eloquent model instance. Otherwise, your application will be vulnerable to SQL injection. You have to pass the request input return_order_id to the ignore method, but you can verify in advance whether the return_order_id is an auto-incremented ID of the Eloquent model instance. In order to prevent SQL injection attacks, decide to cast to int type
$validator = Validator::make(
$params,
[
'return_order_id' => [
'required',
'numeric',
'integer',
Rule::exists('Modules\Order\Models\ReturnOrder', 'id')
],
'data' => [
'required',
'array'
],
'data.*.logistics_company' => 'max::255',
'data.*.logistics_freight_no' => [
// 如果退货订单ID不存在，则不验证，防止报错：Undefined array key "return_order_id"
'exclude_without:return_order_id',
'required',
'distinct',
'max::255',
Rule::unique('Modules\Order\Models\ReturnOrderLogistic')->ignore((int)$params['return_order_id'], 'return_order_id')
]
],
[
'return_order_id.required' => '请选择退货订单ID',
'return_order_id.numeric' => '退货订单ID必须是数字',
'return_order_id.integer' => '退货订单ID必须是整数',
'return_order_id.exists' => '退货订单ID不存在',
'data.required' => '请填写退货订单物流信息',
'data.array' => '退货订单物流信息必须是数组',
'data.*.logistics_company.max' => '物流公司最大长度是255',
'data.*.logistics_freight_no.required' => '请填写货运单号',
'data.*.logistics_freight_no.distinct' => '货运单号不允许重复',
'data.*.logistics_freight_no.max' => '货运单号最大长度是255',
'data.*.logistics_freight_no.unique' => '货运单号已被占用'
]
);
5. After the first request is successful, although the second request has duplicate logistics_freight_no , the request can still be successful. The final execution SQL is as follows, in line with the expected
{
"return_order_id": 28,
"data": [
{
"logistics_company": 65656,
"logistics_freight_no": 1
},
{
"logistics_company": 65656,
"logistics_freight_no": 2
},
{
"logistics_company": 65656,
"logistics_freight_no": 24
}
]
}
select count(*) as aggregate from `return_orders` where `id` = 28
select
count(*) as aggregate
from
`return_order_logistics`
where
`logistics_freight_no` = 1
and `return_order_id` <> '28'
select
count(*) as aggregate
from
`return_order_logistics`
where
`logistics_freight_no` = 2
and `return_order_id` <> '28'
select
count(*) as aggregate
from
`return_order_logistics`
where
`logistics_freight_no` = 24
and `return_order_id` <> '28'
delete from `return_order_logistics` where `return_order_id` = 28
insert into
`return_order_logistics` (
`created_at_gmt`,
`logistics_company`,
`logistics_freight_no`,
`return_order_id`,
`updated_at_gmt`
)
values
(
'2024-04-25 02:23:41',
65656,
1,
28,
'2024-04-25 02:23:41'
),
(
'2024-04-25 02:23:41',
65656,
2,
28,
'2024-04-25 02:23:41'
),
(
'2024-04-25 02:23:41',
65656,
24,
28,
'2024-04-25 02:23:41'
)
6. Then when the return_order_id is not 28, the verification fails: the waybill number has been occupied, which is in line with expectations. Execute SQL as follows
{
"return_order_id": 29,
"data": [
{
"logistics_company": 65656,
"logistics_freight_no": 1
},
{
"logistics_company": 65656,
"logistics_freight_no": 2
},
{
"logistics_company": 65656,
"logistics_freight_no": 24
}
]
}
{
"status_code": 400,
"code": -1,
"message": "货运单号已被占用"
}
select count(*) as aggregate from `return_orders` where `id` = 29
select
count(*) as aggregate
from
`return_order_logistics`
where
`logistics_freight_no` = 1
and `return_order_id` <> '29'
