Aug 24, 2022
0
Level 1
how to sum with groupby and withHas in lararvel
0
i have 2 tables
invoice (id, service_inv_cat_id, etc..) payment (id, invoice_id, amount) I need to get the sum of amount in payment with groupby (service_inv_cat_id)
here is my code
$income_details_cat = Invoice::select('id', 'service_inv_cat_id')
->with(['service_inv_cat' => function ($q) {
$q->select('id', 'name');}])
->with(['payment' => function ($q) use ($year, $month){
$q->select('id','invoice_id', 'amount')
->whereYear('paid_date', $year)
->whereMonth('paid_date', $month)
;}])
->whereHas('payment', function ($q) use ($year,$month) {
return $q->where('type', 3)
->whereYear('paid_date', $year)
->whereMonth('paid_date', $month)
;})
->withSum(['payment' => function ($query) use ($year, $month){
$query->whereYear('paid_date', $year)
->whereMonth('paid_date', $month);
}], 'amount')
->get();
It returns
laser 80
wallet 200
wallet 100
wallet 50
but I need to get the total sum of wallet,
i have tried to add ->groupBy('service_inv_cat_id')
but it returns the following
laser 80
wallet 200
it returns the sum of payment of only one record, ex wallet has higher than 200 in total with many invoices as you can see in pic 1 but its returns only one
Please or to participate in this conversation.