How to insert record just before / after another record in Laravel

Question

Currently, I have Laravel 9 project and I want to insert new record just above or below another record in the database. But I am stuck at:

Integrity constraint violation: 1062 Duplicate entry '5' for key 'PRIMARY'....

Here is my code.

public function store(StoreCategoryRequest $request)
{
    $before_id = $request->before_id != 0 ? $request->before_id: null;
    $after_id = $request->after_id != 0 ? $request->after_id: null;

    $category = auth()->user()->categories()->create($request->validated());

    if (($before_id && !$after_id) || (!$before_id && $after_id)) 
    {
       Category::when($before_id && !$after_id,
            function ($query) use ($before_id) { return $query->where('id', '>=', $before_id); }
        )->when(!$before_id && $after_id,
            function ($query) use ($after_id) { return $query->where('id', '>', $after_id); }
        )->increment('id', 1);

        $category->update(['id' => $before_id ?? $after_id]);
    }

    return to_route('category.index')->with(feedback('New category inserted successfully.', 'success'));

}

Is there any other way, please guide me.

Answer 1

Ok, I got the answer. Here is the code:

Category::latest('id')->when($before_id && !$after_id,
    function ($query) use ($before_id) { return $query->where('id', '>=', $before_id); }
)->when(!$before_id && $after_id,
    function ($query) use ($after_id) { return $query->where('id', '>', $after_id); }
)->increment('id', 1);
  
Category::where('id', $category->id + 1)->update(['id' => $before_id ?? $after_id + 1]);