CODEWITHSUNDEEP
phplaravellaravel-5.5eloquent
D.R.
D.R.

Reputation: 2858

Laravel. How to handle sort_order column shift

I have such a table, and SomeModel, that represents it

| id              | name             | sort_order       |
| --------------- | ---------------- | ---------------- |
| 1               | A                | 1                |
| 2               | B                | 2                |
| 3               | C                | 3                |
| 4               | D                | 8                |

I'm implementing an API for this table. How to handle properly sort_order column shift during inserting the same values.

For example:

I want to save such an object:

{
    "name": "B2",
    "sort_order": 2
}

and receive such a table:

| id              | name             | sort_order       |
| --------------- | ---------------- | ---------------- |
| 1               | A                | 1                |
| 5               | B2               | 2                |
| 2               | B                | 3                | <--- initially was 2
| 3               | C                | 4                | <--- initially was 3
| 4               | D                | 8                |

How to implement it?

Upvotes: 1

Views: 585

Answers (2)

apokryfos
apokryfos

Reputation: 40653

Since you have some model you can do this:

In your AppServiceProvider

Event::listen("eloquent.inserting: ".SomeModel::class, function (SomeModel $model) {
      SomeModel::where("sort_order", ">=", $model->sort_order)
             ->update([ "sort_order" => \DB::raw("sort_order+1") ])
});

The idea is to update all other models with a sort order higher or equal to the one you're inserting and increment it by one. You might want to do something similar with the deleting event to fill in the gaps and perhaps the updating event to swap sort orders.

You can (and should) dig deeper into the event system for models by reading the documentation

Of course you could always set the sort_order column as unique and create a DB trigger to keep the sort orders in check for you which is probably better since this is a data integrity concern.

Upvotes: 0

Sebastien D
Sebastien D

Reputation: 4482

I'd do it like this:

use App\SomeModel;
use Illuminate\Http\Request;

public function insert_data(Request $request){  
    SomeModel::where("sort_order", ">=", $request->sort_order)->increment("sort_order");
    SomeModel::create($request->all());
}

Upvotes: 1

Related Questions