在 Laravel 9 中，基于 chunkById 分块查询时报错：SQLSTATE[23000]: Integrity constraint violation: 1052 Column ‘id’ in where clause is ambiguous
1、在 Laravel 9 中，基于 chunkById 分块查询时报错：SQLSTATE[23000]: Integrity constraint violation: 1052 Column ‘id’ in where clause is ambiguous 。如图1
![在 Laravel 9 中，基于 chunkById 分块查询时报错：SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in where clause is ambiguous](https://www.shuijingwanwq.com/wp-content/uploads/2024/07/1-5.png)
图1
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 | PS E:\wwwroot\object> php artisan upgrade:v0.26.0:fix-split-order-amount所有已拆分订单金额的历史数据修复开始第1批处理完毕 Illuminate\Database\QueryException SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in where clause is ambiguous (SQL: select distinct `orders`.`id`, `orders`.`amount_receivable`, `orders`.`amount_postage`, `orders`.`amount_insurance`, `orders`.`refund_amount`, `orders`.`discount_amount`, `orders`.`other_income_cny`, `orders`.`other_expense_cny`, `orders`.`rate` from `orders` inner join `orders` as `split_orders` on `orders`.`id` = `split_orders`.`parent_split_id` where `split_orders`.`create_type` = 50 and `split_orders`.`parent_split_id` is not null and `orders`.`id` in (3174, 3237, 3268) and `id` > 3174 order by `id` asc limit 1) at E:\wwwroot\object\vendor\laravel\framework\src\Illuminate\Database\Connection.php:759 755▕ // If an exception occurs when attempting to run a query, we'll format the error 756▕ // message to include the bindings with SQL, which will make this exception a 757▕ // lot more helpful to the developer instead of just the database's errors. 758▕ catch (Exception $e) { ➜ 759▕ throw new QueryException( 760▕ $query, $this->prepareBindings($bindings), $e 761▕ ); 762▕ } 763▕ } 1 E:\wwwroot\object\vendor\laravel\framework\src\Illuminate\Database\Connection.php:418 PDOException::("SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in where clause is ambiguous") 2 E:\wwwroot\object\vendor\laravel\framework\src\Illuminate\Database\Connection.php:418 PDOStatement::execute()PS E:\wwwroot\object> |
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 36 37 38 39 40 41 42 43 | /** * Execute the console command. * * @return void */ public function handle(): void { $this->comment('所有已拆分订单金额的历史数据修复开始'); $i = 1; Order::query() ->select('orders.id', 'orders.amount_receivable', 'orders.amount_postage', 'orders.amount_insurance', 'orders.refund_amount', 'orders.discount_amount', 'orders.other_income_cny', 'orders.other_expense_cny', 'orders.rate') ->join('orders as split_orders', 'orders.id', '=', 'split_orders.parent_split_id') ->where('split_orders.create_type', '=', Order::SPLIT_CREATION) ->whereNotNull('split_orders.parent_split_id') ->distinct() ->with('orderItems:id,order_id,quantity,price_sale,is_discard') ->whereIn('orders.id', [3174,3237,3268]) ->chunkById( 1, function ($orders) use (&$i) {// $parentSplitIds = $orders->pluck('id')->all();// $splitOrders = Order::query()// ->select('id', 'amount_receivable', 'amount_postage', 'amount_insurance', 'refund_amount', 'discount_amount', 'other_income_cny', 'other_expense_cny', 'rate', 'parent_split_id')// ->where('create_type', '=', Order::SPLIT_CREATION)// ->whereIn('parent_split_id', $parentSplitIds)// ->with('orderItems:id,order_id,quantity,price_sale,is_discard')// ->get();//// // 根据 parent_split_id 对集合项进行分组// $groupedSplitOrders = $splitOrders->groupBy('parent_split_id')->all(); foreach ($orders as $order) { // $this->calculateSplitOrderAmount($order, $groupedSplitOrders[$order->id]); } $this->line(sprintf('第%s批处理完毕', $i)); $i++; } ); $this->comment('所有已拆分订单金额的历史数据修复结束'); } |
3、添加了 chunkById 的 column 参数后，报错：The chunkById operation was aborted because the [orders.id] column is not present in the query result.如图2
![添加了 chunkById 的 column 参数后，报错：The chunkById operation was aborted because the [orders.id] column is not present in the query result.](https://www.shuijingwanwq.com/wp-content/uploads/2024/07/2-4.png)
图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 | PS E:\wwwroot\object> php artisan upgrade:v0.26.0:fix-split-order-amount所有已拆分订单金额的历史数据修复开始第1批处理完毕 RuntimeException The chunkById operation was aborted because the [orders.id] column is not present in the query result. at E:\wwwroot\object\vendor\laravel\framework\src\Illuminate\Database\Concerns\BuildsQueries.php:148 144▕ 145▕ $lastId = data_get($results->last(), $alias); 146▕ 147▕ if ($lastId === null) { ➜ 148▕ throw new RuntimeException("The chunkById operation was aborted because the [{$alias}] column is not present in the query result."); 149▕ } 150▕ 151▕ unset($results); 152▕ 1 E:\wwwroot\object\app\Console\Commands\Upgrades\v0_26_0\FixSplitOrderAmount.php:66 Illuminate\Database\Eloquent\Builder::chunkById(Object(Closure), "orders.id") 2 E:\wwwroot\object\vendor\laravel\framework\src\Illuminate\Container\BoundMethod.php:36 App\Console\Commands\Upgrades\v0_26_0\FixSplitOrderAmount::handle()PS E:\wwwroot\object> |
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 | Order::query() ->select('orders.id', 'orders.amount_receivable', 'orders.amount_postage', 'orders.amount_insurance', 'orders.refund_amount', 'orders.discount_amount', 'orders.other_income_cny', 'orders.other_expense_cny', 'orders.rate') ->join('orders as split_orders', 'orders.id', '=', 'split_orders.parent_split_id') ->where('split_orders.create_type', '=', Order::SPLIT_CREATION) ->whereNotNull('split_orders.parent_split_id') ->distinct() ->with('orderItems:id,order_id,quantity,price_sale,is_discard') ->whereIn('orders.id', [3174,3237,3268]) ->chunkById( 1, function ($orders) use (&$i) {// $parentSplitIds = $orders->pluck('id')->all();// $splitOrders = Order::query()// ->select('id', 'amount_receivable', 'amount_postage', 'amount_insurance', 'refund_amount', 'discount_amount', 'other_income_cny', 'other_expense_cny', 'rate', 'parent_split_id')// ->where('create_type', '=', Order::SPLIT_CREATION)// ->whereIn('parent_split_id', $parentSplitIds)// ->with('orderItems:id,order_id,quantity,price_sale,is_discard')// ->get();//// // 根据 parent_split_id 对集合项进行分组// $groupedSplitOrders = $splitOrders->groupBy('parent_split_id')->all(); foreach ($orders as $order) { // $this->calculateSplitOrderAmount($order, $groupedSplitOrders[$order->id]); } $this->line(sprintf('第%s批处理完毕', $i)); $i++; }, 'orders.id' ); |
4、再次添加了 chunkById 的 alias 参数后，不再报错。且生成的 SQL 符合预期。
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 | Order::query() ->select('orders.id', 'orders.amount_receivable', 'orders.amount_postage', 'orders.amount_insurance', 'orders.refund_amount', 'orders.discount_amount', 'orders.other_income_cny', 'orders.other_expense_cny', 'orders.rate') ->join('orders as split_orders', 'orders.id', '=', 'split_orders.parent_split_id') ->where('split_orders.create_type', '=', Order::SPLIT_CREATION) ->whereNotNull('split_orders.parent_split_id') ->distinct() ->with('orderItems:id,order_id,quantity,price_sale,is_discard') ->whereIn('orders.id', [3174,3237,3268]) ->chunkById( 1, function ($orders) use (&$i) {// $parentSplitIds = $orders->pluck('id')->all();// $splitOrders = Order::query()// ->select('id', 'amount_receivable', 'amount_postage', 'amount_insurance', 'refund_amount', 'discount_amount', 'other_income_cny', 'other_expense_cny', 'rate', 'parent_split_id')// ->where('create_type', '=', Order::SPLIT_CREATION)// ->whereIn('parent_split_id', $parentSplitIds)// ->with('orderItems:id,order_id,quantity,price_sale,is_discard')// ->get();//// // 根据 parent_split_id 对集合项进行分组// $groupedSplitOrders = $splitOrders->groupBy('parent_split_id')->all(); foreach ($orders as $order) { // $this->calculateSplitOrderAmount($order, $groupedSplitOrders[$order->id]); } $this->line(sprintf('第%s批处理完毕', $i)); $i++; }, 'orders.id', 'id' ); $this->comment('所有已拆分订单金额的历史数据修复结束'); } |
1 2 3 4 5 6 | PS E:\wwwroot\object> php artisan upgrade:v0.26.0:fix-split-order-amount所有已拆分订单金额的历史数据修复开始第1批处理完毕第2批处理完毕第3批处理完毕所有已拆分订单金额的历史数据修复结束 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | select distinct `orders`.`id`, `orders`.`amount_receivable`, `orders`.`amount_postage`, `orders`.`amount_insurance`, `orders`.`refund_amount`, `orders`.`discount_amount`, `orders`.`other_income_cny`, `orders`.`other_expense_cny`, `orders`.`rate`from `orders` inner join `orders` as `split_orders` on `orders`.`id` = `split_orders`.`parent_split_id`where `split_orders`.`create_type` = 50 and `split_orders`.`parent_split_id` is not null and `orders`.`id` in (3174, 3237, 3268)order by `orders`.`id` asclimit 1 |
近期评论