Pivot table with same model, how to get the "other user"?

I have a pivot model that matches two users with "user_id" and "matched_user_id" columns. What I want to achieve is something like :

For example matches table has these values :

user_id | matched_user_id
1 | 2
3 | 1
4 | 1

When I run something like :

 User::find(1)->matches

I want it to return a collection of users with the ids 2,3 and 4. So the user I'm calling it from can be on either side of the pivot table. I've been trying to solve this since 3 days now.

I tried something like this, but I'm facing an n+1 query problem with this :

$matches = Match::where('user_id', auth()->id())->orWhere('matched_user_id', auth()->id())->get();
foreach($matches as $match){
	echo $match->user_id == auth()->id() ? User::find($match->matched_user_id)->name : User::find($match->user_id)->name;
}

and honestly it doesn't look clean at all. I hope there's a better and more efficient way to do this.

tykus

1 year ago

Best Answer

Level 104

Check out Staudenmeir's Merged Relationships package. You can create a merge view migration which will allow you to then define a merge relation on the User model using that database view

1 like

motinska94

1 year ago

Level 3

@tykus Thanks for the recommendation! I asked a senior and they gave the following suggestion which worked perfectly for my use case. I also have bad experiences with external packages (English is not my main language and I can't learn well without a detailed tutorial) so I prefer the vanilla approach.

I'll still mark your comment as best answer because I'm sure your way is better than mine.

I'm leaving the code I used, in case anyone else has the same question in the future :

function getMatches(bool $loadPictures = false){
    $currentUserId = auth()->id();
    $matches = Match::query()
        ->where('user_id', $currentUserId)
        ->orWhere('matched_user_id', $currentUserId)
        ->get();

    $userIds = $matches->map(fn (Match $match) => $match->user_id == $user->id ? $match->matched_user_id : $match->user_id);

    if($loadPictures){
        $users = User::whereIn('id', $userIds)->with('profilePicture:user_id,path')->get();
    }else{
        $users = User::whereIn('id', $userIds)->get();
    }

    return $users;
}

This only runs 3 db queries (two if I don't eager load profile pictures) and even with an external package I don't think it'll get more performant than this.

Please or to participate in this conversation.