douzhang1955
2015-07-19 12:38
浏览 72
已采纳

通过在Table1.id上应用groupBy但在Table2上没有软删除行,使用Laravel Query Builder计算总和?

I want to get all the purchases and their sums and also I don't want to add the amount if payments.deleted_at is not null.

Here are the tables

purchases

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

payments

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

Code

$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();

When I run the code below the 1st result is not included. Result

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

I know why It is not included but the problem is if I remove whereNull('payments.deleted_at') that particular row in payments will also add to the sum.How should I solve this ??

Expected Result

id | name               | total
1  | Gamerzone Book       0
2  | Recipe Book          60 
3  | EngineX Book         0
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • douji8017 2015-07-19 13:53
    已采纳

    In this case your join condition should looks like this:

    ON (payments.booking_id = purchases.id AND payments.deleted_at IS NOT NULL)

    And it is not about WHERE (according to your SELECT). You should use join-closure like this:

    $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();
    

    Just replace

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

    with

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

    and remove this:

    ->whereNull('payments.deleted_at')
    

    it should help.

    已采纳该答案
    打赏 评论

相关推荐 更多相似问题