How to seed self referencing table Laravel 5.1

Question

How can I seed a table with two foreign keys from the same table like messages:

Migration:

    public function up()
    {
        Schema::create('messages', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('to')->unsigned();
            $table->integer('from')->unsigned();
            $table->integer('parent_id')->unsigned()->nullable();
            $table->text('body');
            $table->boolean('status')->default(false);
            $table->timestamps();
        });

        Schema::table('messages', function (Blueprint $table) {
            $table->foreign('to')
                    ->references('id')->on('users')
                    ->onDelete('cascade');

            $table->foreign('from')
                    ->references('id')->on('users')
                    ->onDelete('cascade');

            $table->foreign('parent_id')
                    ->references('id')->on('messages');

        });
    }

ModelFactory:

$factory->define(App\Message::class, function($faker) {

    return [
        'body' => $faker->text
        'from' => //user id,
        'to' => //user id,
        'parent_id' => //message id
    ];

});

How can i get user ids here?
How do I get an existing row of message for the id?

Arindam · Answer 1 · 2016-06-08T19:09:07.557

$messages = factory(App\Message::class, 20)->create();
$messageIds = DB::table('messages')->lists('id'); // returns array of userids
DB::table('categories')->where('id', $rowid); // where $rowid is the id which you want

Full solution would be like this

class MessagesTableSeeder extends Seeder
{
    public function run()
    {
        // start with some cleanup    
        DB::table('messages')->truncate();
        // Create some App\Message instances...
        $messageCount = 30;
        $messages = factory(App\Message::class, $messageCount)->create();
        $messageIds = DB::table('messages')->lists('id');
        for($i=0; $i<$messageCount - 1; $i++) {
            $randomizedMessageIds = $messageIds;
            shuffle($randomizedMessageIds);
            DB::table('messages')->where('id', $i)->update(['parent_id'=>$randomizedMessageIds[$i]]);
        }
    }
}

Quick note that lists has been deprecated in 5.3 in favour of pluck so it becomes: $messageIds = DB::table('messages')->pluck('id'); which returns a collection. To get the array into $randomizedMessageIds to be shuffled, we therefore need: $randomizedMessageIds = $messageIds->all(); — theotherdy, Oct 22 '16 at 10:31

score 0 · Answer 2 · answered Sep 01 '17 at 12:53

Late to the party but I have another solution:

When creating your factory for whatever Model, the first few times, let the parent_id be 0. After a specified created entries in the DB, you can pass in the real fetched data which is already created.

I'm using this to self reference categories and subcategories.

 $factory->define(CMS\Models\Category::class, function (Faker\Generator $faker) {
    $users = \CMS\Models\User::all()->pluck('user_id')->toArray();

    /* Get all categories, if there are less then 4, the reference 
    is an array with a 0, meaning no parent. After 4 are created, we 
    start picking random categories s a parent ID, the list will grow 
    so does the variation. Of course you can set the minimum amount 
    of created categories as you wish.*/

    $categories = \CMS\Models\Category::all()->pluck('category_id');
    if (count($categories) <= 4) {
        $categories = [0];
    } else {
        $categories = $categories->toArray();
    }
    return [
        'category_id' => $faker->unique()->numberBetween(1,1000),
        'title' => $faker->sentence(1,40),
        'description' => $faker->text(50),
        'content' => $faker->paragraph(rand(3,5)),
        'approved' => $faker->boolean(),
        'trashed' => $faker->boolean(),
        'type' => $faker->randomElement('post','product'),
        'user_id' => $faker->randomElement($users),
        'parent_id' => $faker->randomElement($categories),
        'created_at' => $faker->dateTimeThisYear,
        'updated_at' => $faker->dateTimeThisYear,
    ];
});

Using this method you will have at least 1 entry without a parent if you set the count($categories) <= 1 which is needed to add parent_id's to your DB. If it's absolutely needed to have all entries have a parent_id, it only takes a manual change of 1 column in your DB.

How to seed self referencing table Laravel 5.1

2 Answers2