I am using laravel and I need to find the eloquent query for: get all the records in a date range, group them by day and sum all the values.
sales table is:
-id
-total_cost
-total_sale
-profit
I have tried this way, it returns the query grouped by day but I can't find the way to add the totals.
$sales = Venta::whereMonth('created_at', Carbon::now()->month)
->where('user_id', Auth::user()->id)
->whereYear('created_at', '=', '2022')
->get()
->groupBy(function($date) {
return Carbon::parse($date->created_at)->format('d');
});
Any suggestions?
CodePudding user response:
You may use:
$from = Carbon::now()->startOfMonth();
$to = Carbon::now()->endOfMonth();
$sales = Venta::query()
->select(
DB::raw('DAY(created_at), SUM(cost) AS total_cost, SUM(sale) AS total_sale')
)
->whereBetween('created_at', [$from, $to])
->where('user_id', Auth::user()->id)
->groupBy(DB::raw('DAY(created_at)'))
->get()
Let me explain, here you are grouping on a database level (before the ->get()) method. This is faster than doing it after the ->get(), where you would have to group in the collection. If you still wanna do it after the ->get() method, you could also use the ->reduce() syntax for the totals.
On another note, it is MUCH faster to do ->whereBetween('created_at', [$from, $to])
instead of
->whereYear('created_at', '=', '2022')
and whereMonth('created_at', Carbon::now()->month
combined because the database has to convert every row before returning it to the client.