Question 114432 in PostgreSQL Question posted in
The official documentation can be found here.

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

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 ?

Tags:

2

Answers


  1. 0

    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 |
    Login or Signup to reply.
  2. 0

    I believe you can accomplish this with a self-join using a left outer join and evaluating the success of the join condition:

    SELECT
  f1.*, f2 is not null as DoIFollowBack
FROM
  Followers f1
  left join Followers f2 on
    f2.FollowedId = f1.UserId and
    f1.FollowedId = f2.UserId
WHERE
  f1.FollowedId = 100

    This should scale nicely should you expand it to more (or even all) users.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search