CODEWITHSUNDEEP
mysqllaravel
nbayly
nbayly

Reputation: 2167

Laravel Seed of Nullable foreign key

I'm trying to seed my MySQL database with reference data. The particular table I am having difficulty has a nullable foreign key that maps to the same table to represent a parent/child relationship, as per my migration function:

public function up()
{
    Schema::create('groups', function (Blueprint $table) {
        $table->engine = 'InnoDB';
        $table->increments('id');
        $table->integer('parent_group_id')->unsigned()->nullable();
        $table->foreign('parent_group_id')->references('id')->on('groups');
        $table->string('value');
        $table->softDeletes();
    });
}

Issue is trying to seed with the foreign key as NULL on the top level rows. If I don't include that field on any of the inserted rows the seed works as expected. When I add the field to only the child rows it expects the same field count on every row and errors out as:

[PDOException]
SQLSTATE[21S01]: Insert value list does not match column list: 1136 Column
count doesn't match value count at row 2

I could not find any reference on how to seed a value to NULL. Last attempt I settled for was:

<?php

use Illuminate\Database\Seeder;

class GroupsTableSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        DB::table('groups')->insert([
            [
                'parent_group_id' => [NULL],
                'value' => 'Group1'
            ],
            [
                'parent_group_id' => 1,
                'value' => 'Subgroup1'
            ],
            [
                'parent_group_id' => 2,
                'value' => 'Subgroup2'
            ]
        ]);
    }
}

Which errors out to Array to string conversion.

Using '[NULL]' errors to General error: 1366 Incorrect integer value. I have tried other variations but no luck. Whats the correct way to insert a NULL value in a seed? Any help appreciated. Regards,

Upvotes: 1

Views: 3248

Answers (1)

Jerodev
Jerodev

Reputation: 33186

You can just use a php null value, Laravel is smart enough to convert it to a null value for the database.

DB::table('groups')->insert([
    [
        'parent_group_id' => null,
        'value' => 'Group1'
    ],
    [
        'parent_group_id' => 1,
        'value' => 'Subgroup1'
    ],
    [
        'parent_group_id' => 2,
        'value' => 'Subgroup2'
    ]
]);

When using [ and ] you create a php array, that is why you get that error.

Upvotes: 2

Related Questions