How to implement the storage of friends in the database?

Question

PHP

How to store user-friend relationships is understandable. We create a friend table in which there are two columns - user and friend. We make a key on two fields. Accordingly, user A added user B as a friend - a corresponding entry appeared. When user B has confirmed the friend request, a symmetrical record is created.

Further, if we want to display the user's friends, we write something like
select f1.friend from friends f1 join friends f2 on f1.user=f2.friend and f2.user=f1.friend where f1.user=:user_id

But what if we want to display not only the so-called “mutual friends”, but also friend requests and unconfirmed friend requests.
i.e. for example:
User user1 added users user2, user3 as friends. user2 confirmed the request. user4 added user1 as a friend. And user1's profile should display:
user2 (remove from friends)
user3 (revoke application)
user4 (accept application)

How to write a request? Or one request is not enough?

Answer 1

But what if you store it a little differently?
For example, do not create a duplicate entry in the opposite direction, but initially use one more field in the ternary number system: ± 1 when one user added another (the sign indicates the direction of the application) and 0 when the application is confirmed.
Then the request will be one for the selection of the pair, and the states to withdraw/accept the request and remove from friends will be determined by the sign of the number in the additional field.
Of the obvious advantages. It will take up about half as much space - a trifle, but nice.
I can't think of any cons right away.

Answer 2

Add one more column — connection type. Will be - user, friend, relation_type, where relation_type, for example, friend/not_approved_friend/declined_friend/follower. And just a restriction on the type of connection will be added to the selection.

Answer 3

So keeping friends is not a good idea. If the site has 1000 users and each has 100 friends, then you will have a table with 200,000 entries, and rather slow queries for such a simple thing as a list of friends.
I would do denormalization, that is, I would simply store the list of friends as a string in the user model field :)
Fast queries, since parsing a string of several hundred characters will be faster than querying a table of several hundred thousand records, and such a method is in development easier.
Although maybe I'm wrong and the difference in performance will not be that big.
Actually, I'm answering your question. You just need to get a list of all records where user1 is in the user or friend field, then determine in the code whether these are mutual friends (there is a record where user1 is in the friend field, and a record where user1 is in the user field, the second user is the same in both queries) or one of them just sent a request. The query would be something like:
select user, friend from friends where user=:user_id or friend=:user_id

Answer 4

theoretically, you need two sets - the user's friends, and the opposite - those who called your user as friends.

1. select f1.friend from friends f1 where f1.user=:user_id
2. select f1.user from friends f1 where f1.friend=:user_id

the intersection will give mutual friends, subtractions - non-reciprocal on one side and on the other.
the SQL syntax says that this can easily be done with a complete join, which is usually not implemented but is emulated by joining left and right. I mean:

select * from friends f1 LEFT join friends f2 on f1.user=f2.friend and f2.user=f1.friend where f1.user=:user_id
UNION
select * from friends f1 RIGHT join friends f2 on f1.user=f2.friend and f2.user=f1.friend where f2.friend=:user_id

and there will be 3 options - both f1 and f2 are not null - mutual friends or one of them is null
But this is not the most effective way, I would agree with serso and advise you to have an additional column with the type (at the same time you can have several of them - friends, spouses / lovers , colleagues ) and, when adding, check the inverse relationship and immediately fill in the column. On a loaded system, this can be done in deferred mode.