1. The request parameters are as follows, and the uniqueness of logistics_freight_no in the table must be ensured. Since batch overwrite writing is supported, the final implementation plan is to first delete all records where return_order_id is 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 validation 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 a duplicate value for logistics_freight_no exists in the table, validation fails with the message: The freight number has already been taken. As shown in Figure 1
4. Reference: Forcing A 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 a system-generated unique ID such as an auto-incrementing ID or UUID from an Eloquent model instance. Otherwise, your application will be vulnerable to SQL injection attacks. I had to pass the request input return_order_id to the ignore method, but I can validate in advance whether return_order_id is an auto-incrementing ID of an Eloquent model instance. To prevent SQL injection attacks, I decided to cast it to the 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 succeeds, the second request can still succeed even with a duplicate logistics_freight_no. The final executed SQL is as follows, which meets expectations
{
"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 return_order_id is not 28, validation fails with: The freight number has already been taken, which meets expectations. The executed SQL is 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'