Laravel 5.8嵌套查询实现月度库存报表合并及数值显示问题

当然可以把这两个查询合并成单表查询，同时解决qty_balance显示为Collection对象的问题。我给你两种方案，优先推荐第一种，因为它能避免N+1查询的性能问题，还能一次性拿到所有数据：

方案一：合并为单查询（推荐） #

直接在主查询的SELECT语句中嵌入子查询，一次性获取当月交易数据和前期余额，这样就不用循环去查每个单元的余额了：

public function p_report(Request $request) { 
    $targetMonth = $request->month;
    $targetYear = $request->year;

    $report = DB::table('unit as u') 
        ->select(
            'u.id',
            'name_u', 
            'price', 
            DB::raw('sum(qty_in) as vol_in'), 
            DB::raw('sum(price*qty_in) as qty_ins'), 
            DB::raw('sum(qty_out) as vol_out'), 
            DB::raw('sum(price*qty_out) as qty_outs'), 
            DB::raw('sum(qty_in)-sum(qty_out) as vol_end'), 
            DB::raw('sum(price*qty_in)-sum(price*qty_out) as qty_end'),
            // 子查询获取当前年月之前的余额，用COALESCE处理无数据的情况
            DB::raw('(
                SELECT COALESCE(SUM(price*qty_in) - SUM(price*qty_out), 0) 
                FROM transaction t_prev 
                WHERE t_prev.unit_id = u.id 
                AND YEAR(t_prev.date_transaction) = '.$targetYear.' 
                AND MONTH(t_prev.date_transaction) < '.$targetMonth.'
                AND t_prev.status = 1
            ) as qty_balance')
        ) 
        ->leftJoin('transaction as t', 't.unit_id', '=', 'u.id') 
        ->where('t.status', 1) // 明确指定是transaction表的status，避免字段冲突
        ->where('u.type_unit', '=', $request->type_unit) 
        ->whereMonth('t.date_transaction', '=', $targetMonth) 
        ->whereYear('t.date_transaction', '=', $targetYear) 
        ->groupBy(['u.id','name_u','price']) 
        ->orderBy('name_u') 
        ->get(); 

    return view ('report/p_report', compact('report')); 
}

关键说明： #

1. 子查询获取余额：通过关联当前unit.id，直接查询该单元在目标年月之前的交易余额，一次搞定所有数据，避免循环查询的性能损耗。
2. COALESCE处理空值：如果某个单元没有前期交易，会返回0而不是NULL，更符合报表的展示需求。
3. 明确表别名：给status条件加上t.前缀，避免和unit表的同名字段冲突（如果有的话）。
4. 直接返回数值：子查询返回的是单个数值，所以qty_balance会直接显示数字，不再是Collection对象。

方案二：修复原循环查询的Collection问题 #

如果你暂时不想调整主查询结构，也可以修改循环内的查询逻辑，直接获取数值而非Collection：

public function p_report(Request $request) { 
    $report = DB::table('unit as u') 
        ->select('u.id','name_u', 'price', DB::raw('sum(qty_in) vol_in'), DB::raw('sum(price*qty_in) qty_ins'), DB::raw('sum(qty_out) vol_out'), DB::raw('sum(price*qty_out) qty_outs'), DB::raw('sum(qty_in)-sum(qty_out) vol_end'), DB::raw('sum(price*qty_in)-sum(price*qty_out) qty_end') ) 
        ->leftJoin('transaction as t', 't.unit_id', '=', 'u.id') 
        ->where('status',1) 
        ->where('type_unit', '=' , $request->type_unit) 
        ->whereMonth('date_transaction','=', $request->month) 
        ->whereYear('date_transaction', '=' , $request->year) 
        ->groupBy(['u.id','name_u','price']) 
        ->orderBy('name_u') 
        ->get(); 
    
    foreach ($report as $reports) { 
        // 用value()直接获取字段值，没有数据返回null
        $balance = DB::table('unit as u') 
            ->select( DB::raw('sum(price*qty_in)-sum(price*qty_out) qty_balance') ) 
            ->leftJoin('transaction as t', 't.unit_id', '=', 'u.id') 
            ->where('u.id', '=' , $reports->id) 
            ->whereMonth('date_transaction','<', $request->month) 
            ->whereYear('date_transaction', '=' , $request->year) 
            ->where('t.status', 1) // 同样明确表别名
            ->value('qty_balance'); 

        // 处理null情况，设为0
        $reports->qty_balance = $balance ?? 0; 
    } 
    
    return view ('report/p_report', compact('report')); 
}

关键说明： #

• 使用->value('qty_balance')替代->get()，它会直接返回查询结果中第一个记录的qty_balance字段值，而不是Collection对象。
• 用?? 0处理没有前期交易的情况，确保显示0而不是null。

内容的提问来源于stack exchange，提问作者Arz