How to get followers/following listing from mysql query

Question

This is my followers /following database table schema. I want to fetch all data for a particular user. eg user_id 6 is following 7,8,9 and is followed by 7. My purpose is to find if that particular user follows his followers. How can I achieve that?

id     |   user_id   |  follower_id
------------------------------------
1            6             7
2            6             8
3            7             6
4            8             15
5            6             9
6            5             7

Answer 1

You can use separate queries:

To get followers:

SELECT GROUP_CONCAT(DISTINCT t1.follower_id) as followers FROM test_f t1 WHERE t1.user_id = 6

To get following:

SELECT GROUP_CONCAT(DISTINCT t2.user_id) as following FROM test_f t2 WHERE t2.follower_id = 6

Answer 2

I have added dummy table data here:

You will get follower list of each user as:

SELECT user_id, GROUP_CONCAT(DISTINCT follower_id SEPARATOR ', ') as following 
FROM followers GROUP BY user_id

Results like:

You can get followers and followings in same query.

SELECT DISTINCT f.user_id, 
o.following,
e.follower
FROM followers f
LEFT JOIN
(
    SELECT followers.user_id, GROUP_CONCAT(DISTINCT follower_id SEPARATOR ', ') as following 
    FROM followers GROUP BY user_id
) as o ON f.user_id = o.user_id
LEFT JOIN
(
    SELECT followers.follower_id, GROUP_CONCAT(DISTINCT user_id SEPARATOR ', ') as follower 
    FROM followers GROUP BY follower_id
) as e ON f.user_id = e.follower_id