Right - had time to think this is probably how I would do it.
I would have a users table and make a one to many relationship with Interest. On the interests table I would put
- id
- user_id
- interest_id (foreign of user)
- type of like maybe an integer/enum that maps to something like superlike, hotty, I wanna do stuff to you etc (yes this is creeping me out writing it, but it's a dating site...)
- timestamps
- SoftDelete timestamp.
Then if a user is interested in someone, it can be something like
$user->interests()->updateOrCreate([
'interest_id' => $interestedIn->id,
'interest_type' => Interested::Superlike,
]);
If ($interestedIn->whereHas('interests', fn($query) => $query->where('interest_id', $user->id)))
{
//Remember that interests are soft-deletable.
Interest::where('user_id', $user->id)->where('interested_id', $interestedIn->id)->delete();
Interest::where('user_id', $interestedIn->id)->where('interested_id', $user->id)->delete();
if($user->id < $interestedIn->id) // "primary" user is the one with the lowest ID to stop duplicates in pivot
{
DB::table('matches')->insert([
'match_id_1' => $user->id,
'match_id_2' => $InterestedIn->id,
//timestamps etc
]);
} else {
DB::table('matches')->insert([
'match_id_1' => $InterestedIn->id,
'match_id_2 => $user->id,
//timestamps etc
]);
}
}
Other things you would need to consider is that when retrieving the matches, you would need to retrieve the match based on the one with the lowest ID in the first column, so your retrieval would need to be something like:
$matchesOfUser = Match::where('match_id_1', $user_id)->orWhere('match_id_2', $user_id)->get();
Other things you might want to look at is if users ever unmatched you would have to deal with the soft delete and they would either have to have the soft delete removed on interests if they rematched, or more likely when you retrieved a list of possible interests you would need to do something like the following (as well as checking that they are even interested in each other in like a gender/sexuality way anyway):
$possibleInterests = User::whereDoesntHave('interests' fn($query) => $query->where('interest_id', $user_id)->withTrashed())->get();
PPS: Check my code there may be errors I've not tested it...
