How to add a model with custom auto-increment primary key Laravel

Question

I'm creating a seeder that reads (a subset of) data from our production db into our staging environment.

So I got a model called "categories" which it's primary key is set like so:

                                                             Table "public.categories"
     Column      |              Type              |                        Modifiers                        | 
-----------------+--------------------------------+---------------------------------------------------------+
 id              | integer                        | not null default nextval('categories_id_seq'::regclass) |

since I'm selecting a few categories from my prod db, its ids aren't sequential (they look like this:)

Now I also have a custom seeder file where I would like to create fake categories. Naturally, I'll have to make sure that I'm creating a category whose ID is larger than the category with the highest id. I do that like so:

$factory->define(App\Category::class, function (Faker\Generator $faker) {
    // to avoid duplicating ids
    $lastCategory = Category::select('id')->orderBy('id','desc')->first();

    $category = new Category();
    $category->id  = $lastCategory->id+1;
    $category->ref = str_random(20);
    $category->image = $faker->imageUrl(300, 300);
    $category->priority = rand(0, 100);
    $category->save();

But I keep on getting this error:

[Illuminate\Database\QueryException] SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value violates unique constraint "categories_pkey" DETAIL: Key (id)=(13) already exists. (SQL: insert into "categories" ("ref_translation", "ref", "parent_id", "top_parent_id", "image", "priority", "updated_at", "created_at") values (translatable.category.ref.HDHCDuNPC4vZoHBB, sandwiches, 124, 124, https://cdn.filepicker.io/api/file/TqB5OvCdSxGDFecrSyrU, 0, 2018-02-20 08:38:20, 2018-02-20 08:38:20) returni ng "id")

[Doctrine\DBAL\Driver\PDOException] SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value violates unique constraint "categories_pkey" DETAIL: Key (id)=(13) already exists.

although doing the same as a raw postgresql statement works just fine:

insert into categories (id, ref_translation,ref,image,priority) values (200, 'translatable.category.ref.ZAxcHj4hOziemQyz', 'LP2a1bY81IGSza0eHVSqdfsdfdsffds', 'image_name', 1);
INSERT 0 1

How do I get this to work through Laravel's Eloquent ORM?

update

for reference, this is what my class generator looks like:

$factory->define(App\Category::class, function (Faker\Generator $faker) {
    $category = App\Category::create([
        'ref'      => str_random(20),
        'image'    => $faker->imageUrl(300, 300),
        'priority' => rand(0, 100),
    ]);
    $top_parent_id = $category->id;

    return [
        'ref'           => str_random(21),
        'parent_id'     => $top_parent_id,
        'top_parent_id' => $top_parent_id,
        'image'         => $faker->imageUrl(300, 300),
        'priority'      => rand(0, 100),
    ];
});

Did you try to update Laravel? – ANeme Feb 22 '18 at 12:22 — ANeme, Feb 22 '18 at 12:22

score 1 · Answer 1 · edited Mar 27 '18 at 10:54

1

This made it work

$lastCategory = \App\Category::orderBy('id','desc')->first();
\DB::statement('alter sequence categories_id_seq restart with '.(intval($lastCategory->id)+1));

References

edited Mar 27 '18 at 10:54

halfer

19,824
17
99
186

answered Mar 14 '18 at 16:50

abbood

23,101
16
132
246

mmmm that't terrible thing to do gonna vote down it gonna bite you later on the way – xiarnousx Mar 14 '18 at 17:11
How come? I hope you got more than just: because I said so.. keep in mind this is dev environment where all the seed data is fake and so will be the subsequently entered data – abbood Mar 14 '18 at 17:13
which basically $lastCategory = \App\Category::orderBy('id', 'desc')->first(); then $newFreakCategory = $lastCategory->replicate(); – xiarnousx Mar 14 '18 at 17:14
Have you tested that? – abbood Mar 14 '18 at 17:22
That doesn't tell me anything about the id_sequence of the table – abbood Mar 14 '18 at 17:27
Also showing a different way of doing the same thing (assuming it works of course) doesn't mean the first way is a terrible idea – abbood Mar 14 '18 at 17:28
because you are altering the sequence of the db which should not be done at the application level. What if u call factory like that factory(App\Category::class, 100)->create(); It's interesting thing to try. – xiarnousx Mar 14 '18 at 17:35
Um.. im simply bringing the state of the app to a testable state using seeders.. which I can tear down and bring back up multiple times a day.. naturally this won't happen with a production database.. your problem is that you are applying production standards to a test env. Many would question the wisdom of copying prod data to a Dev environment in the first place.. but the again, we are moving away from objecticity and gearing towards personal preference here – abbood Mar 14 '18 at 17:41

score 0 · Answer 2 · answered Feb 20 '18 at 09:20

It seems it's inserting a new record with id = 13, for some reason

// this is another way, however you need a PDO object for it
$lastId = DB::getPdo()->lastInsertId();

Try removing the:

// ->id because the $lastCategory contains the last integer
$category->id  = ((int)$lastCategory +1);

score 0 · Answer 3 · answered Feb 20 '18 at 10:52

0

Use Laravel's increments() method in Model while defining the column itself. Check the doc here. This will take care of incrementing IDs on new inserts.

There are variants of this method for int, bigint and tinyint as well.

answered Feb 20 '18 at 10:52

tushar.dahiwale

146
2
13

that won't work.. it doesn't make any sense to modify the structure of the production database just to make the seeders happy.. the seeders must work without any alternation to the existing database schema – abbood Feb 20 '18 at 10:56

score 0 · Answer 4 · answered Sep 22 '22 at 11:02

Try it this way.

Create a function in the model to increment the Primary key as my example: id

public static function increatmentId()
{
    $id = 0;

    $model = Self::latest('id')->first(['id']);

    if ($model) {
        $id = (int) $model->id + 1;
    }

    return $id;
}

Asssign the increased value to the primary key on every creation.

protected static function boot()
{
    parent::boot();

    static::creating(function ($model) {
        $model->id = Self::increatmentId();
    });
}

How to add a model with custom auto-increment primary key Laravel

update

4 Answers4

References