php - laravel 查询构造器聚合函数优化

Question

我想使用laravel构造个聚合查询 SQL语句如下：

select date_format(date_key,'%Y'),sum(sales) as sales,sum(orders) as orders from dataCategory where date_key>='2014-01-01' and date_key <'2017-01-01' group by date_format(date_key,'%Y');

目前是这样构造的：

$array = DataCategory::select(DB::raw("date_format(date_key,'%Y') as date_key,sum(sales) as sales,sum(orders_num) as orders_num,sum(buyer_num) as buyer_num"))
    ->where([['cateid',$cateid],['date_key','>=','2016-06-01']])
    ->groupBy(DB::raw("date_format(date_key,'%Y')"))
    ->get()
    ->toArray();

但这样查询 时间过长。求问怎样优化查询？还是在模型里定义相关方法？作为laravel小白，不是很了解啊。求指导

Answer 1

建议采用laravel中的collection，访问器和修改器修改优化

Answer 2

explain看下语句查询情况

$array = DataCategory::select(DB::raw("date_format(date_key,'%Y') as year,sum(sales) as sales,sum(orders_num) as orders_num,sum(buyer_num) as buyer_num"))
    ->where([['cateid',$cateid],['date_key','>=','2016-06-01']])
    ->groupBy('year'))
    ->get()
    ->toArray();