SQL来自联系人的最新照片(按联系人分组) [英] SQL Latest photos from contacts (grouped by contact)

问题描述

这个问题的简短版本是，我要在登录后按照Flickr主页上显示的内容完成操作.它显示了每个朋友的三张最新照片，这些照片按日期排序，但按朋友分组

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.

这里有一个更长的解释:例如，我有3个朋友:约翰，乔治和安德里亚.我要提取的列表应如下所示:

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:

乔治

• 照片-2010-05-18
• 照片-2010-05-18
• 照片-2010-05-12

约翰

• 照片-2010-05-17
• 照片-2010-05-14
• 照片-2010-05-12

安德里亚

• 照片-2010-05-15
• 照片-2010-05-15
• 照片-2010-05-15

最近上传照片的朋友位于最上方，但其后2个文件紧随其后.

Friend with most recent photo uploaded is on top but his or her 2 next files follow.

我想从MySQL做到这一点，而暂时到达这里:

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 

其中user1_id = 8是当前登录的用户，user2_id是朋友的ID.该查询确实返回ID = 8的用户联系人按日期排序上传的最新文件.但是，我想完成上面提到的分组和限制.

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.

推荐答案

有时候，达到目的的唯一方法是创建一段非常丑陋和令人发指的SQL，以至于进行多个查询的替代方法很有吸引力:-)

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.

这篇关于SQL来自联系人的最新照片(按联系人分组)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！