How to change the position of a category in the general list?

Question

Database design

I have this migration:

Schema::create('categories', function (Blueprint $table) {
            $table->mediumIncrements('id');
            $table->string('name')->index(); // Заголовок
            $table->text('text')->nullable(); // Описание
            $table->string('title',150); // title на странице категории
            $table->string('keywords',212)->nullable(); // keywords на странице категории
            $table->text('description'); // description на странице категории
            $table->string('image',80)->nullable(); // путь до картинки категории
            $table->string('folder',23)->nullable(); // название папки где хранятся картинки контента
            $table->boolean('is_extended')->default(0); // расширенная категория или нет
            $table->boolean('hide')->default(0); // опубликован или не опубликован
            $table->string('alias',160); // URL категории
            $table->unsignedTinyInteger('index')->default(0); // Позиция категории
            $table->unsignedMediumInteger('parent_id')->nullable();
            $table->foreign('parent_id')->references('id')->on('categories')->onDelete('set null');
            $table->timestamps();
        });

It is necessary to implement the ability to drag and drop categories, let's say the category is at the end, it can be dragged to the beginning, and it will become the first, I implemented this using a field in the database indexand when outputting, sorting by this field occurs. But the problem is that by default all categories indexhave 0, and first all categories with index 0 go, and then everything else, how to solve this problem? Is it just to increment this index when creating each category? But I don't think it's a very good option.

Answer 1

No, there is definitely no autoincrement. But if the business logic requires it, you can do +1 to the maximum value when creating a new one, as an example.
And so, for default sorting, you already have an id, so sorting will go according to it, other things being equal (well, if everyone has 0).
By the way, I advise you to install the spatie/eloquent-sortable package , they have done the implementation of most of the necessary logic for sorting, so you only need to register method calls in the right places, the default behavior (you can use the query scope), well, the request from the front. And everything will magically work. You do not need to change the schema and database, just install the package and make the appropriate changes to the code.