New To Laravel, error on using composite unique constraint.

Hello, I'm new to Laravel, I started learning this week since I got a internship and I will learn PHP, Laravel and Codeigniter in there, so I wanted to get a head start. I wanted to develop a simple recipes application, with recipes and reviews, but I'm currently having a problem when trying to seed the reviews table. The problem I'm facing is when i try to seed it, I keep hitting the unique constraint created to assure that a user only creates one review per recipe, even when trying to handle the duplication. Currently my reviews table is:

Schema::create('reviews', function (Blueprint $table) {
            $table->id();
            $table->foreignIdFor(User::class); // Foreign Key to the author of the review.
            $table->foreignIdFor(Recipe::class); // Recipe receiving the review.
            $table->tinyInteger("score");
            $table->string("title");
            $table->text("description");
            $table->timestamps();
            $table->unique(["user_id", "recipe_id"]);
        });

I tried to add some verifications before returning the factory definition, but it still gives errors. The current factory logic is:

    public function definition(): array
    {
        // Get a random recipe and a random user different than it's author.
        $recipe = Recipe::inRandomOrder()->first() ?? Recipe::factory()->create();
        $user_id = User::all()->where('id', '!=', $recipe->user_id)->random()->id;

        // Counter of tries to create a valid review.
        $cur_count = 0;

        // Assure that the user doesn't have a review for that recipe.
        while (Review::where('user_id', $user_id)
            ->where('recipe_id', $recipe->id)->exists()
        ) {
            // Increments the counter and create a new user if doesn't find a valid user.
            if (++$cur_count == 3) {
                $user_id = User::factory()->create();
                break;
            }

            // Get a new random user.
            $user_id = User::where('id', '!=', $recipe->user_id)
                ->where(
                    'id',
                    '!=',
                    $user_id
                )->random()->id;
        }

        return [
            'user_id' => $user_id,
            'recipe_id' => $recipe->id,
            'title' => fake()->slug(),
            'description' => fake()->paragraph(),
            'score' => fake()->numberBetween(0, 10)
        ];
    }

I'm getting: SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value violates unique constraint "reviews_user_id_recipe_id_unique" DETAIL: Key (user_id, recipe_id)=(27, 9) already exists. I would be really thankful if someone could give me a idea on how to make it work. Thanks in advance.

Tray2

1 year ago

Level 73

You have created a unique constraint, and you are violating it, read the error message.

Unique violation: 7 ERROR: duplicate key value violates unique constraint "reviews_user_id_recipe_id_unique" DETAIL: Key (user_id, recipe_id)=(27, 9) already exists.

The user id 27 and the recipe_id of 9 already exists in the table.

Gustavo-Feijo

1 year ago

Level 1

@Tray2 I understood that, the question is why the logic implemented before doesn't avoid that. In the loop I'm verifying if a review with the user_id and recipe_id doesn't exist already, then I get another random id until the loop isn't triggered or the count limit is reached, shouldn't the loop avoid the constraint being violated?

Tray2

1 year ago

Level 73

@Gustavo-Feijo Why not just create a new user, it will give you an id that does not exist.

I don't understand what your code is supposed to do. If it is just to create a new user, and new review and link them then you should just do that.

1 like

Lumethys

1 year ago

Level 5

$user_id = User::all()->where('id', '!=', $recipe->user_id)->random()->id;

just a heads up: this line retrieve ALL users inside the db and then filter it on the client, which is extremely bad. (User::all() is getting all data from db)

a better query would be

$user = User::query()
	->inRandomOrder()
	->where('id', '!=', $recipe->user_id)
	->select(['id'])
	->first();

$userId = $user->id;

However, it is not the source of your problem, your approach is just wrong

A Factory's job is to define what the data looks like, they shouldnt have much logic, logic should be in the seeder.

A better approach would be:

1/ Generate n random users

2/ Generate m random recipes

3/ Associate some of the generated users to some of the generated recipe

Does it sound simpler?

some example code:

class DatabaseSeeder extends Seeder
{
    public function run(): void
    {
        $users = User::factory()->count(50)->create();				//generate 50 users
		$recipes = Recipe::factory()->count(30)->create();		//generate 30 recipes

		$randomUsers = $users->random(10);			//get 10 random users from the list of 50
		
		$randomUsers->each(function(User $user) use ($recipes) {
				$user->reviews()->create([
					'recipe_id' 		=> $recipes->random()->id,
            		'title' 			=> fake()->slug(),
            		'description' 		=> fake()->paragraph(),
           			'score' 			=> fake()->numberBetween(0, 10)
				]);
		});
    }
}

Please or to participate in this conversation.