sum the result of records where condition in blade

Level 104

I don't understand what the accessor method is supposed to be doing here. What model is this defined on; and why would a single instance of the model calculate the sum from all records?

1 like

Level 5

@tykus cause i want to use this method in many pages and many routs so i insert it into model to invoke it any time i need - and it works correctly but when the records change cause i change the date still sum all where type =1

Level 104

@MahmoudAdelAli it makes no sense - what are you summing?

1 like

Level 5

@tykus it sum the amount of all invoices where type is 1 - which the type 1 mean revenues

Level 104

@MahmoudAdelAli so you have a single arbitrary Invoice model instance; and on that instance you can get the total_revenue property of all Invoice instances (where type is 1)? Why?

Level 5

@tykus :) cause the type 1 mean revenues and type 2 mean Expenses - so i need to sum all revenues and print it and sum all Expenses and print it and subtract the two sum and get the profits - why ? cause the client need that so now the data is correct and every thing is ok but i need to apply all that when date filter work

Level 104

@MahmoudAdelAli now… it is not correct - you are using accessors wrong

1 like

Level 5

@tykus oki , so what's the correct way ?

Level 104

@MahmoudAdelAli a separate query

$total = Invoice::where('type', 1)->sum('amount');

1 like

Level 5

@tykus thank you i appreciate that but imagine i need to use this $total in many different places and controllers ? what the solution here ? and second thing it's not the problem , the issue here the where not work inside blade and i cant repeat the code many times to get the sum where type between 2 dates

  return view('admin.invoices.index',[
            'invoices' => Invoice::latest()->when(request('start_date'), function ($q){
                $start_date = Carbon::parse(request('start_date'))->toDateTimeString() ?? '';
                $end_date = Carbon::parse(request('end_date'))->toDateTimeString() ?? '';
                return $q->whereBetween('created_at',[$start_date,$end_date]);
            }),

and when i use where inside blade

{{$invoices->where('type' , 2)->sum('amount')}}

i got

SQLSTATE[42S22]: Column not found: 1054 Unknown column '' in 'where clause'

Best Answer

Level 5

The solution i found for now - is use latest() with get()

return view('admin.invoices.index',[
            'invoices' => Invoice::latest()->when(request('start_date'), function ($q){
                $start_date = Carbon::parse(request('start_date'))->toDateTimeString() ?? '';
                $end_date = Carbon::parse(request('end_date'))->toDateTimeString() ?? '';
                return $q->whereBetween('created_at',[$start_date,$end_date]);
            })->get()->paginate(10),


        ]);

to decrease the quires and in blade

{{$invoices->where("type", 2)->sum('amount')}}

so here from 11 quire to 1 quires :D

Level 104

@MahmoudAdelAli there's nothing that will convince you to make a separate query to compute the sum; instead you are fetching every Invoice record?

antaugustol