在 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

图1

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、代码实现如下

    /**
     * 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

图2

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>

        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 符合预期。

        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('所有已拆分订单金额的历史数据修复结束');
    }

PS E:\wwwroot\object> php artisan upgrade:v0.26.0:fix-split-order-amount
所有已拆分订单金额的历史数据修复开始
第1批处理完毕
第2批处理完毕
第3批处理完毕
所有已拆分订单金额的历史数据修复结束

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` asc
limit
  1