Joining Tables MySQL/Laravel eloquent based on condition to disallow duplicates

So I have two tables I am looking to join data.

So currently I have this in laravel controller.

$profile = DB::table('profiles')
        ->join('users', 'profiles.user_id', '=', 'users.id')
        ->leftJoin('friends', function ($join) {
            $join->on('user_a_id', '=', 'profiles.user_id')->orOn('user_b_id', '=', 'profiles.user_id')->orOn('friend_a_id', '=', 'profiles.user_id')->orOn('friend_b_id', '=', 'profiles.user_id');
        })
        ->select('profiles.*', 'users.first_name', 'users.last_name', 'friends.friend_a_id', 'friends.user_a_id', 'friends.friend_b_id', 'friends.user_b_id')
        ->orderBy('created_at', 'desc')
        ->where('user_id', '!=', $user_id)
        ->get();

Here I am getting all the users profiles and matching the friends with them. Its a left join where all the data from the profile table is brought in, but only the the data the matches the condition is brought in on the right friends table.

So here's what's going on. The friends table holds the user Id's for the person who is making the request. So user_i_id is always the first person initiating the request, and the friend_a_id is the person he is requesting. The user_b_Id is the person responding to the request.

The profile table is each users profile.

So I only want to bring in user profiles once and match them with a friend request to see if the current user if friends with each user or not.

Here's the issue on the front end.

You see...I only want David to appear once. All profiles only once.

Here is the html template

             <div class="row">
                    @foreach ($profiles as $profile)
                        <div class="col-sm-12">
                        <div class="col-xs-12">
                        <div class="col-xs-3">
                            <img class="img-responsive" src="/" alt=" 's profile picture" />
                        </div>
                        <div class="col-xs-3">
                            <div> </div>
                        </div>
                            <form action="/friends" method="post" enctype="multipart/form-data">
                              <input type="hidden" name="_token" value="{!! csrf_token() !!}">
                                  <input type="hidden" class="form-control" id="friendRequest" name="friendRequest"
                                  value="">
                                <div class="col-xs-3">
                                  <button type="button" class="submit_btn btn btn-default" data-friendId="">
                                  <?php if($profile->user_a_id === $mainUser->id && $profile->friend_b_id !== $mainUser->id) { 
                                    echo ("Friend Request Pending");
                                  } else if ($profile->friend_a_id === $mainUser->id && $profile->user_b_id !== $mainUser->id) {
                                    echo ("Request Awaiting Approval");
                                    } else if($profile->user_a_id === $mainUser->id && $profile->friend_b_id === $mainUser->id || $profile->friend_a_id === $mainUser->id && $profile->user_b_id === $mainUser->id) {
                                    echo ($pageUser->first_name . "'s Friend");
                                    } else {
                                    echo ("Send Friend Request");
                                    }?></button>
                                </div>
                            </form>

                    <div class="col-xs-3">
                        <span>See Meal Plan</span>
                    </div>
                        </div>
                        </div>
                    @endforeach
             </div>

from Newest questions tagged laravel-5 - Stack Overflow http://ift.tt/28PVjBa
via IFTTT