问题描述
我有一个基于Django的网络应用程序，用户可以彼此聚集和聊天。我刚刚完成了一个功能，用户可以制作自己的聊天组，以任何感兴趣的话题为中心。这些可以是私人或公开可见。
I have a Django-based web-app where users congregate and chat with one another. I've just finished writing a feature whereby users can make their own "chat groups", centered around any topic of interest. These could either be private, or publically visible.
我的下一个挑战是显示一个所有现有的公共群组，分页，并按最先发生的群组排序排序。经过深思熟虑之后，我决定最发生的群体是在之前的 60分钟中看到最独特的游客（无声或其他）的群组。可以肯定的是，唯一的，我的意思是不同的用户，而不是同一个用户一次又一次地打到一个组。
My next challenge is showing a list of all existing public groups, paginated, and sorted by the most happening group first. After some deep thinking, I've decided that the most happening group is one which sees the most unique visitors (silent or otherwise) in the previous 60 mins. To be sure, by unique I mean distinct users, and not the same user hitting a group again and again.
最有效的方式是什么在我的基于类的视图相关联的这个受欢迎程度列表的 get_queryset（）方法中获得所需的有序查询集？其次，最有效的方式是在同一个查询集中的每个组对象中注释总体不同视图，以便我可以另外显示意见，根据目前的热点排序？
What's the most efficient way to get the desired, ordered query-set in the get_queryset() method of my class-based view associated to this popularity listing? Secondly, what's the most efficient way to also annotate total distinct views to each group object in the same queryset, so that I can additionally show total views, while sort according to what's currently hot?
相关模型是：
class Group(models.Model):
topic = models.TextField(validators=[MaxLengthValidator(200)], null=True)
owner = models.ForeignKey(User)
private = models.CharField(max_length=50, default=0)
created_at = models.DateTimeField(auto_now_add=True)
class GroupTraffic(models.Model):
visitor = models.ForeignKey(User)
which_group = models.ForeignKey(Group)
time = models.DateTimeField(auto_now_add=True)
相关视图是：
class GroupListView(ListView):
model = Group
form_class = GroupListForm
template_name = "group_list.html"
paginate_by = 25
def get_queryset(self):
return Group.objects.filter(private=0,date__gte=???).distinct('grouptraffic__visitor').annotate(recent_views=Count('grouptraffic__???')).order_by('-recent_views').annotate(total_views=Count('grouptraffic__which_group=group'))
正如你所看到的，我在 get_queryset（self）方法中挣扎得相当强大以上，注释两次，而不是。请指教！
As you can see, I've struggled rather mightily in the get_queryset(self) method above, annotating twice and what not. Please advise!
推荐答案
您不能将 annotate（）和 distinct（）在单个django查询中。所以你可以试试如下：
You cannot combine annotate() and distinct() in a single django query. So you can try like:
date = datetime.datetime.now()-datetime.timedelta(hours=1)
下一个查询是获取独特访问者的群组交易
Next query is to get the grouptraffic with unique visitors
new_traff = GroupTraffic.objects.filter(time__gte=date).distinct('visitor','which_group').values_list('id',flat=True)
trendingGrp_ids = GroupTraffic.objects.filter(id__in=new_traff).values('which_group').annotate(total=Count('which_group')).order_by('-total')
上述查询将让您按总计排序趋势组织，如：
The above query will get you trending groupids ordered by total like:
[{'total': 4, 'which_group': 2}, {'total': 2, 'which_group': 1}, {'total': 1, 'which_group': 3}]
这里总计指不。在过去60分钟内，每个群组都有新的独一无二的访问者。
Here total refers to no. of new unique visitors for each group in the last 60 minutes.
现在遍历trenddingGrp_ids以获取具有观看次数的趋势趋势Grps：
Now iterate over trendingGrp_ids to get the trending trendingGrps with views:
trendingGrps = [Group.objects.filter(id=grp['which_group']).extra(select={"views":grp['total']})[0] for grp in trendingGrp_ids]
更新：
要获取所有公共群组，并通过测量他们在过去1小时内收到的流量对它们进行排序。
To get all public groups, and sort them by how hot they are via measuring the traffic they received in the past 1 hr.
new_traff = GroupTraffic.objects.filter(time__gte=date,which_group__private=0).distinct('visitor','which_group').values_list('id',flat=True)
trendingGrp_ids = GroupTraffic.objects.filter(id__in=new_traff).values('which_group').annotate(total=Count('which_group')).order_by('-total')
trendingGrps = [Group.objects.filter(id=grp['which_group']).extra(select={"views":grp['total']})[0] for grp in trendingGrp_ids]
trndids = [grp['which_group'] for grp in trendingGrp_ids]
nonTrendingGrps = Group.objects.filter(private=0).exclude(id__in=trndids).extra(select={"views":0})
allGrps = trendingGrps.append(nonTrendingGrps)
这篇关于高度挑战的查询过滤，排序和注释（在基于Django的应用程序中）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！