Get content from Database when relationship (pivot table) exists

Hi there,

i build a small project to post as post, follow other users and see them posts - when they are friends / follows.

I have three tables (simplified)

// posts
id
user_id
content

// users
id 
username

// relations (i create two inserts a -> b, b -> a)
user_id
friend_id
is_active (the requested user must approve the request)

To get the posts from one user is no problem. But how can i get the data when these relationships are "active"? Keep in mind: The relationship must be approved. I know how i setup the database etc., but i stuck on the part when i check "these two users are friends, get the data".

When a user have 100 friends, it must get the posts from all friend, there are active.

Hope you can help.

lostdreamer_nl

8 years ago

Level 53

setup the user model to have 2 methods for their friends: 1 that returns all friends (and open requests) and 1 that only returns the active friends that have been accepted.

When you want a user with all posts of their active friends, you use the activefriends relation.


public function friends() 
{
    return $this->belongsToMany(User::class, 'relations', 'friend_id');
}

public function activefriends() 
{
    return $this->belongsToMany(User::class, 'relations', 'friend_id')->wherePivot('is_active', true);
}

Now you can do:

$user = User::with('activefriends.posts')->find(3);
dd($user->toArray());

Kunzilla

8 years ago

Level 4

Hi @lostdreamer_nl ,

thanks for your reply. Nice concept, but i want alle posts in one collection with the user information.

In your solution i get the users, with the posts.

lostdreamer_nl

8 years ago

Level 53

in that case, the easiest way would be:

$user = User::find(1);
$posts = Post::whereIn('user_id', $user->activefriends->pluck('id'))->with('user')->get();

It would be 4 queries:

Get user 1
Get all active friends of this user
Get all posts for these user Ids
Get all users for the posts

Please or to participate in this conversation.