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-02 18:18:05Z

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 2, 2014 at 18:18

answered May 2, 2014 at 17:48

The Alpha

147k30 gold badges294 silver badges313 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

ollieread Over a year ago

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?

The Alpha Over a year ago

Roles are already created according to his code in question, only attaching with created roles.

The Alpha Over a year ago

I was thinking he want to attach one role to each user but for multiple roles sync should be used, thanks @ollieread :-)

lozadaOmr Over a year ago

@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?

The Alpha Over a year ago

It has nothing to do with seeding.

|

Jarek Tkaczyk · Accepted Answer · 2014-05-03 21:01:12Z

8

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)
        ]);
    }

answered May 3, 2014 at 21:01

Jarek Tkaczyk

81.4k27 gold badges167 silver badges162 bronze badges

2 Comments

lozadaOmr Over a year ago

But can it generate including the relationship? I'm sorry if this may sound silly but I am not familiar with using Faker.

Jarek Tkaczyk Over a year ago

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.

Ben · Accepted Answer · 2016-04-13 11:52:16Z

3

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 );
   }

answered Apr 13, 2016 at 11:52

Ben

1,67013 silver badges15 bronze badges

Comments

ollieread · Accepted Answer · 2014-05-02 17:37:25Z

1

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.

edited May 2, 2014 at 17:37

answered May 2, 2014 at 16:58

ollieread

6,3211 gold badge22 silver badges36 bronze badges

2 Comments

ollieread Over a year ago

Excellent. Are the users being seeded too? As in, is this entirely random testing data or is this production ready data?

lozadaOmr Over a year ago

Yes the users are being seeded, this only for testing data.

Collectives™ on Stack Overflow

Better way of Seeding a Pivot Table

4 Answers 4

6 Comments

2 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related