Laravel中基于子查询嵌套值计算设备总数量的问题

你遇到的SQLSTATE[42S22]错误核心原因是：current_qt是通过Batch模型的withCurrentQt scope动态添加的虚拟字段，数据库的hospital_batches表中并不存在这个列。你在Equipment的scope子查询中直接引用sum(current_qt)，相当于让数据库去查询一个不存在的物理列，自然会报错。

要解决这个问题，需要把计算current_qt的逻辑直接嵌入到Equipment的统计子查询中，或者通过CTE预计算所有Batch的current_qt后再统计。以下是两种可行方案：

方案一：嵌套子查询直接计算 #

将Batch模型中计算current_qt的逻辑，直接嵌套到Equipment的求和子查询里，让数据库先计算每个Batch的当前数量，再按位置分组求和：

public function scopeWithQts($query)
{
    $locations = $this->getHospitalStandardLocations();

    // 提取计算单个Batch current_qt的子查询（复用逻辑）
    $currentQtSubquery = Transaction::selectRaw('COALESCE(SUM(qt), 0)')
        ->whereColumn('batch_id', 'hospital_batches.id')
        ->where('issued_at', '>=', function ($sub) {
            $sub->select('issued_at')
                ->from('hospital_transactions')
                ->whereColumn('batch_id', 'hospital_batches.id')
                ->where('type', 'COUNT')
                ->latest()
                ->take(1);
        });

    $query->addSelect([
        'qt_inventory' => function ($q) use ($locations, $currentQtSubquery) {
            $q->selectRaw('SUM((' . $currentQtSubquery->toSql() . '))')
                ->from('hospital_batches')
                ->whereNotIn('location_id', $locations->raft)
                ->whereNotIn('location_id', $locations->fak)
                ->whereColumn('equipment_id', 'hospital_equipment.id')
                ->mergeBindings($currentQtSubquery); // 绑定参数避免SQL注入
        },
        'qt_fak' => function ($q) use ($locations, $currentQtSubquery) {
            $q->selectRaw('SUM((' . $currentQtSubquery->toSql() . '))')
                ->from('hospital_batches')
                ->whereIn('location_id', $locations->fak)
                ->whereColumn('equipment_id', 'hospital_equipment.id')
                ->mergeBindings($currentQtSubquery);
        },
        'qt_raft' => function ($q) use ($locations, $currentQtSubquery) {
            $q->selectRaw('SUM((' . $currentQtSubquery->toSql() . '))')
                ->from('hospital_batches')
                ->whereIn('location_id', $locations->raft)
                ->whereColumn('equipment_id', 'hospital_equipment.id')
                ->mergeBindings($currentQtSubquery);
        },
        'qt_total' => function ($q) use ($currentQtSubquery) {
            $q->selectRaw('SUM((' . $currentQtSubquery->toSql() . '))')
                ->from('hospital_batches')
                ->whereColumn('equipment_id', 'hospital_equipment.id')
                ->mergeBindings($currentQtSubquery);
        },
        'qt_check' => function ($q) use ($locations, $currentQtSubquery) {
            $inventorySum = $q->newQuery()
                ->selectRaw('SUM((' . $currentQtSubquery->toSql() . '))')
                ->from('hospital_batches')
                ->whereNotIn('location_id', $locations->raft)
                ->whereNotIn('location_id', $locations->fak)
                ->whereColumn('equipment_id', 'hospital_equipment.id')
                ->mergeBindings($currentQtSubquery)->toSql();
            
            $fakSum = $q->newQuery()
                ->selectRaw('SUM((' . $currentQtSubquery->toSql() . '))')
                ->from('hospital_batches')
                ->whereIn('location_id', $locations->fak)
                ->whereColumn('equipment_id', 'hospital_equipment.id')
                ->mergeBindings($currentQtSubquery)->toSql();

            $sumExpr = $locations->ignore_fak ? $inventorySum : "{$inventorySum} + {$fakSum}";
            return $q->selectRaw($sumExpr);
        }
    ])->withCasts([
        'qt_inventory' => 'integer',
        'qt_fak' => 'integer',
        'qt_raft' => 'integer',
        'qt_total' => 'integer',
        'qt_check' => 'integer'
    ]);

    return $query;
}

关键细节 #

• 使用COALESCE(SUM(qt), 0)避免没有COUNT交易记录时返回NULL，确保求和结果为0
• 通过mergeBindings绑定子查询参数，防止SQL注入并保证查询正确性
• 直接在scope中计算qt_total和qt_check，后续无需额外处理

方案二：使用CTE预计算（可读性更高） #

如果你的数据库支持MySQL 8.0+/PostgreSQL等支持CTE的版本，可以先通过CTE预计算所有Batch的current_qt，再基于这个结果统计Equipment的各位置数量，代码可读性更强：

public function scopeWithQts($query)
{
    $locations = $this->getHospitalStandardLocations();

    // 预计算所有Batch的current_qt
    $query->withExpression('batches_with_current_qt', function ($q) {
        $q->select('hospital_batches.*', 
            Transaction::selectRaw('COALESCE(SUM(qt), 0)')
                ->whereColumn('batch_id', 'hospital_batches.id')
                ->where('issued_at', '>=', function ($sub) {
                    $sub->select('issued_at')
                        ->from('hospital_transactions')
                        ->whereColumn('batch_id', 'hospital_batches.id')
                        ->where('type', 'COUNT')
                        ->latest()
                        ->take(1);
                })
                ->as('current_qt')
        )->from('hospital_batches');
    })
    ->addSelect([
        'qt_inventory' => function ($q) use ($locations) {
            $q->selectRaw('SUM(current_qt)')
                ->from('batches_with_current_qt')
                ->whereNotIn('location_id', $locations->raft)
                ->whereNotIn('location_id', $locations->fak)
                ->whereColumn('equipment_id', 'hospital_equipment.id');
        },
        'qt_fak' => function ($q) use ($locations) {
            $q->selectRaw('SUM(current_qt)')
                ->from('batches_with_current_qt')
                ->whereIn('location_id', $locations->fak)
                ->whereColumn('equipment_id', 'hospital_equipment.id');
        },
        'qt_raft' => function ($q) use ($locations) {
            $q->selectRaw('SUM(current_qt)')
                ->from('batches_with_current_qt')
                ->whereIn('location_id', $locations->raft)
                ->whereColumn('equipment_id', 'hospital_equipment.id');
        },
        'qt_total' => function ($q) {
            $q->selectRaw('SUM(current_qt)')
                ->from('batches_with_current_qt')
                ->whereColumn('equipment_id', 'hospital_equipment.id');
        },
        'qt_check' => function ($q) use ($locations) {
            $inventorySum = $q->newQuery()
                ->selectRaw('SUM(current_qt)')
                ->from('batches_with_current_qt')
                ->whereNotIn('location_id', $locations->raft)
                ->whereNotIn('location_id', $locations->fak)
                ->whereColumn('equipment_id', 'hospital_equipment.id')->toSql();
            
            $fakSum = $q->newQuery()
                ->selectRaw('SUM(current_qt)')
                ->from('batches_with_current_qt')
                ->whereIn('location_id', $locations->fak)
                ->whereColumn('equipment_id', 'hospital_equipment.id')->toSql();

            $sumExpr = $locations->ignore_fak ? $inventorySum : "{$inventorySum} + {$fakSum}";
            return $q->selectRaw($sumExpr);
        }
    ])->withCasts([
        'qt_inventory' => 'integer',
        'qt_fak' => 'integer',
        'qt_raft' => 'integer',
        'qt_total' => 'integer',
        'qt_check' => 'integer'
    ]);

    return $query;
}

关键细节 #

• CTEbatches_with_current_qt预计算所有Batch的当前数量，后续统计直接引用该表的current_qt字段
• 逻辑更清晰，适合复杂场景的维护

修改后，你可以直接基于这些动态字段进行筛选：

$equipment = Equipment::with(['batches','batches.location'])
    ->orderByCode()
    ->withExpiryDates()
    ->withQts()
    ->where('qt_total', '>', 0) // 筛选总数量大于0的设备
    ->when($expiryDates, function ($query) use ($expiringBefore){
        $query->whereHas('batches', function($query) use ($expiringBefore){
            $query->expiringBefore($expiringBefore);
        });
    });

内容的提问来源于stack exchange，提问作者Jan-Willem