Design a friends table for a social networking site

Question

Hi i am facing problem regarding the structure of a table for storing the all the friends list of an user. In the below table which i have used if user 84 has requested for friendship with user 70 & 74 then user 84 is friends are 70 & 74 and vice versa. Similarly user 77 with 84.

But the problem is that if i want to find out all the friends and their detail of user 84 i.e. of user (70,74,77).

Should i enter duplicate record like image 2 Ex: if user 84 send friend request to user 70 when user 70 accept the request then another row will be inserted like user_id 70 & friend_id 84.

OR is there any mysql query to join with users table based on the column value of the friends table. Like if user_id is 84 it will join based on the friend_id and if friend_id is 84 then it will join with user table by using the user_id column.

Answer 1

After searching for a long time i found the best way to solve the problem by using a join operation as below and also without entering duplicate entries. Here is my query

SELECT  users.*  FROM friends  
  LEFT JOIN users  ON  
    (users.`id` = friends.`user_id` OR  users.`id` = friends.`friend_id`) 
        AND  
    users.`id` != '84'   
WHERE     
    friends.`user_id` = '84'   
        OR  
    friends.`friend_id`='84'

I have made user_id=84 as static one can make that dynamic as per the requirement.

Answer 2

I assume that in your system friendship is symmetrical, i.e. if A is friends with B that implies that B is also friends with A. In which case, I suggest that you change your Friend table's columns to reflect the fact that the two people have equal status in the relationship e.g.

Friend

• @ User1_Id
• @ User2_Id

With that in place, you could do a query something like this

SELECT * FROM User WHERE User.UserId IN (
    (SELECT User1_Id FROM Friend WHERE User2_Id = MY_USER_ID)
    UNION
    (SELECT User2_Id FROM Friend WHERE User1_Id = MY_USER_ID)
)

This uses a sub-select rather than a join, plus a union to get a single list of friends' user ids depending on which way around the users are listed in the Friend table. Documentation of sub-query and union.

It's probably worth keeping it like this, rather than simplifying the code by having 2 rows in your Friends table for each relationship - (A, B) and (B, A). If you have N users the Friends table will have up to N*(N-1) rows if you duplicate and half that if you don't.

Answer 3

SELECT * FROM friends AS f 
LEFT JOIN users AS u ON f.user_id = u.user_id 
     AND f.friend_id = u.user_id 
WHERE f.friend_id = 84 OR f.user_id = 84

I didn't try this but hope will work for you.

Answer 4

Instead of doing this my suggestion is to use below structure to handle this issue. Create two different table. Namely 1- Frindship 2- FreindshipStatus

1-

2-

In friendship table when userId1 sends friend request to userId2 then recoed get inserted with status as 1. As userId2 accepts friend request then status becomes 2. If userid2 Canceled request then status become 3. And even if u want to fetch data. supposeu want to find who is friend with user id 3. Then use query like this-

SELECT userId1, userId2 from friendship where userId2=3 OR userId1 =3 AND status =2

No need to enter duplicate entries.

Answer 5

Based on first table design, you can use UNION for that in order to get all friends for particular user ID:

SELECT f.friend_id FROM friends f WHERE f.user_id = 84
UNION ALL
SELECT f.user_id FROM friends f WHERE f.friend_id = 84

Output is:

+-----------+
| friend_id |
+-----------+
|        70 |
|        74 |
|        77 |
+-----------+
3 rows in set

Answer 6

This query will give you all the entries that includes user XXXX's friends

select * from friendshipTable where user_id = XXXX or friend_id = XXXX

Answer 7

You can use join query to fetch the result. No need for adding duplicate entries