Seeding database tables that have foreign key constrains, or better way to regenerate models with same information

I did some research on how to seed with foreign keys, and most of them include faker, which then randomly assigns those fields.

Unfortunately, this doesn't really work for me (I think) as I need the same data every time.

I have a relationship of

category()->subCategory()->subSubCategory()->product

I keep all categories and subcategories in the database so that I could update, rearrange, or delete them. I want to be able to regenerate them on the fly rather than creating a category then going creating subcategories and later creating subSubCategories which all foreign key constrained.

        Schema::create('categories', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->string('slug')->unique();
            $table->timestamps();
        });

        Schema::create('sub_categories', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('category_id')->unsigned();
            $table->string('name');
            $table->string('slug')->unique();
            $table->timestamps();

            $table->foreign('category_id')
            ->references('id')->on('categories')
            ->onDelete('cascade');
        });


        Schema::create('sub_sub_categories', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('sub_category_id')->unsigned();
            $table->string('name');
            $table->string('slug')->unique();
            $table->timestamps();

            $table->foreign('sub_category_id')
            ->references('id')->on('sub_categories')
            ->onDelete('cascade');
        });

So I was thinking of using seeder for this to make my life easier

class CategoryTableSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
    //categories
        DB::table('categories')->insert([
            'name' => 'Clothing',
            'slug' => 'clothing',
        ]);
        DB::table('categories')->insert([
            'name' => 'Shoes',
            'slug' => 'shoes',
        ]);

    //subcategories
           DB::table('sub_categories')->insert([
                'category_id' => '1',
                'name' => 'Womens',
                'slug' => 'womens',
            ]);
            DB::table('sub_categories')->insert([
                'category_id' => '1',
                'name' => 'Mens',
                'slug' => 'mens',
            ]);
    }
}

But I face with

Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`paciaisau`.`sub_categories`, CONSTRAINT `sub_categories_category_id_foreign` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE) (SQL: insert into `sub_categories` (`category_id`, `name`, `slug`) values (1, Womens, womens))

What would be the right way of doing this?

aurawindsurfing

6 years ago

Level 50

Hi @spl3s

You are on the right track but you should not call tables in your seeder directly. Instead build up 3 factories for category, sub_category and sub_sub_category. Then when you create a sub_category you can straight away create it parent category:

<?php

use App\Category;

$factory->define(App\Sub_Category::class, function () {
    return [
        'category_id' => factory(Category::class)
    ];
});

Really good examples are here: https://laraveldaily.com/laravel-two-ways-seed-data-relationships/

Hope it helps!

SPL3S

6 years ago

Level 1

Thanks for the example, Laravel daily shows how to assign them randomly with new seeding, in my case I always want the Clothing match with Women's which then should match with skirt:

category->subcategory->subsubcategory Clothing->Women's->Skirt,

and then another would be:

Electronics->Portable->Laptops

aurawindsurfing

6 years ago

Level 50

Yes then look at my example. Basically if you call factory in a callback by default it will be associated with the parent factory.

Please or to participate in this conversation.