How to convert a sql query to laravel queryBuilder

Question

I have a sql query as follow:

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

The result of the query is:

Users | Time_Frame

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

I want to convert to eloquent query.

I have tried as like,

$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();

the error is occurred like below;

After diggin around multiple stackoverflow questions and answers this is what I came up with: `$results = DB::table("as_journey_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"))->whereBetween('created_at',array(Carbon::now(), Carbon::now()->subDays(1))->groupBy([DB::raw('DATE(created_at)'),DB::raw('HOUR(created_at)')])->orderBy('Users','desc')->get();` — user3647971, Jul 01 '20 at 17:01
Here are listed links helping with this issue: [whereBetween](https://stackoverflow.com/questions/24824624/laravel-q-where-between-dates) [subDays](https://stackoverflow.com/questions/33361628/how-to-query-between-two-dates-using-laravel-and-eloquent) [CONCAT](https://stackoverflow.com/questions/26306178/laravel-querybuilder-with-join-and-concat) [Select count and groupBy](https://www.itsolutionstuff.com/post/laravel-5-select-with-count-query-with-group-by-exampleexample.html) — user3647971, Jul 01 '20 at 17:06
The error itself is not related to querybuilder but rather with laravel/database configuration. — user3647971, Jul 01 '20 at 17:25
[See here](https://stackoverflow.com/questions/48145384/how-to-disable-only-full-group-by-option-in-laravel) for this error message. — user3647971, Jul 01 '20 at 17:27

score 1 · Accepted Answer · answered Jul 01 '20 at 19:17

Try it like this:

Use where('created_at','>=',now()->subDay(1)) instead of whereRaw("created_at >= NOW() - INTERVAL 1 DAY").
Use groupBy('Time_Frame') since the error is telling you to groupBy the columns you selected which is named as Time_frame not DATE(created_at),HOUR(created_at).
Use latest('Users') instead of orderByRaw("count(as_user_id) DESC") and use the alias you defind (Users) inside the orderBy not 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();

How to convert a sql query to laravel queryBuilder

1 Answers1