Using user-defined MySQL variables in Laravel

I attempted to do the following in Laravel:

DB::query("SET @rank:=0; UPDATE scores SET rank=@rank:=@rank+1 WHERE game_id=4 ORDER BY score DESC");

That is valid MySQL code, and works if you type it directly into MySQL via the command line, but Laravel was throwing an error:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL 
syntax; check the manual that corresponds to your MySQL server version for the right 
syntax to use near 'UPDATE scores SET rank=@rank:=@rank+1 WHERE game_id=4 ORDER BY' at line 1

SQL: SET @rank:=0; UPDATE scores SET rank=@rank:=@rank+1 WHERE game_id=4 ORDER BY score DESC

Bindings: array (
)

I attempted to separate the two queries:

DB::query("SET @rank:=0");
DB::query("UPDATE scores SET rank=@rank:=@rank+1 WHERE game_id=4 ORDER BY score DESC");

But Laravel still threw an error:

Method [] is not defined on the Query class.

DB::raw() to the rescue!

Solution:

DB::query(DB::raw("SET @rank:=0"));
DB::query("UPDATE scores SET rank=@rank:=@rank+1 WHERE game_id=4 ORDER BY score DESC");
0 comments

Leave a Comment