I’ve been stumped on this and tried everything I can think of but I can’t get Laravel to execute a working mysql query to reorder an ordering column when deleting a row.

Here’s a sample simplified query, which works directly in mysql:

SET @number = 3; UPDATE images SET order = (@number := @number + 1) WHERE id >2 AND project_id = 10

Laravel code that doesn’t update the database but throws no errors:

$sql = 'SET @number = 3; UPDATE images SET order = (@number := @number + 1) WHERE id >2 AND project_id = 10';
DB::raw($sql);

Other things I’ve tried which throw errors including saying there’s an error in the query:

DB::unprepared($sql);
DB::statement(DB::raw($sql);
DB::statement($sql);
DB::update($sql);
DB::select($sql);

I’ve also tried splitting it the statements with no luck:

DB::statement("SET @number = 3;");
$sql = 'UPDATE images SET order = (@number := @number + 1) WHERE id >2016 AND project_id = 10';
$update = DB::update($sql);

I tried a few other things which I didn’t log in Git but I had no luck getting any of it to work. I asked for help on the forums and people told me to use Laravel’s Eloquent model to update rows one by one which is pretty inefficient. You get extra trips over the network (especially important when the code is not on the same server as the database server since you can get dropped calls), extra connections to the server, the extra overhead of processing the query on the server and in the database, etc instead of just having mysql make the update.

The recommended solution may not seem like a big deal but I’ve run into issues where client connections have been dropped mid-update and left a table partially renumbered.

As a last resort and workaround, I opted to use a stored procedure to accomplish my goal. That presented its own can of worms as searching for examples on how to execute stored procedures in Laravel 5.x was also not easy. There’s nothing in the documentation and all the examples I found didn’t work.

Here’s what worked for me with Laravel 5.6, with mySQL 5.6.38:

DB::statement('call spRenumberComments(?, ?)',  [$id, $projectid);