SQL Latest photos from contacts (grouped by contact)
- by kitsched
Hello,
To short version of this question is that I want to accomplish something along the lines of what's visible on Flickr's homepage once you're logged in. It shows the three latest photos of each of your friends sorted by date but grouped by friend.
Here's a longer explanation: For example I have 3 friends: John, George and Andrea. The list I want to extract should look like this:
George
Photo - 2010-05-18
Photo - 2010-05-18
Photo - 2010-05-12
John
Photo - 2010-05-17
Photo - 2010-05-14
Photo - 2010-05-12
Andrea
Photo - 2010-05-15
Photo - 2010-05-15
Photo - 2010-05-15
Friend with most recent photo uploaded is on top but his or her 2 next files follow.
I'd like to do this from MySQL, and for the time being I got here:
SELECT photos.user_id, photos.id, photos.date_uploaded
FROM photos
WHERE photos.user_id IN (SELECT user2_id
FROM user_relations
WHERE user1_id = 8)
ORDER BY date_uploaded DESC
Where user1_id = 8 is the currently logged in user and user2_id are the ids of friends. This query indeed returns the latest files uploaded by the contacts of the user with id = 8 sorted by date. However I'd like to accomplish the grouping and limiting mentioned above.
Hopefully this makes sense. Thank you in advance.