1👍
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}]
Here total refers to no. of new unique visitors for each group in the last 60 minutes.
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]
Update:
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)
0👍
1)Create a separate function that tallies the distinct views in a chatbox. For every chatbox, put the result in a list. Return the biggest value in the list and assign it to a variable. Import the function and filter with the variable.
2) Make a set for each box. The set contains all the distinct users of the chatbox. Filter by the lenght of the set.