News feed help for activity stream

Question

building a facebook-like newsfeed and need help. We would like to start off with just comments in the news feed.

We would like to display all of the comments of everybody the current user is following on the site. There is a followers table with the follower id and the followee id. Here is a basic query of the followers table to select everybody the current user is following:

SELECT follower_id, followee_id
FROM followers
WHERE follower_id = $current_id

There is a users table that contains the user_id and name of the user. It needs to be selected for each comment to show the commenters name.

There is also a comments table. Here is a basic query for all the comments and users to select their name:

SELECT *, CONCAT(users.first_name,' ',users.last_name) AS name,
FROM comments, users
WHERE comments.user_id = users.id
ORDER BY comments.date DESC

The problem is that we need to have a list of all the most recent comments from anybody you are following. Can anybody help?

Answer 1

It looks like you just need to combine the two queries you gave. Something like:

SELECT *, CONCAT(users.first_name,' ',users.last_name) AS name,
FROM comments, users
WHERE comments.user_id = users.id
AND comments.user_id IN 
   (SELECT followee_id
   FROM followers
   WHERE follower_id = $current_id)
ORDER BY comments.date DESC