如何将SQL查询转换为Laravel queryBuilder

Furkan Ozturk：

我有一个SQL查询如下：

SELECT count(as_user_id) as Users,
 CONCAT( HOUR(created_at), ' to ', CONCAT( HOUR(created_at), ':59:59' ) ) as Time_Frame
FROM content_impression
WHERE created_at >= NOW() - INTERVAL 1 DAY
GROUP BY 
 DATE(created_at), 
 HOUR(created_at)
ORDER BY count(as_user_id) DESC

查询的结果是：

用户| 大体时间

2 | 10至10:59:59
2 | 9至9:59:59
1 | 11至11:59:59
1 | 12至12:59:59

我想转换为雄辩的查询。

我尝试过像

$results = DB::table("content_impression")
        ->select(DB::raw(" count(as_user_id) as Users,CONCAT( HOUR(created_at), ' to ', CONCAT( HOUR(created_at), ':59:59' ) ) as Time_Frame"))
        ->whereRaw("created_at >= NOW() - INTERVAL 1 DAY")
        ->groupByRaw("DATE(created_at),HOUR(created_at)")->orderByRaw("count(as_user_id) DESC")->get();

发生错误，如下所示；

Makdous：

像这样尝试：

使用where('created_at','>=',now()->subDay(1))代替whereRaw("created_at >= NOW() - INTERVAL 1 DAY")。
使用groupBy('Time_Frame')该命令是因为错误告诉groupBy您所选的列，其名称为Time_frame not DATE(created_at),HOUR(created_at)。
使用latest('Users')代替，orderByRaw("count(as_user_id) DESC")并Users在orderBynot中使用defind（）的别名count(as_user_id)。

$results = DB::table("content_impression")
               ->selectRaw(" count(as_user_id) as Users,CONCAT( Hour(created_at), ' to ', CONCAT( Hour(created_at), ':59:59' ) ) as Time_Frame")
               ->where('created_at','>=',now()->subDay(1))
               ->groupBy('Time_Frame')->latest('Users')->get();

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。