CODEWITHSUNDEEP
phpmysql
Alex K
Alex K

Reputation: 111

How to query for followed user posts?

I am trying to get the posts from the users the current user is following. And also the post owners username and avatar. My tables look like this

Users table

User Id | Username | Avatar

Posts Table

post_id | user_id | body | image

Follows Table

leaders_id | followers_id

Here i s my current query

query = "SELECT *
            FROM posts
            WHERE $user_id IN(
                SELECT leader_id 
                FROM follows 
                WHERE follower_id=$user_id)
            ORDER BY date DESC
            LIMIT 10";

Upvotes: 0

Views: 149

Answers (2)

Joey Zhang
Joey Zhang

Reputation: 348

Are you looking for something like as follows?

SELECT p.* FROM posts AS p, follows AS f 
WHERE p.user_id=f.leader_id AND f.followers_id=$user_id

If you want the leaders' information, you may join the users table.

SELECT p.*, u.* FROM posts AS p, follows AS f, users AS u 
WHERE p.user_id=f.leader_id AND p.user_id=u.user_id AND f.followers_id=$user_id

If you want the follower's information, I would recommend you to use a separate query to users table, since the information would repeat in the query results otherwise.

Upvotes: 1

Matt Altepeter
Matt Altepeter

Reputation: 956

SELECT p.* FROM posts p JOIN follows f ON p.userId = f.leaderId WHERE f.followerId = $userID;

http://www.sqlfiddle.com/#!9/2c98df/3

Upvotes: 0

Related Questions