Laravel 4, how to I create seed data to tables with relationships?

Question

I have created a database with two tables, "goals" and "partgoals". The practial use is to make a savings goal (money) and have milestones along the way (partgoals). I want the partgoals obviously be linked to a specific goal. The relationships are created but I run into trouble when trying to create my seed data.

My goal is to set up two goals table like this (GoalsTableSeeder.php):

<?php

class GoalsTableSeeder extends Seeder {

    public function run()
    {
        DB::table('goals')->delete();


        $goals = array(
            array(
                'max'      => 1850000,
                'avgsav' => 3500,
                'duedate'  => date('2015-03-15'),
                'created_at' => new DateTime,
                'updated_at' => new DateTime,
            ),
            array(
                'max'      => 1100000,
                'avgsav' => 5000,
                'duedate'  => date('2013-11-15'),
                'created_at' => new DateTime,
                'updated_at' => new DateTime,
            )
        );

        DB::table('goals')->insert( $goals );
    }

}

And my partgoals table like this (PartgoalsTableSeeder.php):

<?php

class PartgoalsTableSeeder extends Seeder {

    public function run()
    {
        DB::table('partgoals')->delete();


        $partgoals = array(
            array(
                'id' => 1,
                'milestone'      => 100000,
                'duedate'  => date('2014-03-15'),
                'created_at' => new DateTime,
                'updated_at' => new DateTime,
            ),
            array(
                'id' => 1,
                'milestone'      => 20000,
                'duedate'  => date('2013-06-15'),
                'created_at' => new DateTime,
                'updated_at' => new DateTime,
            ),
            array(
                'id' => 2,
                'milestone'      => 400000,
                'duedate'  => date('2013-09-15'),
                'created_at' => new DateTime,
                'updated_at' => new DateTime,
            ),
            array(
                'id' => 2,
                'milestone'      => 200000,
                'duedate'  => date('2014-10-15'),
                'created_at' => new DateTime,
                'updated_at' => new DateTime,
            )
        );

        DB::table('partgoals')->insert( $partgoals );
    }

}

The migration table for "goals":

<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateGoalsTable extends Migration {

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('goals', function(Blueprint $table)
        {
            $table->increments('id');
            $table->integer('max');
            $table->float('avgsav');
            $table->date('duedate');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('goals');
    }

}

The migration table for partgoals:

<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreatePartgoalsTable extends Migration {

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('partgoals', function(Blueprint $table)
        {
            $table->foreign('id')
                ->references('id')->on('goals')
                ->onDelete('cascade');
            $table->increments('id');
            $table->float('milestone');
            $table->date('duedate')->nullable();
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('partgoals');
    }

}

What am I doing wrong? I am new to Laravel (and Laravel 4).

Answer 1

To seed tables with relationship, you need to defined model factories in the ModelFactory.php file and then create a seeder class to run the seeder.

For ex. ModelFactory.php

$factory->define(App\Category::class, function (Faker\Generator $faker) {

    $name = $faker->name;

    return [
        'name' => $name,
        'visible' => 1
    ];
});

$factory->define(App\Video::class, function (Faker\Generator $faker) {

    return [
        'title' => $faker->name,
        'description' => '',
        'status' => 1
    ];
});

Then the seeder class can be as follows

<?php

use Illuminate\Database\Seeder;

class CategoriesTableSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        $categories = factory(App\Category::class, 20)->create()->each(function ($u) {
                for ($i=0; $i<5; $i++)
                {
                    $u->videos()->save(factory(App\Video::class)->make());
                }
            });
    }
}

You can refer this article on how to generate seeds for two tables with relationship http://deepdivetuts.com/seeding-two-tables-using-laravel-5-3

Answer 2

I see a few problems with your code:

1) The way you create the foreign key
When assigning the Foreign key constraint, you should make that column unsignedInteger.
In the code below I will correct the mistake you made of naming two columns 'id'.

Schema::create('partgoals', function(Blueprint $table)
    {
        $table->increments('id');        
        $table->unsignedInteger('goal_id');
        $table->foreign('goal_id')
            ->references('id')->on('goals')
            ->onDelete('cascade');
        $table->float('milestone');
        $table->date('duedate')->nullable();
        $table->timestamps();
    });

2) The way you seed your database
If you specify a foreign key, you should declare the value when creating the entry in the table seeder.
If you want to specify a NULL value, this can be done by allowing the column to accept such value (by default it doesn't). In this case, we should add ->nullable()->default(NULL)

Schema::create('partgoals', function(Blueprint $table)
    {
        $table->increments('id');        
        $table->unsignedInteger('goal_id')->nullable()->default(NULL);
        $table->foreign('goal_id')
            ->references('id')->on('goals')
            ->onDelete('cascade');
        $table->float('milestone');
        $table->date('duedate')->nullable();
        $table->timestamps();
    });

Minor mistake
3) You are passing the 'id' => 1 twice in your seeder

Answer 3

When using increments in the query builder, that automatically makes that your primary key, auto-incremented, and unique. You can't have a foreign key also be your primary key unless it's a one-to-one relationship. That's just bad design though. Your schema should look something like below.

Schema::create('partgoals', function(Blueprint $table)
    {
        $table->increments('id');
        $table->foreign('goal_id')
            ->references('id')->on('goals')
            ->onDelete('cascade');
        $table->float('milestone');
        $table->date('duedate')->nullable();
        $table->timestamps();
    });

Also, when seeding, if you use the insertGetId when inserting, it will return the ID of the record you just inserted. This you can use in another insert, like inserting into another table, later. However, this has to take place in the same script. You may be able to pass it back out to DatabaseSeeder and then back into another seed script, but I haven't tried this.

Answer 4

I'm not familiar with Laravel, or what you're trying to do, but based on the error you added in the comments it seems that your problem is a result of trying to enter multiple records with the same primary key (1) into your partgoals table.

I'm not sure how you've set your tables up, but it seems like you've defined a partgoals table with a unique primary key column ID, which you're also trying to use as a foreign key to reference the goals table. It may be worth creating another field to hold your foreign key in the partgoals table.