how to sum with groupby and withhas in lararvel

Issue

i have 2 tables

  1. invoice (id, service_inv_cat_id, etc..)
  2. 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 the following
enter image description here

but I need to get the total sum of wallet,

I have tried added ->groupBy(‘service_inv_cat_id’)

but it returns the following enter image description here

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

Solution

I assume service_cat and payment belongTo payment ?

$income_details_cat = Invoice::select('invoices.id', 'invoices.service_inv_cat_id', 'payments.amount')
->join('payments', 'invoices.id', '=', 'payments.invoice_id')
->with('service_inv_cat:id,name')
->sum('payments.amount')
->where('payments.type', 3)
->whereYear('payments.paid_date', $year)
->whereMonth('payments.paid_date', $month)
->groupBy('service_inv_cat_id')
->get();

I am a bit lost to be honest but I would re-write your code like this not sure why you have so many withs for the same thing I think you would get the same result as you have now if written like this:

$income_details_cat = Invoice::select('id', 'service_inv_cat_id')
->with('service_inv_cat:id,name', 'payment:id,invoice_id,amount')
->wherHas(['payment' => fn ($q) =>
        $q->where('type', 3)
        ->whereYear('paid_date', $year)
        ->whereMonth('paid_date', $month)
])
->get();

if you can show your relationships a bit clearer and explain what laser is ? I take it wallet is the sum of all amounts grouped by service_inv_cat_id

Answered By – futureweb

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published