如何在Laravel Eloquent中实现SELECT嵌套子查询

需求说明 #

将指定原生SQL转换为Laravel Eloquent查询，核心实现satuan与harga列乘积的总和计算。

原生SQL #

SELECT
    rekenings.no_rekening AS no_rekening, 
    rekenings.id AS rekening_id, 
    programs.nama_program AS nama_program, 
    programs.id AS program_id, 
    detail_dpas.nama_barang AS nama_barang, 
    detail_dpas.volume AS volume,
    detail_dpas.satuan AS satuan, 
    detail_spjs.satuan AS satuanSpj, 
    detail_dpas.harga AS harga, 
    detail_spjs.harga AS hargaSpj,
    detail_spjs.catatan AS catatan, 
    detail_dpas.dpa_id AS dpa_id, 
    detail_dpas.id AS detail_dpa_id,
    (
        SELECT SUM(satuan*harga) AS totalspj
        FROM detail_spjs
        WHERE
            rekening_id = rekenings.id
            AND program_id = programs.id
    ),
    (
        SELECT SUM(volume*harga) AS totaldpa
        FROM detail_dpas
        WHERE
            detail_dpas.dpa_id = dpa_id
            AND detail_dpas.id = detail_dpa_id
    )
FROM
    spjs
    JOIN
        detail_spjs
        ON spjs.id = detail_spjs.spj_id
    JOIN
        dpas
        ON dpas.id = detail_spjs.dpa_id
    JOIN
        detail_dpas
        ON dpas.id = detail_dpas.dpa_id
    JOIN
        rekenings
        ON rekenings.id = detail_spjs.rekening_id
    JOIN
        programs
        ON programs.id = detail_spjs.program_id
WHERE
    spjs.id = $id

已尝试的代码（未成功） #

$table_spjs = DB::table('spjs')
    ->join('detail_spjs', 'spjs.id', '=', 'detail_spjs.spj_id')
    ->join('dpas', 'dpas.id', '=', 'detail_spjs.dpa_id')
    ->join('detail_dpas', 'dpas.id', '=', 'detail_dpas.dpa_id')
    ->join('rekenings', 'rekenings.id', '=', 'detail_spjs.rekening_id')
    ->join('programs', 'programs.id', '=', 'detail_spjs.program_id') 
    ->where('spjs.id', '=', $id)   
    ->select(
        'rekenings.no_rekening as no_rekening',
        'rekenings.id as rekening_id',
        'programs.nama_program as nama_program',
        'programs.id as program_id',
        'detail_dpas.nama_barang as nama_barang',
        'detail_dpas.volume as volume',
        'detail_dpas.satuan as satuan', 
        'detail_spjs.satuan as satuanSpj', 
        'detail_dpas.harga as harga',
        'detail_spjs.harga as hargaSpj',
        'detail_spjs.catatan as catatan',
        'detail_dpas.dpa_id as dpa_id',
        'detail_dpas.id as detail_dpa_id',
        DB::Raw('select sum(satuan*harga) as totalspj from detail_spjs where rekening_id=rekenings.id and program_id=programs.id'),
        DB::Raw('select sum(volume*harga) as totaldpa from detail_dpas where detail_dpas.dpa_id=dpa_id and detail_dpas.id=detail_dpa_id')
    )    
    ->get();

解决方案 #

方案1：修正DB::Raw写法（保持原生查询结构） #

原代码的问题在于子查询未正确包裹、字段存在歧义，修正后的代码如下：

$table_spjs = DB::table('spjs')
    ->join('detail_spjs', 'spjs.id', '=', 'detail_spjs.spj_id')
    ->join('dpas', 'dpas.id', '=', 'detail_spjs.dpa_id')
    ->join('detail_dpas', 'dpas.id', '=', 'detail_dpas.dpa_id')
    ->join('rekenings', 'rekenings.id', '=', 'detail_spjs.rekening_id')
    ->join('programs', 'programs.id', '=', 'detail_spjs.program_id') 
    ->where('spjs.id', '=', $id)   
    ->select([
        'rekenings.no_rekening as no_rekening',
        'rekenings.id as rekening_id',
        'programs.nama_program as nama_program',
        'programs.id as program_id',
        'detail_dpas.nama_barang as nama_barang',
        'detail_dpas.volume as volume',
        'detail_dpas.satuan as satuan', 
        'detail_spjs.satuan as satuanSpj', 
        'detail_dpas.harga as harga',
        'detail_spjs.harga as hargaSpj',
        'detail_spjs.catatan as catatan',
        'detail_dpas.dpa_id as dpa_id',
        'detail_dpas.id as detail_dpa_id',
        // 修正子查询：加括号包裹、表别名避免歧义、指定字段所属表
        DB::raw('(SELECT SUM(ds.satuan * ds.harga) FROM detail_spjs ds WHERE ds.rekening_id = rekenings.id AND ds.program_id = programs.id) AS totalspj'),
        DB::raw('(SELECT SUM(dd.volume * dd.harga) FROM detail_dpas dd WHERE dd.dpa_id = detail_dpas.dpa_id AND dd.id = detail_dpas.id) AS totaldpa')
    ])    
    ->get();

修正要点：

• 子查询用括号包裹，并为结果指定别名（AS totalspj/AS totaldpa）
• 子查询表添加别名（ds/dd），避免与主查询字段冲突
• 明确指定计算字段所属表（如ds.satuan * ds.harga）
• 修正第二个子查询的where条件，明确关联主查询的detail_dpas表字段

方案2：使用Eloquent模型关联（面向对象写法） #

如果已定义对应模型，可通过关联预加载+遍历计算实现：

1. 定义模型关联

// Spj模型
class Spj extends Model
{
    public function detailSpjs()
    {
        return $this->hasMany(DetailSpj::class, 'spj_id');
    }
}

// DetailSpj模型
class DetailSpj extends Model
{
    public function rekening()
    {
        return $this->belongsTo(Rekening::class);
    }

    public function program()
    {
        return $this->belongsTo(Program::class);
    }

    public function dpa()
    {
        return $this->belongsTo(Dpa::class);
    }
}

// Dpa模型
class Dpa extends Model
{
    public function detailDpas()
    {
        return $this->hasMany(DetailDpa::class);
    }
}

// Rekening、Program、DetailDpa模型默认继承Model即可，无需额外关联

2. 执行查询

$spjData = Spj::with(['detailSpjs.rekening', 'detailSpjs.program', 'detailSpjs.dpa.detailDpas'])
    ->where('id', $id)
    ->get()
    ->map(function ($spj) {
        return $spj->detailSpjs->map(function ($detailSpj) {
            return $detailSpj->dpa->detailDpas->map(function ($detailDpa) use ($detailSpj) {
                // 计算totalspj
                $totalspj = DetailSpj::where('rekening_id', $detailSpj->rekening_id)
                    ->where('program_id', $detailSpj->program_id)
                    ->sum(DB::raw('satuan * harga'));
                
                // 计算totaldpa
                $totaldpa = DetailDpa::where('dpa_id', $detailDpa->dpa_id)
                    ->where('id', $detailDpa->id)
                    ->sum(DB::raw('volume * harga'));
                
                return [
                    'no_rekening' => $detailSpj->rekening->no_rekening,
                    'rekening_id' => $detailSpj->rekening_id,
                    'nama_program' => $detailSpj->program->nama_program,
                    'program_id' => $detailSpj->program_id,
                    'nama_barang' => $detailDpa->nama_barang,
                    'volume' => $detailDpa->volume,
                    'satuan' => $detailDpa->satuan,
                    'satuanSpj' => $detailSpj->satuan,
                    'harga' => $detailDpa->harga,
                    'hargaSpj' => $detailSpj->harga,
                    'catatan' => $detailSpj->catatan,
                    'dpa_id' => $detailDpa->dpa_id,
                    'detail_dpa_id' => $detailDpa->id,
                    'totalspj' => $totalspj,
                    'totaldpa' => $totaldpa
                ];
            });
        })->flatten();
    })->flatten();

说明：该写法更符合Laravel的面向对象设计，若数据量较大，可通过添加子查询到关联中优化N+1查询问题。

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