connecteev
5 months ago

Finding a unique record (based on a composite key) in Eloquent

Posted 5 months ago by connecteev

I have 3 tables: users, posts, reactions...they're self-explanatory. A user can react to (like) a post, so the reactions table contains: id, user_id, post_id. I have a unique DB constraint for: user_id, post_id, ie a specific user can only react to a given post ONCE.

I am trying to create a seeder (using factories) to generate 1000 random reactions using a factory. Here's my old code, which doesn't work.

$factory->define(Reaction::class, function (Faker $faker) {
    $isRemoved = $faker->boolean(10); // parameter = chance of being true
    return [
        'is_archived' => $isRemoved,
        'deleted_at' => $isRemoved ? now() : NULL,
    ];
});

$factory->state(Reaction::class, 'to_posts', function (Faker $faker) {
    $randomPost = Post::inRandomOrder()->first();
    $reactableId = $randomPost['id'];
    $reactableType = 'App\Post';
    $randomUserId = getUniqueUserId($reactableType, $reactableId); 
    return [
        'user_id' => $randomUserId,
        'reactable_type' => $reactableType, // type of entity this reaction was made to
        'reactable_id' => $reactableId, // id of entity this reaction was made to
        'created_at' => null,
        'updated_at' =>  null,
    ];
});


// Get a random User ID, to avoid the unique constraints on the reactions table
// The combination of user_id, reactable_type, reactable_id have to be unique
function getUniqueUserId($reactableType, $reactableId)
{
    $usersThatHaveReactedToThis =
        Reaction::where([
            ['reactable_type', '=', $reactableType],
            ['reactable_id', '=', $reactableId],
        ])
        ->pluck('user_id')
        ->toArray();

    $randomUser =
        User::whereNotIn('id', $usersThatHaveReactedToThis)
        ->inRandomOrder()
        ->first();

    if (!$randomUser) {
        echo "Exiting Seeder: No random User could be found in the DB\n";
        exit;
    }

    $user_id = $randomUser->id;
    return $user_id;
}

Schema:

CREATE TABLE `reactions` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `is_archived` tinyint(1) DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `user_id` int unsigned NOT NULL,
  `reactable_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `reactable_id` int unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_fk_1390101` (`user_id`),
  CONSTRAINT `user_fk_1390101` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=401 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

As you can see, it first tries to get a random post, then it tries to find a user that has not reacted to the post. which is not the same logic as what I am trying to do now. This sucks because it's doing it serially like this, it cannot find UNIQUE values (I have a unique DB constraint for: user_id, post_id, ie a specific user can only react to a given post ONCE, which makes sense) With this, my seeder dies because of "conflicts".

Seeding: ReactionsFakeDataSeeder
Seeding Reactions... 
Exiting Seeder: No random User could be found in the DB

So, it couldnt find a random user that has not reacted to this post - doesnt mean that another random user that has not reacted to a different post does not exist.

My logic above is flawed. A better way would be to write an eloquent query to get any random user than has NOT reacted to any post. What is the best way to go about doing this?

Please sign in or create an account to participate in this conversation.