Better way of Seeding a Pivot Table

Question

I have the following tables users , roles and the pivot table role_user with the following table structure

users

id
username
password

roles

id
role

role_user

id
role_id

user_id

    <?php
         class PivotTableSeeder extends Seeder {
             public function run()
             {
              // Seeds the roles table
                DB::table('roles')->delete();

                DB::table('role_user')->insert(array(
                array('user_id' => 1, 'role_id' => 1),
                array('user_id' => 2, 'role_id' => 2),
                array('user_id' => 3, 'role_id' => 1),
                array('user_id' => 3, 'role_id' => 3),
                array('user_id' => 3, 'role_id' => 5)
            ));
            }
        }

The pivot table is seeded using DB select

Is there a better way to seed the tables, including the pivot table?

I was thinking maybe when I seed my users table, it will also seed the role_user table instead of manually inserting the data into the pivot table.

Example:

roles

id = 1
role = encoder
id = 2
role = tech
id = 3
role = sales

users

id = 1
username = user
password = pass
id = 2
username = user2
password = pass2

role_user

id = 1
user_id = 1
role_id = 1
id = 2
user_id = 1
role_id = 2
id = 3
user_id = 2
role_id = 3

Edit

I am seeding my users table by using this Eloquent. Is there anyway that while seeding the User, the role_user will also get updated?

User::create(array(
    'id' => '1',
    'username' => 'user',
    'password' => 'pass'
));
User::create(array(
    'id' => '2',
    'username' => 'user2',
    'password' => 'pass2'
));

The Alpha · Accepted Answer · 2014-05-02T18:18:05.723

11

You may try this (Assuming that you have already seeded roles):

$user = User::create(['id' => '1', 'username' => 'user', 'password' => 'pass']);
$user->roles()->sync([1,2]); // array of role ids

$user = User::create(['id' => '2', 'username' => 'user2', 'password' => 'pass2']);
$user->roles()->sync([3,4]); // array of role ids

I've used [] instead of array(), if your PHP is prior to ver-5.4 then you should use array().

edited May 02 '14 at 18:18

answered May 02 '14 at 17:48

The Alpha

143,660
29
287
307

That's not creating the roles, and doesn't `attach()` limit you to one record? So you'd have to run multiple inserts to add multiple roles to a user? – ollieread May 02 '14 at 17:56
Roles are already created according to his code in question, only attaching with created roles. – The Alpha May 02 '14 at 18:05
I was thinking he want to attach one role to each user but for multiple roles `sync` should be used, thanks @ollieread :-) – The Alpha May 02 '14 at 18:19
@WereWolf-TheAlpha was just wondering, I seeded the database using your answer and I noticed that the `created_at` and `updated_at` columns were not updated? – lozadaOmr May 03 '14 at 12:01
1

It has nothing to do with seeding. – The Alpha May 03 '14 at 17:23
What if the roles are seeded too? – Michael Obi May 20 '15 at 08:55

score 8 · Answer 2 · answered May 03 '14 at 21:01

For testing purposes I use pretty simple and fast method like below.

Imagine we have users and categories with pivot table (this comes from JeffreyWay's generators btw):

<?php

// Composer: "fzaninotto/faker": "v1.3.0"
use Faker\Factory as Faker;

class UsersTableSeeder extends Seeder {

    public function run()
    {
        $faker = Faker::create();

        foreach(range(1, 100) as $index)
        {
            User::create([
                'username'   => $username = $faker->userName,
                'email'      => $faker->email,
                'password'   => Hash::make($username),
                'account_id' => $index
            ]);
        }
    }

}

// all other seeders look the same, so I paste just the code that matters:
// Categories
    foreach(range(1, 30) as $index)
    {
        Category::create([
            'name' => $faker->word
        ]);
    }

// pivot table
    foreach(range(1, 50) as $index)
    {
        DB::table('category_user')->insert([
            'category_id' => rand(1,30),
            'user_id' => $faker->unique()->randomNumber(1, 100)
        ]);
    }

But can it generate including the relationship? I'm sorry if this may sound silly but I am not familiar with using Faker. — lozadaOmr, May 04 '14 at 09:37
It can't generate it automatically, but as you can see in my example it requires 2 simple lines of code: random id from 1 to max, where max is hardcoded. Really, it takes like 5 mins to seed several tables this way. — Jarek Tkaczyk, May 04 '14 at 13:12

score 3 · Answer 3 · answered Apr 13 '16 at 11:52

For n:m Relationships, where I need to attach random entries, I use this simple but efficient Seeder code, that only uses real ids:

    $faker = Faker\Factory::create();

    $limit = 100;

    for ($i = 0; $i < $limit; $i++) {
        $newrow = *Yourmodel*::create ([
            'email' => $faker->word . rand(0, 9999) . '@test.com' ,
          ...
        ]);

        $ids = $faker->randomElements( \App\YourOtherModel::select('id')->get()->toArray(), rand(1,*3*) );
        foreach($ids as $id) {
            $newrow->*your_relationship*()->attach( $id );
   }

ollieread · Answer 4 · 2014-05-02T17:37:25.510

I'm not sure how others feel about this, but when seeding the database, I like to use the actual models for the seeding, that way you can use all the handy functions that come with inserting related models.

If you're seeding the user and roles with predefined ids, then it's easy.

class UserSeeder extends Seeder
{

    public function run()
    {
        DB::table('users')->truncate();
        DB::table('roles')->truncate();
        DB::table('user_roles')->truncate();

        $users = [];

        $user = User::create(['id' => 1, 'blah' => 'honk']);
        $users[$user->id] = $user;
        $user = User::create(['id' => 2, 'blah' => 'honk']);
        $users[$user->id] = $user;
        // etc etc

        $roles = [];
        $role = Role::create(['id' => 1, 'blah' => 'honk']);
        $roles[$role->id] = $role;
        // etc etc

        $user[1]->roles()->sync(1);
        $user[2]->roles()->sync(2);
        $user[3]->roles()->sync(1, 3, 5);
    }

}

In a recent application I wrote, I created a seeder that created the ACL groups, then permissions, assigned the permissions to the specific groups, then randomly created somewhere between 10-30 (using rand) users and randomly assigned them to different groups.

Let me know if anything is unclear.

Excellent. Are the users being seeded too? As in, is this entirely random testing data or is this production ready data? — ollieread, May 02 '14 at 17:26

Better way of Seeding a Pivot Table

4 Answers4

Linked