Laravel - How to do a specific query with query builder

Question

I'm using Laravel and have a very specific query that I don't know how to implement with query builder.

The query:

SET @c_num=0;

SELECT *, @c_num:=@c_num+1 AS 'COUNT' 
FROM table_name 
WHERE USERID = 2
ORDER BY id

Thank you

Answer 1

You should be able to do something like:

DB::statement(DB::raw('SET @c_num = 0'));

$result = DB::table('table_name')
    ->selectRaw("*, @c_num:=@c_num+1 AS 'COUNT'")
    ->where('userid', 2)
    ->orderBy('id')
    ->get();

Answer 2

$data = DB::table('table_name')
->where('userid',2)
->select('table_name.*',DB::raw('(@c_num:=@c_num+1) Count')
->orderBy('id')
->get();

Answer 3

You can try the following

DB::table('table_name')
    ->selectRaw("*, @c_num:=IF(@c_num, @c_num+1, 1) as 'COUNT'")
    ->where('user_id', 2)
    ->orderBy('id');