SQL design in Follower/Followed model for relational databases and Follow Back functionality

Question

Suppose I want to keep who follows who in a simple table that has two fields ;

UserId and FollowedId and I have a simple data set like ;

UserId	FollowedId
1	100
2	100
3	100
4	100
5	100
6	100
100	5
100	2

I just use SELECT*FROM Followers WHERE FollowedId = 100 when I want to list all followers of the user with ID 100.

However, I want the following information in my followers list when I query ;

Who I follow among those following me.

I don't directly want who I follow and who follows like an intersection.

I want the result look like following when I query the follower list of a user with ID 100;

UserId	FollowedId	DoIFollowBack
1	100	false
2	100	true
3	100	false
4	100	false
5	100	true
6	100	false

I can do it using another query but just wanted to ask the most efficient way of doing it ?

Having another column to keep this bidirectional relation ? Make another query once I get the follower list to see which ones among them I follow ?

Answer 1

You could use an intersect within a CTE to get the userids, and then Case Expression for your third column:

with follow_back as (
  select userid, followedid
  from my_data
  where followedid = 100
  intersect
  select followedid, userid
  from my_data)
select userid, followedid,
case 
  when userid in (select userid from follow_back) then 'true' else 'false' end DoIFollowBack
from my_data
where followedid = 100

Output:

| userid | followedid | DoIFollowBack|
| ------ | ---------- | ------------ |
| 1      | 100        | false |
| 2      | 100        | true  |
| 3      | 100        | false |
| 4      | 100        | false |
| 5      | 100        | true  |
| 6      | 100        | false |