Home  >  Q&A  >  body text

How to solve group by and order by issues in Laravel?

I have a database named maintenance which has a field named cost. I now want to get the sum of costs by month and year and sort them in descending order by month and year. Can anyone help me?

This is what I tried

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

I got the collection object below and it works fine. But how can I group them by month and year and then sort them in descending order instead of just grouping by month? Also, I only need the total monthly expenses; I don't need all the 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
}

P粉823268006P粉823268006228 days ago414

reply all(1)I'll reply

  • P粉561438407

    P粉5614384072024-03-29 11:08:04

    The best way is to use a database query to accomplish this, but you will also need a raw query:

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

    reply
    0
  • Cancelreply