Laravel group by and order by, how to solve?-CodePudding

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
}

CodePudding user response：

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();