Friend request and list, database schema

Yeah looks fine to me, I'd go with something like this so its super clear.

users
id

friends
user_id friend_id confirmed

class User extends Model
{
    public function friends()
    {
        return $this->belongsToMany(User::class, 'friends', 'user_id', 'friend_id')
            ->withPivot('confirmed');
    }
}

foreach($user->friends as $friend) {
    $friend->pivot->confirmed; // boolean
    $friend; // User Model
}

@phildawson Thx, but the thing is, if I want to retrieve all the friends of a user, I check that table and the user could be "user_id" or the "friend_id", so the query of the relationship changes...

@luddinus I see what you are saying, but I would make user_id just one thing. Here's the same with another table dealing with logging the requests.

users
id

friends
user_id friend_id

requests
requester_id friend_id confirmed_at ignored_at

Kevin says Bob is his friend

1 Bob
2 Kevin

friends
2 1 // INSERTED

A friend request is made to Bob.

requests
2 1 NULL NULL // INSERTED

Bob accepts, request is timestamped.

friends
2 1
1 2 // INSERTED

requests
2 1 NOW() NULL // UPDATED

@phildawson That way "friends" will be duplicated. Is that good? Or it never minds?

friends

1 2
2 1

@luddinus

I would say it's beneficial in this circumstance to have both, rather than a single row determining the relationship.

From an architectural pov I would also always avoid having one column representing a different value when used in a different context.

A person can list their friends and not wait until the other has accepted the friendship exists. And if they want to unfriend they can the other will still be listed as their friend.

hello, i'm looking for such a friend request logic. is this best practice? or you can offer different/better database models?

@aboutsam I think this a good candidate for a repository. You could tuck away the friend request query like this:

class FriendRequestRepository
{
    public function forUser(User $user)
    {
        return FriendRequest::query()->where(function ($query) use ($user) {
            $query->where('user_1_id', '=', $user->getKey());
            $query->orWhere('user_2_id', '=', $user->getKey());
        })->with('user_1', 'user_2')->get();
    }
}

This will fetch FriendRequest models where either user_1_id or user_2_id is the given user’s ID.

However, you still need some logic to determine who the other user is. So you could map over the collection and return the other user:

class FriendRepository implements FriendRepositoryContract
{
    protected $friendRequests;

    public function __construct(FriendRequestRepository $friendRequests)
    {
        $this->friendRequests = $friendRequests;
    }

    public function forUser(User $user)
    {
        $friendRequests = $this->friendRequests->forUser($user);

        return $friendRequests->map(function ($request) use ($user) {
            if ($user->getKey() == $request->user_1_id) {
                return $request->user_2;
            } else {
                return $request->user_1;
            }
        });
    }
}

Now when calling app(FriendRepository::class)->forUser($user), you’ll get a collection of User models representing the friends of the given user.

You might want to “decorate” the repository with a caching version, too:

class CachingFriendRepository implements FriendRepositoryContract
{
    protected $friends;

    public function __construct(FriendRepository $friends)
    {
        $this->friends = $friends;
    }

    public function forUser(User $user)
    {
        $key = 'friends:user:'.$user->getKey(); // i.e.friends:user:123

        return Cache::remember($key, $minutes = 30, function () {
            return $this->friends->forUser($user);
        });
    }
}

But you’ll have to remember to clear the user’s cached friends if a request is saved or deleted:

FriendRequest::saved(function ($request) {
    Cache::deleteMultiple([
        'friends:user:'.$request->user_1_id,
        'friends:user:'.$request->user_2_id,
    ]);
});

FriendRequest::deleted(function ($request) {
    Cache::deleteMultiple([
        'friends:user:'.$request->user_1_id,
        'friends:user:'.$request->user_2_id,
    ]);
});

I think that’s enough. I’m getting carried away now! Ha-ha.

@martinbean i will figure it out if get it run on my lara installation.. thank a lot for now! :)