Twitter style tracking table in SQL

Question

I have a servo / next base table in mySQL that looks like this.

id user_id follower_id 1 userA userB 2 userC userA 3 userA userC 4 userB userC 5 userB userA 

I checked these topics, but could not find what I need

database design for subscribers and subscribers

SQL Followers and Followers

I need to have a system like this:

Suppose we are userB (we follow user A, followed by userC and userA)

I like to return a result that includes the next tracking / next state. For example, for userB:

 id followedBy areWeFollowing 1 userA 1 2 userC 0 

Thank you for your help!

Arda

Answer 1

With this query, to find out who you are following, follow him.

 SELECT ff1.follower_id as followedBy, ( select count(follower_id) from follower_following as ff2 where ff2.user_id = ff1.follower_id and ff2.follower_id = ff1.user_id ) as areWeFollowing FROM follower_following as ff1 where user_id = 'userB';