Laravel seeding - unique pairs of user and teacher IDs

Question

I am using database migration and seeding in Laravel 5.1.

Migration

public function up()
{
    Schema::create('teachers', function (Blueprint $table) {
        $table->increments('id');
        $table->integer('user_id')->unsigned();
        $table->integer('teacher_id')->unsigned();
        $table->boolean('disable')->default(0);
        $table->timestamps();

        $table->unique(['user_id', 'teacher_id']);

        $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
        $table->foreign('teacher_id')->references('id')->on('users')->onDelete('cascade');
    });
}

*** user_id and teacher_id must be unique together.

Model Factory

$factory->define(App\Teacher::class, function ($faker) {
    return [
        'user_id'           => $faker->numberBetween(1, 59),
        'teacher_id'        => $faker->numberBetween(1, 59),
    ];
});

I set up the seeder for producing 500 teacher relational in DatabaseSeeder.php: factory(App\Teacher::class, 500)->create();

but i got this error:

[PDOException] 
SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '10-11' for key 'teachers_user_id_teacher_id_ 
unique'

As you can see, the *** (unique property) caused the error. is there any way to fix it? What is your idea?

Trying yo generate `500` unique pairs of user and teacher IDs while allowing each one a number between `1` and `59`, will undoubtably cause duplicates because of the small range. `C(59,2)` gives `1711` possible combinations, which for the `500` items you want to generate will have a very high chance of repeating pairs, thus throwing the _"Duplicate entry"_ error. So your best bet is to increase the upper range for `numberBetween` to something significantly higher than `59`, or just cache and check previously generated pairs before trying to store new ones, to make sure there are no duplicates. — Bogdan, Oct 29 '15 at 23:29
@Bogdan is there any way to customize factory function for preventing dublicate? increasing the upper range don't solve the problem. — Andrew, Oct 29 '15 at 23:47
Try caching each generated pair in an array, for example named `$savedPairs`, and when each new `$pair` is generated, search that array using `array_search($pair, $savedPairs)` and only save the new database entry if the result of the search is `false`. — Bogdan, Oct 29 '15 at 23:55

score 2 · Answer 1 · answered Feb 22 '16 at 12:00

You could get a collection of User models, and then in a while loop assign pairs from random IDs in that collection:

$users = User::all(['id']);

while ($users->count() > 1) {
    // Get random user for a teacher, and remove from collection
    $teacher = $users->random();
    $users->pull($teacher->getKey());

    // Get random user and remove from collection
    $user = $users->random();
    $users->pull($user->getKey());

    Teacher::create([
        'user_id' => $user->getKey(),
        'teacher_id' => $teacher->getKey(),
    ]);
}

score 0 · Answer 2 · answered Aug 27 '21 at 13:12

You can throw exception if unique record are exists in database. Then inside seeder class, use try-catch block, with catching exception do nothing. The seeding should continue if it encounters existing records.

Example: let's say that there are 3 columns in product_stocks table, where product_id, color_id, and size_id is unique.

Factory class:

/**
 * Define the model's default state.
 *
 * @return array
 */
public function definition()
{
    $productId = $this->faker->randomElement(Product::pluck('id')->toArray());
    $colorId = $this->faker->randomElement(Color::pluck('id')->toArray());
    $sizeId = $this->faker->randomElement(Size::pluck('id')->toArray());

    $exists = ProductStock::where([
        ['product_id', '=', $productId],
        ['color_id', '=', $colorId],
        ['size_id', '=', $sizeId],
    ])->exists();

    if ($exists) {
        throw new Exception('duplicate value');
    }

    return [
        'product_id' => $productId,
        'color_id' => $colorId,
        'size_id' => $sizeId,
        'stok_ready' => $this->faker->numberBetween(0, 100),
    ];
}

Seeder class:

/**
 * Run the database seeds.
 *
 * @return void
 */
public function run()
{
    try {
        ProductStock::factory()
            ->count(Product::count() * rand(5, 10))
            ->create();
    } catch (Exception $e) {
        // do something
    }
}

Though if your seeding throws `QueryException`, then use that instead of regular `Exception`. — Rizky92, Aug 27 '21 at 13:19

toxab · Answer 3 · 2020-04-13T13:17:40.573

/**@var array $unique*/

$repeatRandom =  static function () use (&$unique, &$repeatRandom) {
    $userId = User::pluck('id')->random();
    $teacherId = User::pluck('id')->random();
    $newPair = [
        $userId, $teacherId
    ];

    foreach ($unique as $items) {
        if (!array_diff_assoc($items, $newPair)) {
            return $repeatRandom($unique);
        }
    }
    return $newPair;
};


$factory->define(Teacher::class, static function (Faker $faker) use (&$unique, &$repeatRandom) {

    $userId = User::pluck('id')->random();
    $teacherId = User::pluck('id')->random();

    $newPair = [
        $userId, $teacherId
    ];

    if (is_array($unique)) {
        foreach ($unique as $items) {
            if (!array_diff_assoc($items, $newPair)) {
                $newPair = $repeatRandom($unique);
            }
        }
    }

    $unique[]  = $newPair;
    return [
        'user_id' => $newPair[0],
        'teacher_id' => $newPair[1]
    ];  
}

Laravel seeding - unique pairs of user and teacher IDs

3 Answers3