php - 按月分组并在 Laravel Eloquent 上对日期进行排序

Question

我有以下查询

$months = \App\DATA::select(
    DB::raw('sum(order_value) as `sums`'),
    DB::raw("DATE_FORMAT(created_at,'%M %Y') as months")
)
    ->where("created_at", ">", \Carbon\Carbon::now()->subMonths(6))
    ->groupBy('months')
    ->get();

对过去 6 个月中每个月的数据进行汇总。问题是它按字母顺序输出日期。

August 2019
July 2019
June 2019
May 2019
November 2019
October 2019
September 2019

我不能用 orderBy 订购它:

->orderBy('created_at')

由于以下问题

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column

如何按日期订购？

Answer 1

尝试使用 max 函数和 ->orderBy('createdAt', 'desc') 。

$months = \App\DATA::select(
       DB::raw('sum(order_value) as `sums`'),
       DB::raw("DATE_FORMAT(created_at,'%M %Y') as months"),
       DB::raw('max(created_at) as createdAt')
)
      ->where("created_at", ">", \Carbon\Carbon::now()->subMonths(6))
      ->orderBy('createdAt', 'desc')
      ->groupBy('months')
      ->get();