CODEWITHSUNDEEP
mysqllaravellaravel-query-builder
user3242861
user3242861

Reputation: 1929

Laravel - How to do a specific query with query builder

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

Upvotes: 0

Views: 55

Answers (3)

Rwd
Rwd

Reputation: 35180

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

Upvotes: 1

chanafdo
chanafdo

Reputation: 5124

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

Upvotes: 0

Nikita
Nikita

Reputation: 437

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

Upvotes: 1

Related Questions