通过在表1上应用groupBy，使用Laravel Query Builder计算sum.id，但没有软删除表2上的行 - Calculate sum using Laravel Query Builder by applying groupBy on Table1.id but without soft deleted rows on Table2?

Calculate sum using Laravel Query Builder by applying groupBy on Table1.id but without soft deleted rows on Table2?

我想获得所有的购买和它们的总额，而且如果payments.deleted_at不为空，我也不想添加金额。

这些是表格

购买

id | name
1  | Gamerzone Book
2  | Recipe Book
3  | EngineX Book

支付

id  | purchase_id  | amount  | deleted_at
1     1              100       2015-06-12 11:00:00
2     2              50        NULL
2     2              10        NULL

代码

$query = DB::table('purchases')
        ->select(['purchases.*',
                   DB::raw("IFNULL(sum(payments.amount),0) as total")
            ])
         ->leftJoin('payments','payments.purchase_id','=','purchases.id')
         ->whereNull('payments.deleted_at')
         ->groupBy('purchases.id')->get();

当我运行下面的代码时，第一个结果不包括在内。结果

id | name               | total
2  | Recipe Book          60 
3  | EngineX Book         0

我知道为什么它不包括，但问题是，如果我删除whereNull('payments.deleted_at')支付中的特定行也会加到总和上。我该如何解决这个问题??

预期结果

id | name               | total
1  | Gamerzone Book       0
2  | Recipe Book          60 
3  | EngineX Book         0

在这种情况下，您的连接条件应该如下所示:

(支付。Booking_id =购买。id AND payments.deleted_at IS NOT NULL)

它不是关于WHERE(根据你的SELECT)。您应该像这样使用join-closure:

$query = DB::table('purchases')
->select(['purchases.*', DB::raw("IFNULL(sum(payments.amount),0) as total")])
->leftJoin('payments', function($join) {
    $join->on('payments.booking_id', '=', 'purchases.id');
    $join->on('payments.deleted_at', 'IS', DB::raw('NOT NULL')); 
})
->groupBy('purchases.id')->get();

只是替换

->leftJoin('payments','payments.booking_id','=','purchases.id')

->leftJoin('payments', function($join) {
    $join->on('payments.booking_id', '=', 'purchases.id');
    $join->on('payments.deleted_at', 'IS', DB::raw('NOT NULL')); 
})

并删除此:

->whereNull('payments.deleted_at')

应该有帮助。