get records grouped by day and sum fields

user_14505836 0
Viewed 58

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?

laravel
2 Answers

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.

user_16877660

I suggest using the following code: ‌

$sales = Venta::query()->selectRaw('DAY(created_at), SUM(cost) AS total_cost, SUM(sale) AS total_sale')
    ->whereBetween('created_at', [Carbon::now()->startOfMonth(),  Carbon::now()->endOfMonth()])
    ->where('user_id', Auth::user()->id)
    ->groupBy(DB::raw('DAY(created_at)'))
    ->get();
Admin1
Related