如何查询关注的用户帖子？

Question

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";

Answer 1

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.