facebook messages result set like part 2
- by David
Hey guys, I don't think I'm getting it right.
Here is my most recent query that I believe is almost working.
SELECT max(sender_id) as sender_id,
subject, MAX(id) as message_id, MAX(created_at) as updated_at
FROM messages
where (recipient_id = #{current_user} and recipient_deleted = 'f')
or sender_id = #{current_user}
GROUP BY subject
ORDER BY max(created_at) DESC ;
Basically the goal is to fetch all recent messages of a user, group them by subject, and make sure that the photo that is showing up is the other party's photo, never the current user. (also need the most recent message body and message count but that is for another time)
Here is what I think is wrong. doing max(sender_id) is wrong, since the current_user id could be higher than the sender's id. I am using the sender_id in the html page to fetch the user's photo.
