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
– user3647971, Commented Jul 1, 2020 at 17:01
Here are listed links helping with this issue: whereBetween subDays CONCAT Select count and groupBy — user3647971
– user3647971, Commented Jul 1, 2020 at 17:06
The error itself is not related to querybuilder but rather with laravel/database configuration. — user3647971
– user3647971, Commented Jul 1, 2020 at 17:25

Makdous · Accepted Answer · 2020-07-01 19:17:03Z

1

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

answered Jul 1, 2020 at 19:17

Makdous

1,4331 gold badge15 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to convert a sql query to laravel queryBuilder

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related