laravel 常用的操作

select([DB::raw('DATE_FORMAT(starting_time,"%Y-%m-%d") as date'), DB::raw('COUNT(distinct(user_id)) as value')])->groupBy('date')->orderBy('date')->get()

按时间分组加去重

select([DB::raw('any_value(project_id) as project_id'), 'quality', DB::raw('count(*) as total')])->groupBy('quality')->orderByDesc('total')->get()

any_value 可以查询想要的字段(因为严格模式下,非group字段不能被查询)

select([DB::raw('any_value(user_id) as user_id'), 'project_id', DB::raw('sum(amount_all) as value')])

sum统计和 count统计数

WhereRaw('FIND_IN_SET(' . $user_id . ',manager_id)')

find_in_set 查询是否存在1,2,3,4 类似格式

            $list = $record->paginate($request->input('limit',10));
            $list->transform(function($item,$index){
                $arr = $item->toArray();
                $arr['coupon']['begin_at'] = Carbon::parse($arr['coupon']['begin_at'])->format('Y-m-d');
                $arr['coupon']['expire_at'] = Carbon::parse($arr['coupon']['expire_at'])->format('Y-m-d');
                $arr['created_at'] = Carbon::parse($arr['created_at'])->format('Y-m-d');
                return $arr;
            });

查询后处理集合数据