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