CODEWITHSUNDEEP
mysqllaraveleloquentquery-builder
Furkan ozturk
Furkan ozturk

Reputation: 722

How to convert a sql query to laravel queryBuilder

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

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;

enter image description here

Upvotes: 0

Views: 211

Answers (1)

Makdous
Makdous

Reputation: 1433

Try it like this:

  1. Use where('created_at','>=',now()->subDay(1)) instead of whereRaw("created_at >= NOW() - INTERVAL 1 DAY").
  2. 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).
  3. 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();

Upvotes: 1

Related Questions