Laravel group by and order by, how to solve?

December 20, 2022

I have a database called maintenance that has a field called cost. I now want to get the sum the costs by month and year and sort them in descending order by month and year. Could someone please help me with that?

Here what I have tried

$MaintenanceStats = Maintenance::oldest()
    ->get()
    ->groupBy(function($val) {
         return Carbon::parse($val->from)->format('F');
     })
     ->take(7);

I got this collection object bellow, which works fine. But how can I group them by both month and year and then order them in descending order rather than just grouping by month? Plus, I only need the monthly total cost; I don’t need all maintenance records.

Illuminate\Database\Eloquent\Collection {#1517 ▼ // app\Http\Controllers\GeneralController.php:40
  #items: array:5 [▼
    "September" => Illuminate\Database\Eloquent\Collection {#1452 ▶}
    "July" => Illuminate\Database\Eloquent\Collection {#1530 ▶}
    "January" => Illuminate\Database\Eloquent\Collection {#1519 ▶}
    "November" => Illuminate\Database\Eloquent\Collection {#1520 ▶}
    "December" => Illuminate\Database\Eloquent\Collection {#1521 ▶}
  ]
  #escapeWhenCastingToString: false
}

>Solution :

The best way is to do it with the DB queries, but you’ll need a raw query as well:

$maintenanceStats = Maintenance::selectRaw("year(`from`) AS year, month(`from`) AS month, sum(cost) AS cost_total")
   ->groupByRaw("year(`from`)")
   ->groupByRaw("month(`from`)")
   ->orderBy('year')
   ->orderBy('month')
   ->get();