php - 返回按日期排序的数据

Question

我有一个函数可以返回每天的销售额。这种方法的问题是我希望按日期存储数据，但我按以下顺序获取它们:

01-Dec
02-Dec
03-Dec
03-Nov
04-Nov
05-Nov
etc.

我明白为什么会这样，但我不确定如何解决。我可以将 subMonth(1) 替换为 startofmonth，这将部分解决我的问题，但这不是我想要的。相反，我想退回最近 30 天订购的商品。

    return DB::table('sales')
        ->select(\DB::RAW('DATE_FORMAT(created_at, "%d-%M") as date'), \DB::raw('COUNT(*) as count'))
        ->where('created_at', '>=', Carbon::now()->subMonth(1))
        ->orderBy('date')
        ->groupBy('date')
        ->get(['date', 'count'])
        ->keyBy('date')
        ->transform(function ($data) {
            return $data->count;
        });

我也试过 orderBy('created_at') 但它给了我下面的错误，我想避免改变 sql 模式。

Syntax error or access violation: 1055 Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'x.sales.created_at' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

编辑:按照要求，这是返回语句的sql查询

select DATE_FORMAT(created_at, "%d-%M") as date, COUNT(*) as count from `sales` where `created_at` >= ? group by `date` order by `date` asc

Answer 1

我不太了解您的框架查询语法。但您可以将多一列 DATE_FORMAT(created_at, "%Y%m%d") AS order_column 并应用顺序，并在列“order_column”上分组并在显示数据时使用列“data”。

select DATE_FORMAT(created_at, "%Y%m%d") AS order_column, DATE_FORMAT(created_at, "%d-%M") as date, COUNT(*) as count from `sales` where `created_at` >= ? group by `order_column` order by `order_column` asc



return DB::table('sales')
        ->select(\DB::RAW('DATE_FORMAT(created_at, "%Y%m%d") AS order_column'),\DB::RAW('DATE_FORMAT(created_at, "%d-%M") as date'), \DB::raw('COUNT(*) as count'))
        ->where('created_at', '>=', Carbon::now()->subMonth(1))
        ->orderBy('order_column')
        ->groupBy('order_column')
        ->get(['date', 'count'])
        ->keyBy('date')
        ->transform(function ($data) {
            return $data->count;
        });