在 Laravel 9 中，验证字段值在表中的唯一性，不得不将请求输入传递给 ignore 方法的折衷处理
1、请求参数如下，需要确保 logistics_freight_no 在表中的唯一性。由于支持批量覆盖写入，最终的实现方案是先清空 return_order_id 为 28 的所有记录，然后再全部重新写入。
1 2 3 4 5 6 7 8 9 10 11 12 13 | { "return_order_id": 28, "data": [ { "logistics_company": 65656, "logistics_freight_no": 1 }, { "logistics_company": 65656, "logistics_freight_no": 2 } ]} |
2、验证规则实现如下
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | $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、当表中 logistics_freight_no 存在重复值时，验证失败，提示：货运单号已被占用。如图1
图1
4、参考：强制唯一规则忽略给定 ID 。但是，注意：你不应该将任何用户控制的请求输入传递给 ignore 方法。相反，您应该只传递系统生成的唯一 ID，例如来自 Eloquent 模型实例的自动递增 ID 或 UUID。否则，您的应用程序将容易受到 SQL 注入攻击。不得不将请求输入 return_order_id 传递给 ignore 方法，不过可以提前验证 return_order_id 是否是 Eloquent 模型实例的自动递增 ID。为了防止 SQL 注入攻击，决定强制转换为 int 类型
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | $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、当第一次请求成功后，第二次请求虽然有重复的 logistics_freight_no ，仍然可以请求成功。最终执行的 SQL 如下，符合预期
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | { "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 } ]} |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 | select count(*) as aggregate from `return_orders` where `id` = 28select count(*) as aggregatefrom `return_order_logistics`where `logistics_freight_no` = 1 and `return_order_id` <> '28'select count(*) as aggregatefrom `return_order_logistics`where `logistics_freight_no` = 2 and `return_order_id` <> '28'select count(*) as aggregatefrom `return_order_logistics`where `logistics_freight_no` = 24 and `return_order_id` <> '28'delete from `return_order_logistics` where `return_order_id` = 28insert 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、那么当 return_order_id 不为 28 时，验证失败：货运单号已被占用，符合预期。执行 SQL 如下
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | { "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 } ]} |
1 2 3 4 5 | { "status_code": 400, "code": -1, "message": "货运单号已被占用"} |
1 2 3 4 5 6 7 8 9 | select count(*) as aggregate from `return_orders` where `id` = 29select count(*) as aggregatefrom `return_order_logistics`where `logistics_freight_no` = 1 and `return_order_id` <> '29' |
近期评论