CODEWITHSUNDEEP
mysql
jannis
jannis

Reputation: 1

Mysql: Show all users that a user is following and add dynamic column if i also follow that user

I have a website with a twitter-like follower system. I have a mySQL-table "followers", like this:

'followers' TABLE STRUCTURE:

| user_id    | follow_id |
--------------------------
| 1          | 2         |
| 1          | 3         |
| 2          | 3         |
| 2          | 4         |

Now I want to get all the users listed that f.e. user 2 is following (so 3 and 4) but I also want to show if the current user logged in (let's assume user 1) is also following a user. This information should be added dynamically to a new column "ifollow", like this:

Expected result when checking which users "user 2" is following:

| user_id    | follow_id | ifollow    |
---------------------------------------
| 2          | 3         | true       |
| 2          | 4         | false      |

Right now I just get all the users a user is following and then check in the view with a function like "doIFollow($id)" to display the right follow/unfollow-buttons but I think it would be way better to just get all the needed information with the query. Does anyone know how this ist possible?

Upvotes: -1

Views: 68

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can use a correlated subquery for this:

SELECT user_id, follow_id,  
       COALESCE((SELECT true       
                 FROM followers AS f2
                 WHERE f2.follow_id = f1.follow_id AND 
                       f2.user_id = 1), false)
FROM followers AS f1
WHERE user_id = 2

Edit: (credit goes to @Mjh)

The same thing can also be accomplished with a simple LEFT JOIN operation:

SELECT f1.user_id, f1.follow_id,  
       IF(f2.user_id IS NULL, false, true) AS ifollow
FROM followers AS f1
LEFT JOIN followers AS f2 
   ON f1.follow_id = f2.follow_id AND f2.user_id = 1
WHERE f1.user_id = 2

Upvotes: 1

Related Questions