如何将指定SQL子查询改写为Laravel Eloquent查询语句？

原始SQL语句 #

SELECT  COUNT(*) AS CNT FROM CashTransaction AS A 
  WHERE A.Flag=0 
    AND A.IsCancel=0 
    AND A.Amount-ifnull((
        SELECT sum(ifnull(Bills.Amount,0)) 
          FROM CashTransaction AS Bills 
          WHERE A.ContractID=Bills.ContractID 
            AND Bills.Flag=2 
            AND Bills.IsCancel=0),0) > 0

已完成的部分Eloquent代码 #

$commissionDepositCount = CashTransaction::where('Flag', 0)
        ->where('IsCancel',0)

    ->where('Amount','>',function($query){
            $query->from('CashTransaction')
                
    })

    ->when($user->Level < 5,function($query) use($user){
        $query->where('SenderID',$user->id);
        })
    ->count()

需要实现的子查询逻辑 #

需将以下SQL逻辑转换为Eloquent写法：

A.Amount-ifnull((
        SELECT sum(ifnull(Bills.Amount,0)) 
          FROM CashTransaction AS Bills 
          WHERE A.ContractID=Bills.ContractID 
            AND Bills.Flag=2 
            AND Bills.IsCancel=0),0) > 0

完整解决方案 #

这里提供两种实现方式，都能满足需求：

方式一：直接使用whereRaw匹配原始SQL逻辑 #

$commissionDepositCount = CashTransaction::where('Flag', 0)
    ->where('IsCancel', 0)
    ->whereRaw('`Amount` - IFNULL((SELECT SUM(IFNULL(`Amount`, 0)) FROM `CashTransaction` AS `Bills` WHERE `Bills`.`ContractID` = `CashTransaction`.`ContractID` AND `Bills`.`Flag` = 2 AND `Bills`.`IsCancel` = 0), 0) > 0')
    ->when($user->Level < 5, function ($query) use ($user) {
        $query->where('SenderID', $user->id);
    })
    ->count();

方式二：用Eloquent闭包子查询+参数绑定 #

$commissionDepositCount = CashTransaction::where('Flag', 0)
    ->where('IsCancel', 0)
    ->where(function ($query) {
        // 构造子查询
        $subQuery = CashTransaction::selectRaw('SUM(IFNULL(`Amount`, 0))')
            ->whereColumn('ContractID', 'CashTransaction.ContractID')
            ->where('Flag', 2)
            ->where('IsCancel', 0);
        
        // 主查询条件：Amount减去子查询结果（空值则取0）大于0
        $query->whereRaw('`Amount` - IFNULL(?, 0) > 0', [$subQuery]);
    })
    ->when($user->Level < 5, function ($query) use ($user) {
        $query->where('SenderID', $user->id);
    })
    ->count();

说明 #

• 方式一完全贴合原始SQL结构，直观易懂，适合快速迁移
• 方式二将子查询独立出来，通过参数绑定避免SQL注入风险，更符合Laravel的最佳实践
• 两种方式都处理了空值场景，确保计算不会出现异常

内容的提问来源于stack exchange，提问作者허재영