Making a user profile activity feed

Question

I need to build an activity feed to go on each users profile page showing what they have been doing on the site.

There is three tables: comments, ratings, users

I want the feed to include the comments and ratings that the user has posted.

in the comments and ratings table it stores the user id of the user who posted it, not the username, so in for each item in the news feed it needs to select from the users table where the user id is the same to retrieve the username.

All the entries in the feed should be ordered by date.

Here is what ive got even though i know it is not correct because it is trying to match both with the same row in the users table.

SELECT comments.date, comments.url AS comment_url, comments.user_id, ratings.date, ratings.url AS rating_url, ratings.user_id, users.id, users.username
FROM comments, ratings, users
WHERE comments.user_id=%s
AND comments.user_id=users.id
AND ratings.user_id=%s
AND ratings.user_id=users.id
ORDER BY ratings.date, comments.date DESC

Answer 1

JOIN. It seems you know that, but here's how: SELECT * FROM comments LEFT JOIN users ON comments.user_id = users.id

Thus, as far as I can tell, you're trying to order two separate things at the same time. The closest I think I can come up with would be something like:

(SELECT comments.date AS date, users.username AS name, comments.url AS url CONCAT('Something happened: ',comments.url) AS text 
    FROM comments LEFT JOIN users ON comments.user_id = users.id 
    WHERE users.id = %s)
UNION
(SELECT ratings.date AS date, users.username AS name, ratings.url AS url CONCAT('Something happened: ',ratings.url) AS text
    FROM comments LEFT JOIN users ON comments.user_id = users.id 
    WHERE users.id = %s)
ORDER BY date DESC LIMIT 0,10

Note that the columns of both parts of the union match up. I'm pretty sure that that is required for something like this to work. That's why I have that CONCAT statement, which lets you build a string that works differently between ratings and comments.