Hi im trying to summarize that query but im getting error the query is substract of two sums , and name it as balance for each raw, so i want the total sum of balance of all raws
public function total_balance(){
$collection = customer::get();
$total_balance = $collection->selectRaw('*, (select SUM(taskscharges) from `charges` where `customers`.`id` = `charges`.`customer_id`) - (select SUM(payment) from `charges` where `customers`.`id` = `charges`.`customer_id`) as balance')->sum();
return $total_balance;
}
@baspax1 you didn't take my advice yesterday when I suggested you did not need a Collection in this scenario. You especially do not need a Collection when you are trying to use SQL on it?!?!?
After that, your query makes little sense. What is it you are actually trying to achieve?
Of course i followed your advice simply its different function than yours that is above :
public function total_count()
{
return charge::where('success', 0)->count();
}
On new function i tried without collection but cant make it work, simply i want the sum of that balance field https://imgur.com/Epu37ZL the balance field is result of the query that i wrote inside total_balance Function
Also its a bit dirty to call function each query tasks is there any way to insert that two total_count and total_balance in one function and call with keys ? because the Render is with that way getting bigger and bigger
return view('livewire.customers.show',['customers' => $customers],['total_tasks' => $this->total_tasks()],['total_balance' => $this->total_balance()])
Of course i followed your advice
The other answer used Collections???
What is it you are actually trying to achieve?
What does this balance figure represent; the difference between taskcharges and payment columns on the charges table, and grouped by customer???
Yes, each raw of that difference is named as balance and is grouped by customer, so i want the whole sum of that list of customers balance
So that method should return what, a Collection, keyed by customer_id?
The reason I ask is because having the balances separately from the associated customers seems like an incorrect approach. Why can you not calculate the balances with the collection of customers?
Its little complicated , i tried to run query with the exact way that im running on render to collect the sum of each raw but and summarize to one but the 'balance' doesn't recognize as field , its a result of two fields ,to sum that balance,
on render have this :
$customers = Customer::query()
->search($this->search)
->selectRaw('*, (select SUM(taskscharges) from `charges` where `customers`.`id` = `charges`.`customer_id`) - (select SUM(payment) from `charges` where `customers`.`id` = `charges`.`customer_id`) as balance')
->withSum('charges as total_charges', 'taskscharges')
->withSum('charges as total_payments', 'payment')
->orderBy($this->sortBy, $this->sortDirection)
->paginate($this->perPage);
You can do the aggregation in a subquery and join to the main customers query:
$chargesSubQuery = Charge::selectRaw('customer_id, SUM(taskscharges - payment) as balance')
->groupBy('customer_id');
return Customer::search($this->search)
->select('customers.*', 'balances.balance')
->joinSub($chargesSubQuery, 'balances', fn ($join) => $join->on('customers.id', '=', 'balances.customer_id'))
// the reset of your query
->paginate($this->perPage);
Now, each Customer instance will have a balance property whenever you iterate over the customers Collection in the view.
Yes it worked! that exactly i want , to recognize the balance as balance property,
I make Sum('balance') and finally i get the total balance,
one last think , i inserted that to the previous function is it possible to have to returns or whaterver is it and call it on blade like > taks->total_tasks and tasks->total_balance? as you can see i commented the first return ,
Now i have on render : return view('livewire.customers.show',['customers' => $customers],['tasks' => $this->tasks()])
and on blade call the $tasks, how can i define the first return or the second return to show?
above the function :
public function tasks()
{
//return charge::where('success', 0)->count();
$chargesSubQuery = Charge::selectRaw('customer_id, SUM(taskscharges - payment) as balance')
->groupBy('customer_id');
return Customer::query()
->select('customers.*', 'balances.balance')
->joinSub($chargesSubQuery, 'balances', fn ($join) => $join->on('customers.id', '=', 'balances.customer_id'))
->sum('balance');
}
Why? I have no idea what you are doing now... what is tasks, and why is it returning a total of customer balances. Your domain is a mess?!?!?
Maybe i didn't explain very well, but the result is that working now, as i said i wanted only the sum of TOTAL balance property of balances, I want that queries to run outside of render query,Thats why i created total_balance and total_tasks function, So i merged and renamed to Tasks function and want if it possible to define and call that to fill the blade with that data iwant fill exact that metrics , https://imgur.com/SRoFYeD , the total_balance and the tasks,
@baspax1 I suggest some tutorials from https://www.mysqltutorial.org/mysql-sum/
Let the db work for you. Just example https://laracasts.com/discuss/channels/guides/getpdo-usage but bind parameters.
Please or to participate in this conversation.