others - laravel - Eloquent里用GROUP BY和SUM来表达sql请求

我只想在Laravel应用程序中做一个简单的sql请求。

我不太喜欢Eloquent。

这是我要用eloquent的SQL请求:


select user_id, project_id, sum(hours)


FROM time_entries


WHERE spent_on BETWEEN '2018-04-10' AND '2018-12-10'


GROUP BY user_id, project_id



我一直在尝试:


TimeEntries::whereBetween('spent_on', [($request->input('debut')), ($request->input('fin'))])->groupBy('user_id')->sum('hours');



时间:

使用selectRaw()


TimeEntries::whereBetween('spent_on', [($request->input('debut')), ($request->input('fin'))])->groupBy(['user_id', 'project_id'])->selectRaw('user_id , project_id, sum(hours) as sum')->get();



你可以使用Eloquent的selectRaw函数,如下所示:


TimeEntries::selectRaw('user_id, project_id, sum(hours)')


 ->whereBetween(


 'spent_on',


 [ ($request->input('debut')), ($request->input('fin')) ]


 )


 ->groupBy('user_id', 'project_id');



但是,要注意,不要在selectRaw字符串参数中绑定变量,它可能会导致SQL注入问题,因为它传递了转义检查,

以下查询将工作:


$query = TimeEntries::query();


$query->selectRaw('user_id', 'project_id', sum(hours))


->whereRaw("spent_on >= '2018-04-10' and spent_on <= '2018-12-10'")


->groupBy('user_id')


->groupBy('project_id');



$result = $query->get();



Model-

...