Skip to content

Instantly share code, notes, and snippets.

@ronssij
Last active February 9, 2024 18:00
Show Gist options
  • Save ronssij/ebd355ff16c49f011c5690bd14069b37 to your computer and use it in GitHub Desktop.
Save ronssij/ebd355ff16c49f011c5690bd14069b37 to your computer and use it in GitHub Desktop.
Laravel: Sequentially Incrementing Null Columns in a Database
<?php
namespace Database\Seeders;
use Illuminate\Database\Seeder;
use Illuminate\Support\Facades\DB;
class SequentialIncrementNullColumns extends Seeder
{
public function run(): void
{
$maxPosition = '(SELECT IFNULL(MAX(`position`), 0) FROM `sections`)';
DB::transaction(function () use ($maxPosition) {
DB::statement("SET @pos := $maxPosition");
DB::update("UPDATE `sections` SET `position` = (@pos := @pos + 1) WHERE `order` IS NULL AND `position` IS NULL ORDER BY `id`");
});
}
}
@ronssij
Copy link
Author

ronssij commented Feb 9, 2024

Another way.

SQL Query

UPDATE `sections`
INNER JOIN (
    SELECT `id`, (@row_number:=@row_number + 1) AS new_position
    FROM `sections`, (SELECT @row_number := IFNULL((SELECT MAX(`position`) FROM `sections`), 0)) AS init
    WHERE `order` IS NULL AND `position` IS NULL
) AS subq ON `sections`.`id` = subq.`id`
SET `sections`.`position` = subq.new_position;

~ OR ~

Eloquent

Section::joinSub('
    SELECT `id`, (@row_number:=@row_number + 1) AS new_position
    FROM `sections`, (SELECT @row_number := IFNULL((SELECT MAX(`position`) FROM `sections`), 0)) AS init
    WHERE `order` IS NULL AND `position` IS NULL
', 'subq',
  'sections.id', '=', 'subq.id')->update(['position' => DB::raw('`subq`.`new_position`')]);

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment