Select posts from users which are followed

Question

I have spent hours and still couldn't able to do it correctly. I have user table, and posts table.

User table

username | followers        | following

john     | mary,steven,joel | anthony,matthew

Posts table

fromuser | post                    | date 

mary     | Bla bla, today is cold. | 1475982647

I need to get posts from the users who are being followed. Tried this;

SELECT * FROM posts WHERE $member[nick] 
IN (SELECT followers FROM users WHERE $member[nick] IN followers ORDER BY id DESC limit 10

This doesn't return any posts. What is the correct way to do it ?

@FastSnail, its just for followers and following rows, since comma is not allowed, I used it once. Are there any problems ? — caner taşdemir, Sep 30 '16 at 14:01
@canertaşdemir yes [Is storing a delimited list in a database column really that bad?](http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) — Madhawa Priyashantha, Sep 30 '16 at 14:03

Tim Biegeleisen · Accepted Answer · 2016-09-30T14:23:35.983

1

SELECT *
FROM posts
WHERE FIND_IN_SET(fromuser,
                  (SELECT following FROM users WHERE username = 'john')) > 0

This query uses FIND_IN_SET to check each user appearing in posts whether or not he is being followed by a given user (John in this case).

edited Sep 30 '16 at 14:23

answered Sep 30 '16 at 14:06

Tim Biegeleisen

@canertaşdemir I updated with logic which will consider either empty string or `NULL` as meaning there are no followers for that user. – Tim Biegeleisen Sep 30 '16 at 14:10
In your query, where should I include the current user ? $member[nick] – caner taşdemir Sep 30 '16 at 14:12
@canertaşdemir Just add an additional check to the `WHERE` clause. – Tim Biegeleisen Sep 30 '16 at 14:14
So this will get the posts from users which are followed by "John", correct ? – caner taşdemir Sep 30 '16 at 14:14
Your language is _very_ unclear. Let me update again – Tim Biegeleisen Sep 30 '16 at 14:16
I just want to get posts from users which are being followed, for example, in facebook on homepage, we see posts only from people we follow. I try to explain this. – caner taşdemir Sep 30 '16 at 14:17
1

I updated again. Now it finds the posts from users who are _followed_ by John. – Tim Biegeleisen Sep 30 '16 at 14:24

1 Answers1