Query to get latest 10 rows by using id's from other table

Question

I have a follow system on my website, where you're able to follow other users.

On the home page of the website, I'm trying to make it show the latest 10 posts from the users you're following (not 10 from each person you're following, but 10 in total).

I have one table called followers with the following structure:

id  |  user_id  |  following_id
 1        20           52
 2        20           55
 1        20           75
...      ...          ...

Where user_id is your id and follow_id is the id of the users you're following.

I then have a table called posts where all the posts from users are collected.

What I'm trying to do now is create a query that gets the latest 10 posts from the users you're following (ordered by date).

Here's what I've made so far:

/* Select all users this person is following */
$stmt = $cxn->prepare('SELECT following_id FROM followers WHERE user_id = ?');
$stmt->bind_param('i', $user_id);
$stmt->execute();
$result = $stmt->get_result();

/* If a result exists, continue. */
if ($result->num_rows) {
    while ($row = $result->fetch_assoc()) {
        // not sure what to do here, how would the query look?
    }
} else {
    echo "You aren't following anyone!";
}

I'm not sure what the query would/should be to get a total of 10 of the latest posts from the people you're following.

Please help!

Answer 1

You're on the right track, but your main selection is still the posts, rather than the followers - those are the subquery. You probably want something like this.

SELECT * FROM posts WHERE poster_id IN 
    (SELECT following_id FROM followers WHERE user_id = ?)
ORDER BY posted_at DESC
LIMIT 10

The query you wrote is still there, but it's been surrounded by the query to get the actual posts. Substitute poster_id and posted_at for what you call them in your posts table.

Answer 2

SELECT *
FROM posts p
JOIN followers f
ON p.author_id = f.following_id
WHERE f.user_id = ?
ORDER BY p.date DESC
LIMIT 10;