How to get data from 3 models connected with Has-One and Have-Many relationship?

Question

I have a module of friendship request in my project. Below 3 tables are being used in it:-

1. Users
2. User_profile
3. Friendship

Users :- Id,slug,Name,Email, Password

UserProfile :- Id, user_slug, Profile_pic, DOB..etc.

Friendship :- Id, User_slug, Friend_slug, Status

Relationships:-

User Model:-

public function Profile(){
    return $this->hasOne('UserProfile','user_slug','slug')->first();
}

public function sentFriendshipRequests(){
    return $this->hasMany('Friendship','user_slug','slug');
}

public function receivedFriendshipRequests(){
    return $this->hasMany('Friendship','friend_slug','slug');
}   

UserProfile Model:-

public function User(){
    return $this->belongsTo('User','user_slug','slug');
}

Friendship Model:-

public function receiver(){
    return $this->belongsTo('User','friend_slug','slug');
}


public function sender(){
    return $this->belongsTo('User','user_slug','slug');
}

Goal:- I want to display list of pending friendship request received by an user.

Data Required:- All friendship request with pending status for current logged user & Name,Slug,Profile_pic of friendship request sender.

My Approach:-

$friendship_requests= Auth::user()->receivedFriendshipRequests();

foreach($friendship_requests as $frnd_req)
{
    $sender_user=User::where('slug',$frnd_req->user_slug());
}

Is there any other proper way to get this data by using Eloquent Relationship approach,without using join. I means how to get data using HasOne and HasMany relationship in one single query.

Any help or advice is greatly appreciated.

Thanks

Answer 1

This is a self referencing many-to-many relationship, so you don't need those hasMany/belongsTo relations at all. You can simply use one belongsToMany for own requests and another one for received requests.

Read this first: https://stackoverflow.com/a/25057320/784588

Then add these relationships:

// pending requests of mine
function pendingFriendsOfMine()
{
  return $this->belongsToMany('User', 'friendship', 'user_slug', 'friend_slug')
     ->wherePivot('accepted', '=', 0)
     ->withPivot('accepted');
}

// pending received requests
function pendingFriendOf()
{
  return $this->belongsToMany('User', 'friendship', 'friend_slug', 'user_slug')
     ->wherePivot('accepted', '=', 0)
     ->withPivot('accepted');
}

// accessor allowing you call $user->friends
public function getPendingFriendsAttribute()
{
    if ( ! array_key_exists('pendingFriends', $this->relations)) $this->loadPendingFriends();

    return $this->getRelation('pendingFriends');
}

protected function loadPendingFriends()
{
    if ( ! array_key_exists('pendingFriends', $this->relations))
    {
        $pending = $this->mergePendingFriends();

        $this->setRelation('pendingFriends', $pending);
    }
}

protected function mergePendingFriends()
{
    return $this->pendingFriendsOfMine->merge($this->pendingFriendOf);
}

then yuou simply load it using nested relations:

$user = Auth::user();
$user->load('pendingFriendsOfMine.profile', 'pendingFriendOf.profile');
// the above will execute 4 queries - 2 for requests, 2 for related profiles

$pendingFriends = $user->pendingFriends; // for all pending requests
// or
// $user->load('pendingFriendOf.profile'); // 2 queries in this case
// $pendingRequests = $user()->pendingFriendOf; // for received requests only

foreach ($pendingFriends as $user) {
  $user->profile; // eager loaded profie model
}

---

Also, here a few errors you have in your code:

// there can't be first() in the relation definition
// and it is not needed anyway
public function Profile(){
    return $this->hasOne('UserProfile','user_slug','slug')->first();
}



// You never want to run this User::where() ...
// in a foreach loop, for it will result in n+1 queries issue
// You need eager loading instead.
foreach($friendship_requests as $frnd_req)
{
    $sender_user=User::where('slug',$frnd_req->user_slug());
}