Laravel查询提示purchase_return_products.purchase_amount不存在但字段实际存在求助

问题描述 #

Laravel项目执行查询时，purchase_return_products表明确存在purchase_amount字段，但系统抛出该列不存在的SQL错误。

报错信息 #

[2024-07-14 16:28:02] local.ERROR: Errors: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'purchase_return_products.purchase_amount' in 'field list' (Connection: mysql, SQL: select count(*) as aggregate from (select IFNULL(SUM(purchase_products.quantity), 0) as purchase_quantity, IFNULL(SUM(purchase_products.purchase_amount), 0) as purchase_amount, `purchase_products`.`product_id`, IFNULL(SUM(sale_products.quantity), 0) as sale_quantity, IFNULL(SUM(sale_products.sale_purchase_amount), 0) as sale_purchase_amount, IFNULL(SUM(purchase_return_products.quantity), 0) as purchase_return_quantity, IFNULL(SUM(purchase_return_products.purchase_amount), 0) as purchase_return_amount from `products` left join (SELECT product_id, SUM(purchase_products.quantity) as quantity, SUM(purchase_products.purchase_amount) as purchase_amount FROM purchase_products GROUP BY product_id) as purchase_products on `purchase_products`.`product_id` = `products`.`identity` left join (SELECT product_id, SUM(sale_products.quantity) as quantity, SUM(sale_products.sale_purchase_amount) as sale_purchase_amount FROM sale_products GROUP BY product_id) as sale_products on `sale_products`.`product_id` = `products`.`identity` left join (SELECT product_id, SUM(purchase_return_products.quantity) as quantity, SUM(purchase_return_products.purchase_amount) as purchase_return_amount FROM purchase_return_products GROUP BY product_id) as purchase_return_products on `purchase_return_products`.`product_id` = `products`.`identity` where `products`.`store_id` = 2024061225851482 group by `purchase_products`.`product_id` having (purchase_quantity - (purchase_return_quantity + sale_quantity)) > 0) as `aggregate_table`)

Laravel查询代码 #

$data = $this->helper_object->getModel('Product')::select(
                    DB::raw('IFNULL(SUM(purchase_products.quantity), 0) as purchase_quantity'),
                    DB::raw('IFNULL(SUM(purchase_products.purchase_amount), 0) as purchase_amount'),
                   'purchase_products.product_id',
                    DB::raw('IFNULL(SUM(sale_products.quantity), 0) as sale_quantity'),
                    DB::raw('IFNULL(SUM(sale_products.sale_purchase_amount), 0) as sale_purchase_amount'),
                    DB::raw('IFNULL(SUM(purchase_return_products.quantity), 0) as purchase_return_quantity'),
                    DB::raw('IFNULL(SUM(purchase_return_products.purchase_amount), 0) as purchase_return_amount')
                )
                ->leftJoin(DB::raw('(SELECT product_id, SUM(purchase_products.quantity) as quantity, SUM(purchase_products.purchase_amount) as purchase_amount FROM purchase_products GROUP BY product_id) as purchase_products'), function($join) {
                    $join->on('purchase_products.product_id', '=', 'products.identity');
                    })
                ->leftJoin(DB::raw('(SELECT product_id, SUM(sale_products.quantity) as quantity, SUM(sale_products.sale_purchase_amount) as sale_purchase_amount FROM sale_products GROUP BY product_id) as sale_products'), function($join) {
                    $join->on('sale_products.product_id', '=', 'products.identity');
                    })
                ->leftJoin(DB::raw('(SELECT product_id, SUM(purchase_return_products.quantity) as quantity, SUM(purchase_return_products.purchase_amount) as purchase_return_amount FROM purchase_return_products GROUP BY product_id) as purchase_return_products'), function($join) {
                    $join->on('purchase_return_products.product_id', '=', 'products.identity');
                    })
                ->where('products.store_id', $store_id)
                ->when($query, function ($queryBuilder) use ($query) {
                      $queryBuilder->where(function ($innerQueryBuilder) use ($query) {
                         $innerQueryBuilder->where('products.name', 'like', "%$query%")
                                           ->orWhere('products.sku_code_number', 'like', "%$query%");
                                           });
                                         })
                ->when($brand, function ($queryBuilder) use ($brand) {
                      $queryBuilder->where(function ($innerQueryBuilder) use ($brand) {
                         $innerQueryBuilder->where('products.brand', $brand);
                                           });
                                         })
                ->when($category, function ($queryBuilder) use ($category) {
                      $queryBuilder->where(function ($innerQueryBuilder) use ($category) {
                         $innerQueryBuilder->where('products.category', $category);
                                           });
                                         })
                ->groupBy('purchase_products.product_id')
                ->havingRaw('(purchase_quantity - (purchase_return_quantity + sale_quantity)) > 0')
                ->orderBy('products.sku_code_number', 'asc')
                ->paginate(30);

相关表结构 #

purchase_return_products表 #

Schema::create('purchase_return_products', function (Blueprint 
                $table) {
                $table->id();
                $table->string('identity',16)->unique()->index();
                $table->string('purchase_identity',16)->index();
                $table->string('unique_identity',16)->index();
                $table->string('invoice_id',16)->index();
                $table->string('warehouse_id',16)->index();
                $table->string('store_id',16)->index();
                $table->string('parent_id',16)->index();
                $table->string('party_id',16)->index();
                $table->string('product_id',16)->index();
                $table->string('variant_id',16)->index();
                $table->string('serial',120)->nullable()->index();
                $table->decimal('quantity', 25, 3)->index();
                $table->decimal('purchase_price', 25, 3)->index();
                $table->decimal('purchase_amount', 25, 3)->index();
                $table->decimal('unit_offer', 25, 3)->nullable()->index();
                $table->decimal('total_offer', 25, 3)->nullable()->index();
                $table->timestamps();
                $table->date('date')->index();
                $table->string('status',10)->default('Pending')->index();
                $table->string('added_by',16)->index();
                $table->string('updated_by',16)->nullable();

sale_products表 #

Schema::create('sale_products', function (Blueprint $table) {
            $table->id();
            $table->string('identity',16)->unique()->index();
            $table->string('warehouse_id',16)->index();
            $table->string('store_id',16)->index();
            $table->string('parent_id',16)->index();
            $table->string('party_id',16)->index();
            $table->string('purchase_identity',16)->index();
            $table->string('product_id',16)->index();
            $table->string('variant_id',16)->index();
            $table->string('invoice_id',16)->index();
            $table->string('serial',120)->nullable()->index();
            $table->string('sale_type',10)->default('Sale')->index();
            $table->decimal('quantity', 25, 3)->index();
            $table->decimal('purchase_price', 25, 3)->index();
            $table->decimal('sale_purchase_amount', 25, 3)->index();
            $table->decimal('sale_price', 25, 3)->index();
            $table->decimal('sale_amount', 25, 3)->index();
            $table->decimal('discount_percent', 25, 3)->nullable()->index();
            $table->decimal('total_discount', 25, 3)->nullable()->index();
            $table->string('offer_id',16)->nullable()->index();
            $table->decimal('unit_offer', 25, 3)->nullable()->index();
            $table->decimal('total_offer', 25, 3)->nullable()->index();
            $table->decimal('profit', 25, 3)->nullable()->index();
            $table->date('warranty_date')->nullable()->index();
            $table->timestamps();
            $table->date('date')->index();
            $table->string('status',10)->default('Pending')->index();
            $table->string('added_by',16)->index();
            $table->string('updated_by',16)->nullable();

purchase_products表 #

Schema::create('purchase_products', function (Blueprint $table) {
            $table->id();
            $table->string('identity',16)->unique()->index();
            $table->string('warehouse_id',16)->index();
            $table->string('store_id',16)->index();
            $table->string('parent_id',16)->index();
            $table->string('party_id',16)->index();
            $table->string('product_id',16)->index();
            $table->string('variant_id',16)->index();
            $table->string('invoice_id',16)->index();
            $table->string('sale_status',9)->default('Running')->index();
            $table->string('serial',120)->nullable()->index();
            $table->string('purchase_type',10)->index();
            $table->decimal('quantity', 25, 3)->index();
            $table->decimal('purchase_price', 25, 3)->index();
            $table->decimal('purchase_amount', 25, 3)->index();
            $table->decimal('unit_offer', 25, 3)->nullable()->index();
            $table->decimal('total_offer', 25, 3)->nullable()->index();
            $table->date('expire_date')->nullable()->index();
            $table->timestamps();
            $table->date('date')->index();
            $table->string('status',10)->default('Pending')->index();
            $table->string('added_by',16)->index();
            $table->string('updated_by',16)->nullable();

排查与解决方案 #

问题根源 #

查看生成的SQL语句可知：
关联purchase_return_products的子查询中，已将SUM(purchase_return_products.purchase_amount)的别名设为purchase_return_amount，但外层查询仍尝试调用SUM(purchase_return_products.purchase_amount)。由于子查询仅返回product_id、quantity、purchase_return_amount三个字段，外层无法找到purchase_amount字段，因此报错。

此外，外层对已在子查询中求和的字段再次使用SUM属于冗余操作，若关联后出现多行匹配，会导致数据重复计算。

修复方案 #

方案1：统一子查询字段别名

将purchase_return_products子查询中的purchase_return_amount别名改为purchase_amount，与其他表的子查询保持一致：

->leftJoin(DB::raw('(SELECT product_id, SUM(purchase_return_products.quantity) as quantity, SUM(purchase_return_products.purchase_amount) as purchase_amount FROM purchase_return_products GROUP BY product_id) as purchase_return_products'), function($join) {
    $join->on('purchase_return_products.product_id', '=', 'products.identity');
})

同时简化外层SELECT语句，去掉冗余的SUM（子查询已按product_id分组求和，外层直接取别名即可）：

->select(
    DB::raw('IFNULL(purchase_products.quantity, 0) as purchase_quantity'),
    DB::raw('IFNULL(purchase_products.purchase_amount, 0) as purchase_amount'),
    'purchase_products.product_id',
    DB::raw('IFNULL(sale_products.quantity, 0) as sale_quantity'),
    DB::raw('IFNULL(sale_products.sale_purchase_amount, 0) as sale_purchase_amount'),
    DB::raw('IFNULL(purchase_return_products.quantity, 0) as purchase_return_quantity'),
    DB::raw('IFNULL(purchase_return_products.purchase_amount, 0) as purchase_return_amount')
)

方案2：修改外层字段引用

若需保留子查询的purchase_return_amount别名，只需将外层查询对应的字段改为该别名（同样建议去掉冗余SUM）：

DB::raw('IFNULL(purchase_return_products.purchase_return_amount, 0) as purchase_return_amount')

最终修复后的完整代码 #

$data = $this->helper_object->getModel('Product')::select(
        DB::raw('IFNULL(purchase_products.quantity, 0) as purchase_quantity'),
        DB::raw('IFNULL(purchase_products.purchase_amount, 0) as purchase_amount'),
        'purchase_products.product_id',
        DB::raw('IFNULL(sale_products.quantity, 0) as sale_quantity'),
        DB::raw('IFNULL(sale_products.sale_purchase_amount, 0) as sale_purchase_amount'),
        DB::raw('IFNULL(purchase_return_products.quantity, 0) as purchase_return_quantity'),
        DB::raw('IFNULL(purchase_return_products.purchase_amount, 0) as purchase_return_amount')
    )
    ->leftJoin(DB::raw('(SELECT product_id, SUM(purchase_products.quantity) as quantity, SUM(purchase_products.purchase_amount) as purchase_amount FROM purchase_products GROUP BY product_id) as purchase_products'), function($join) {
        $join->on('purchase_products.product_id', '=', 'products.identity');
    })
    ->leftJoin(DB::raw('(SELECT product_id, SUM(sale_products.quantity) as quantity, SUM(sale_products.sale_purchase_amount) as sale_purchase_amount FROM sale_products GROUP BY product_id) as sale_products'), function($join) {
        $join->on('sale_products.product_id', '=', 'products.identity');
    })
    ->leftJoin(DB::raw('(SELECT product_id, SUM(purchase_return_products.quantity) as quantity, SUM(purchase_return_products.purchase_amount) as purchase_amount FROM purchase_return_products GROUP BY product_id) as purchase_return_products'), function($join) {
        $join->on('purchase_return_products.product_id', '=', 'products.identity');
    })
    ->where('products.store_id', $store_id)
    ->when($query, function ($queryBuilder) use ($query) {
        $queryBuilder->where(function ($innerQueryBuilder) use ($query) {
            $innerQueryBuilder->where('products.name', 'like', "%$query%")
                ->orWhere('products.sku_code_number', 'like', "%$query%");
        });
    })
    ->when($brand, function ($queryBuilder) use ($brand) {
        $queryBuilder->where(function ($innerQueryBuilder) use ($brand) {
            $innerQueryBuilder->where('products.brand', $brand);
        });
    })
    ->when($category, function ($queryBuilder) use ($category) {
        $queryBuilder->where(function ($innerQueryBuilder) use ($category) {
            $innerQueryBuilder->where('products.category', $category);
        });
    })
    ->groupBy('purchase_products.product_id')
    ->havingRaw('(purchase_quantity - (purchase_return_quantity + sale_quantity)) > 0')
    ->orderBy('products.sku_code_number', 'asc')
    ->paginate(30);

内容的提问来源于stack exchange，提问作者Ahm Shamim Reza