SQL Latest photos from contacts (grouped by contact)

Question

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.

Answer 1

Sometimes, the only way to acheive an end is to create a piece of SQL so ugly and heinous, that the alternative of doing multiple queries becomes attractive :-)

I would just do one query to get a list of your friends then, for each friend, get the three most recent photos. Something like:

friend_list = sqlexec "select user2_id from relations where user1_id = "
                      + current_user_id
photolist = []
for friend in friend_list:
    photolist += sqlexec "select user_id, id, date_uploaded from photos"
                 + " where user_id = "
                 + friend.get("user2_id")
                 + " order by date_uploaded desc fetch first 3 rows only"

# Now do something with photolist

You don't have to do it as one query any more than you're limited to one regular expression for matching a heinous pattern. Sure it'd be nice to be "clever" but it's rarely necessary. I prefer a pragmatic approach.