How to renumber order by column in database with Laravel

You need to do something like this.

$order = 1;

$records = Model::all();

foreach($records as $row) {
    $row->order = $order;
    $row->update();
    $order++;
}

With the proper search criteria of course.

Hi @Tray2, wouldn't that make multiple SQL calls to update one row at a time?

Yes it would.

There is no other way as far as I know to do this since you need to increment betweeen each update.

The SQL query works in mysql, so there's got to be a way to run it through laravel.

I dont understand your thinking... works in tinker but not in Laravel... its the same thing!

Also, you rewrite the order field but on what basis? How do you know what row gets 3, 4 etc

I don't get it either as that's my understanding as well.

When I put it in a controller, it doesn't seem to update anything. I've debugged it by adding breakpoints and making sure it executes with no errors, but the database is not updated. When I do it in tinker, it updates as expected.

I'm deleting a row and need to renumber the rest. So if id row 3 gets deleted, it starts row 4 becomes 3 and it goes up from there. I have an order by in my actual code. I simplified the example just because I can't get this portion working.

then you need to reorder all the rows starting at 1 and add order by to the update query

Having had the same situation in the past, I've not found reason to renumber since gaps are rarely an issue

@Snapey, I simplified the example. The real code has the order by and additional filter parameters.

I show the numbers on the frontend in the UI so the gaps do matter, which is an app requirement. Did you update the rows 1 by 1 in Laravel or in mysql like I'm trying to do?

In case anyone runs into this issue, I solved it by putting the sql code into a stored procedure and calling the stored procedure through Laravel. This way, you only need one database call and mysql handles updating the rows.

I simplified the example. The real code has the order by and additional filter parameters.

Risky because you are not actually presenting proper use case and the problem may be with the stuff you thought was irrelevant.

Its also annoying since we're thinking, well how is that supposed to work, instead of thinking about the problem.

Yes I would iterate over the collection in a heartbeat and not sweat it, unless of course you are resequencing 1000's of rows in which case I would apply the number on the front end as a local loop count, not from the database.

What you have done there is instead of letting the webserver access the data base x times you let the database hit itself that amount of times. Sure you save a few milliseconds but you make it harder to maintain since you now have logic in more than one place.

Thank you both for the feedback!

@Snapey, I apologize for whatever was confusing. I was just looking for help running the sample query in the example.

@Tray2 , I agree with the logic statement, and would prefer to call the query in Laravel and not a stored proc. I've done this same exact thing in .NET and Java in the past with no issues but I just can't seem to make it work in Laravel. I just can't figure out what I'm missing here and was hoping someone would know how to run that query using Laravel. I thought a DB::raw or DB::statement query should do it based on the docs but it doesn't work for me and I don't know why.

I prefer using SQL to re-number over the language based on past experience performance tuning. The DB update is faster than a PHP call and it eliminates the extra network calls from across the servers.

If anyone knows how to run the query in Laravel directly, I'd be much obliged for the help.

I don't think laravel supports pl/sql in any methods that communicates with the database.

Found this regarding Oracle DB might be adaptable to MySQL

$val1 = 20; 

$result = DB::connection('ora_db')->select(
    DB::raw("exec PRW_GENERA_FACTURA(:val1)"), array ('val1' => $val1)
);

return $result;

Thanks, Tray. I was above to run the stored proc with parameters using DB::statement like so:

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

This works with Laravel 5.6 and mysql 5.6.38.

I also put the stored proc into a migration which means I can maintain it through Laravel.

@gregoryvarghese here's how you do it in one go, without the need for any DB logic:

$sql = <<<SQL
UPDATE images, (select @number := 3) as var -- inline variable setting 
SET `order` = (@number := @number + 1) 
WHERE id >2016 AND project_id = 10
SQL;

DB::update($sql);

Thanks, @JarekTkaczyk! I didn't even think about inlining the variable. I'll try that tonight and let you know.

just make sure you don't end up with any injection attacks by not using bindings.

@gregoryvarghese Hi.

I am having the a similar issue. Do you mind to share some of your code if you still have it?

public function sortCategories(Request $request) {
        $move = $request->desired_position > $request->current_position ? 'down' : 'up';
        
        \DB::select( "UPDATE product_categories SET display_order = 0 WHERE display_order = ". $request->current_position ." AND id = ". $request->category_id  );

        if ($move == 'down') {
            \DB::select(" UPDATE product_categories SET display_order = (display_order - 1) WHERE display_order > ".$request->current_position." AND display_order <= ". $request->desired_position ."  AND id =  " .$request->category_id);

        }

        if ( $move == 'up') {
            \DB::select("UPDATE product_categories SET display_order = (display_order + 1) WHERE display_order >= ". $request->desired_position." AND display_order < ".$request->current_position." AND id = " .$request->category_id);

        }

        \DB::select( "UPDATE product_categories SET display_order = ". $request->desired_position." WHERE display_order = 0 AND id = " . $request->category_id);
        
    }

The above code changes the display_order column in my DB

The issue is that on the front end Laravel does not listen to this code

$categories = ProductCategory::with('products', 'subcategory')->orderBy('display_order', 'desc')->get();

@acedesigns is your frontend getting its data by json?

No it is not. I get my data from a normal Laravel collection. And I display it in a table and loop through it. The issue is that this code does not "work". I want to order the categories from 0,1,2,3,4 ..

$categories = ProductCategory::with('products', 'subcategory')->orderBy('display_order', 'desc')->get();

https://blogs.wayne.edu/web/2017/03/13/updating-a-database-display-order-with-drag-and-drop-in-sql/

Thats where i got the inspiration and i tried to impliment for Larave;