def ranked_by_average_score(teacher_id): return Student.objects.filter(teacher_id=teacher_id).annotate( average_score=Avg('lessonstudent__score'), class_rank=Window( expression=Rank(), order_by=F('average_score').desc(nulls_last=True))).order_by( F('average_score').desc(nulls_last=True))
def get_queryset(self): twentyfour = self.request.query_params.get('twentyfour', None) if twentyfour == '1': tz = pytz.timezone('Europe/London') now = datetime.datetime.now(tz=tz) now = datetime.now() date_today = now.date() time_now = now.time() datetime_now = datetime.combine(date_today, time_now) day_name_today = now.strftime("%A") tomorrow = now + timedelta(days=1) day_name_tomorrow = tomorrow.strftime("%A") meetings_today = Meeting.objects.filter( (Q(day=day_name_today) & Q(time__gte=now.time()))) #.order_by('time') meetings_tomorrow = Meeting.objects.filter( (Q(day=day_name_tomorrow) & Q(time__lte=now.time()))) #.order_by('time') rank_by_day = Window(expression=Rank(), partition_by=F("day"), order_by=F("time").asc()) all = meetings_today | meetings_tomorrow if day_name_today == 'sunday': all_ordered = all.order_by('-day_number', 'time') else: all_ordered = all.order_by('day_number', 'time') return all_ordered return Meeting.objects.all()
def test_rank(self): """ Rank the employees based on the year they're were hired. Since there are multiple employees hired in different years, this will contain gaps. """ qs = Employee.objects.annotate(rank=Window( expression=Rank(), order_by=ExtractYear(F('hire_date')).asc(), )) self.assertQuerysetEqual(qs, [ ('Jones', 45000, 'Accounting', datetime.date(2005, 11, 1), 1), ('Miller', 100000, 'Management', datetime.date(2005, 6, 1), 1), ('Johnson', 80000, 'Management', datetime.date(2005, 7, 1), 1), ('Smith', 55000, 'Sales', datetime.date(2007, 6, 1), 4), ('Jenson', 45000, 'Accounting', datetime.date(2008, 4, 1), 5), ('Smith', 38000, 'Marketing', datetime.date(2009, 10, 1), 6), ('Brown', 53000, 'Sales', datetime.date(2009, 9, 1), 6), ('Williams', 37000, 'Accounting', datetime.date(2009, 6, 1), 6), ('Wilkinson', 60000, 'IT', datetime.date(2011, 3, 1), 9), ('Johnson', 40000, 'Marketing', datetime.date(2012, 3, 1), 10), ('Moore', 34000, 'IT', datetime.date(2013, 8, 1), 11), ('Adams', 50000, 'Accounting', datetime.date(2013, 7, 1), 11), ], lambda entry: (entry.name, entry.salary, entry.department, entry.hire_date, entry.rank), ordered=False)
def get(self, request, format=None): stores = (Store.objects.all().order_by("-st_time_spent").annotate( rank=Window(expression=Rank(), order_by=F("st_time_spent").desc())) ) serializer = StoreSerializer(stores, many=True) return Response(serializer.data)
def ranked(self, from_date, to_date): queryset = self queryset = queryset.filter(comment__added_on__gte=from_date, comment__added_on__lte=to_date) rank_by_total_comments = Window(expression=Rank(), order_by=F('total_comments').desc()) return queryset.annotate(total_comments=Count('comment')).annotate( rank=rank_by_total_comments).order_by('rank', 'id')
def with_rank(self): ranked = UrbanInstituteRentalCrisisData.objects.annotate( rank=models.Window(expression=Rank(), partition_by=[models.F('year')], order_by=(models.F('aaa_units') / models.F('eli_renters')).desc()), total=models.Window(expression=models.Count(['year']), partition_by=[models.F('year')])) return ranked
def get_annotation(self, annotators, min_field, min_rounds): annotations = {a.key: a for a in annotators} return Window( expression=Rank(), order_by=self._get_ordering(annotators, min_field, min_rounds), partition_by=[ annotations[key].get_ranking_annotation(min_field, min_rounds) for key in self.metrics[:1] ], )
def leaderboard_queryset() -> QuerySet: rank_window = Window(expression=Rank(), order_by=F('elo').desc()) board = ( Leaderboard.objects.select_related('profile').order_by('-elo'). annotate(name=F("profile__name")).annotate(rank=rank_window).annotate( win_rate=Case(When(games=0, then=0), default=(Decimal('1.0') * F("wins") / F("games")) * 100, output_field=DecimalField()))) return board
def get_queryset(self): from_date = self.request.query_params.get('from_date') to_date = self.request.query_params.get('to_date') movies = super(TopViewSet, self).get_queryset() if from_date and to_date: movies = movies.filter(year__range=[from_date, to_date]) return movies.annotate(comments_count=Count('comments'), rank=Window(expression=Rank(), order_by=F('comments_count').desc()), )
def get_queryset(self): print(self.request.GET.get('limit')) rank_window = Window(expression=Rank(), order_by=F('adjusted_win_rate').desc()) filter = Q(rosters__match__ranked=True) season = self.request.GET.get('season', '') league = self.request.GET.get('league', '') if league != '': filter &= Q(rosters__match__league__id=league) if season != '': filter &= Q(rosters__match__season__id=season) profiles = ( models.SC2Profile .objects .annotate( total_matches=Count( 'rosters__match', filter=filter, distinct=True ), total_wins=Count( 'rosters__match__match_winners', filter=filter & Q(rosters__match__match_winners__profile__id=F('id')), distinct=True ), total_losses=Count( 'rosters__match__match_losers', filter=filter & Q(rosters__match__match_losers__profile__id=F('id')), distinct=True ), total_draws=F('total_matches') - F('total_wins') - F('total_losses'), win_rate=Case( When(total_matches=0, then=0), default=(Decimal('1.0') * F("total_wins") / ( F("total_wins") + F("total_losses") + (0.5 * F("total_draws")))) * 100, output_field=DecimalField(), ), rate=Case(*self.build_adj_rates(), default=1, output_field=DecimalField(decimal_places=3, max_digits=5) ), adjusted_win_rate=F('rate') * F('win_rate'), rank=rank_window ) .filter(total_matches__gte=1) .order_by('rank') ) return profiles
def with_rank(self): ranked = JCHSData.objects.exclude(datapoint='United States').annotate( asc_rank=models.Window(expression=Rank(), partition_by=[ models.F('datatype'), models.F('source'), models.F('date') ], order_by=models.F('value').asc()), desc_rank=models.Window(expression=Rank(), partition_by=[ models.F('datatype'), models.F('source'), models.F('date') ], order_by=models.F('value').desc()), total=models.Window(expression=models.Count( ['datatype', 'source', 'date']), partition_by=[ models.F('datatype'), models.F('source'), models.F('date') ])) return ranked
def with_rank(self): ranked = HudPitData.objects.annotate( rank=models.Window(expression=Rank(), partition_by=[ models.F('datatype'), models.F('geography'), models.F('year') ], order_by=models.F('value').asc()), total=models.Window(expression=models.Count( ['datatype', 'geography', 'year']), partition_by=[ models.F('datatype'), models.F('geography'), models.F('year') ])) return ranked
def _group_by_ranks(self, query, data): """Handle grouping data by filter limit.""" group_by_value = self._get_group_by() gb = group_by_value if group_by_value else ["date"] tag_column = self._mapper.tag_column rank_orders = [] if "delta" in self.order: if "__" in self._delta: a, b = self._delta.split("__") rank_annotations = { a: self.report_annotations[a], b: self.report_annotations[b] } rank_orders.append( getattr(F(a) / F(b), self.order_direction)()) else: rank_annotations = { self._delta: self.report_annotations[self._delta] } rank_orders.append( getattr(F(self._delta), self.order_direction)()) else: rank_annotations = { self.order_field: self.report_annotations[self.order_field] } rank_orders.append( getattr(F(self.order_field), self.order_direction)()) if tag_column in gb[0]: rank_orders.append(self.get_tag_order_by(gb[0])) # this is a sub-query, but not really. # in the future, this could be accomplished using CTEs. rank_by_total = Window(expression=Rank(), order_by=rank_orders) ranks = (query.annotate(**self.annotations).values( *group_by_value).annotate(**rank_annotations).annotate( rank=rank_by_total)) rankings = [] for rank in ranks: rankings.insert((rank.get("rank") - 1), str(rank.get(group_by_value[0]))) return self._ranked_list(data, rankings)
def get(self, request: Request): season = request.GET.get('season') league = request.GET.get('league') rank_window = Window(expression=Rank(), order_by=F('adjusted_win_rate').desc()) filter = Q(rosters__match__ranked=True) if league is not None: filter &= Q(rosters__match__league=league) if season is not None: filter &= Q(rosters__match__season=season) profiles = (SC2Profile.objects.annotate( total_matches=Count('rosters__match', filter=filter, distinct=True), total_wins=Count( 'rosters__match__match_winners', filter=filter & Q(rosters__match__match_winners__profile__id=F('id')), distinct=True), total_losses=Count( 'rosters__match__match_losers', filter=filter & Q(rosters__match__match_losers__profile__id=F('id')), distinct=True), total_draws=F('total_matches') - F('total_wins') - F('total_losses'), win_rate=Case( When(total_matches=0, then=0), default=(Decimal('1.0') * F("total_wins") / (F("total_wins") + F("total_losses") + (0.5 * F("total_draws")))) * 100, output_field=DecimalField(), ), rate=Case(*self.build_adj_rates(), default=1, output_field=DecimalField(decimal_places=3, max_digits=5)), adjusted_win_rate=F('rate') * F('win_rate'), rank=rank_window).filter(total_matches__gte=1).order_by('rank')) return Response(StandingsSerializer(profiles, many=True).data, status=200)
def leaderboard(request, exam_id): # Authentication user = request.user exam = get_object_or_404(Exam, pk=exam_id) # log(user=str(user), exam=exam_id, end=exam.ended, can_view=user.can_view_exam(exam), cvl=user.can_view_leaderboard(exam)) if not user.can_view_leaderboard(exam): raise PermissionDenied("You do not have permission to view the " "leaderboard for this exam") # if exam.is_ai: # return ai_leaderboard(request, exam) problems = exam.problem_list comps = exam.competitors.annotate(rank=Window( expression=Rank(), order_by=F('total_score').desc()), ).order_by('-total_score') items_per_page = per_page(comps.count()) if user.is_mathlete: # should be: if user is participating mathlete, because leaderboard can be viewed by non-participants after the contest ends comp = user.rel_comps(exam).first() # replace with participating comp # maybe break ties by time so we can just use score__lte to get rank? for i, c in enumerate(comps): if c.id == comp.id: default_page = i // items_per_page + 1 break else: default_page = 1 paginator = Paginator(comps, items_per_page) page_number = request.GET.get('page', default=default_page) page_obj = paginator.get_page(page_number) context = { 'problems': problems, 'exam': exam, 'page_obj': page_obj, 'rel_comps': user.rel_comps(exam), 'all_pages': [paginator.page(i) for i in paginator.page_range] } return render(request, 'exam/leaderboard.html', context)
def lanes(self, request, pk, *args, **kwargs): season = request.GET.get('season') league = request.GET.get('league') rank_window = Window(expression=Rank(), order_by=F('adjusted_win_rate').desc()) filter = Q(lane_rosters__sc2_profile__id=pk) if league is not None: filter &= Q(rosters__match__league=league) if season is not None: filter &= Q(rosters__match__season=season) profiles = ( models.SC2Profile .objects .prefetch_related('lane_segments', 'profile_segments') .annotate( total_matches=Count( 'lane_rosters__match', filter=filter, distinct=True ), test=Case( When(profile_segments__total_score__lt=F('lane_segments__total_score'), then=Sum(1)), default=0, filter=Q(lane_segments__profile__id=pk), output_field=DecimalField() ), total_wins=Count( 'lane_rosters__match__match_winners', filter=filter & Q(lane_rosters__match__match_winners__profile__id=F('id')), distinct=True ), total_losses=Count( 'lane_rosters__match__match_losers', filter=filter & Q(lane_rosters__match__match_losers__profile__id=F('id')), distinct=True ), total_draws=F('total_matches') - F('total_wins') - F('total_losses'), win_rate=Case( When(total_matches=0, then=0), default=(Decimal('1.0') * F("total_wins") / ( F("total_wins") + F("total_losses") + (0.5 * F("total_draws")))) * 100, output_field=DecimalField(), ), rate=Case(*self.build_adj_rates(), default=1, output_field=DecimalField(decimal_places=3, max_digits=5) ), adjusted_win_rate=F('rate') * F('win_rate'), rank=rank_window ) .filter(total_matches__gte=1) .order_by('rank')[:5] ) return Response(serializers.LaneStandingsSerializer(profiles, many=True).data, status=200)
def get(self, request: Request, profile_id: str): profile = None try: profile = SC2Profile.objects.get(id=profile_id) except SC2Profile.DoesNotExist: return Response("Profile does not exist", status=status.HTTP_400_BAD_REQUEST) ms = (Match.objects.all().annotate( players=Count('rosters', distinct=True), lobby_elo=Avg( 'rosters__sc2_profile__leaderboards__elo', filter=Q( rosters__sc2_profile__leaderboards__mode='2v2v2v2', rosters__match__league=None, ))).filter(league=None, legacy=False, players=8, season=None).exclude(lobby_elo=None)) die = (SC2Profile.objects.annotate(avg_death=Avg( 'losses__victim_number', filter=Q(losses__victim_number__gte=1, losses__match__in=ms)))) elo_1650 = ms.filter(lobby_elo__gte=1650) die_1650 = (SC2Profile.objects.annotate( avg_death=Avg('losses__victim_number', filter=Q(losses__victim_number__gte=1, losses__match__in=elo_1650)))) ms_qs = (ms.prefetch_related( 'rosters__sc2_profile__leaderboards', 'matchteam_set', 'matchteam_set__rosters__sc2_profile__leaderboards', 'matchteam_set__rosters__lane__leaderboards', ).annotate(players=StringAgg( 'rosters__sc2_profile__name', delimiter=', ', distinct=True, )).annotate(winners=StringAgg( 'match_winners__profile__name', delimiter=', ', distinct=True )).filter(rosters__sc2_profile__id=profile.id).order_by('-match_date')) ge_window = Window(expression=Rank(), partition_by=[F('match__match_date')], order_by=(F('match__match_date'), F('game_time'))) game_events = profile.game_events.filter( match__in=ms, key__id__istartswith='bunker').annotate( rank=ge_window).annotate(cancels=Case( When(Q(rank=2) & Q(key='bunker_cancelled'), then=1), default=0, output_field=DecimalField())) cancels = int(game_events.aggregate(Sum('cancels'))['cancels__sum']) game_events_1650 = profile.game_events.filter( match__in=ms.filter(lobby_elo__gte=1650), key__id__istartswith='bunker').annotate(rank=ge_window).annotate( cancels=Case(When(Q(rank=2) & Q(key='bunker_cancelled'), then=1), default=0, output_field=DecimalField())) cancels_1650 = int( game_events_1650.aggregate(Sum('cancels'))['cancels__sum']) total_matches = profile.rosters.filter(match__in=ms).count() all_chats = 'N/A' if total_matches > 0: all_chats = MatchMessage.objects.filter(profile=profile, match__in=ms).count() or 0 all_chats = all_chats / total_matches results = { 'id': profile.id, 'name': profile.name, 'avatar_url': profile.avatar_url, 'all_chats_per_game': all_chats, 'total_matches': profile.rosters.filter(match__in=ms).count(), 'first_bunker_cancels': cancels, 'first_bunker_cancels_1650': cancels_1650, 'wins': profile.wins.filter(match__in=ms).count(), 'total_matches_1650': profile.rosters.filter(match__in=elo_1650).count(), 'wins_1650': profile.wins.filter(match__in=elo_1650).count(), 'death_avg': die.get(id=profile.id).avg_death, 'death_avg_1650': die_1650.get(id=profile.id).avg_death } results['win_rate'] = (results['wins'] / max([results['total_matches'], 1])) * 100 results['win_rate_1650'] = (results['wins_1650'] / max( [results['total_matches_1650'], 1])) * 100 results['match_set'] = AbbreviatedMatchSerializer(ms_qs, many=True).data return Response(results, status=200)
def get(self, request: Request, profile_id: str): profile = None result = {} try: profile = SC2Profile.objects.get(id=profile_id) except SC2Profile.DoesNotExist: return Response("Profile does not exist", status=status.HTTP_400_BAD_REQUEST) ms = Match.objects.annotate( players=Count('rosters', distinct=True)).filter(status='final', players=8) ge_window = Window(expression=Rank(), partition_by=[F('match__match_date')], order_by=(F('match__match_date'), F('game_time'))) bunkers_cancelled = profile.game_events.filter( match__in=ms, key__id__istartswith='bunker').annotate(rank=ge_window).annotate( cancels=Case(When(Q(rank=2) & Q(key='bunker_cancelled'), then=1), default=0, output_field=DecimalField())).aggregate( Sum('cancels'))['cancels__sum'] aggregates = ms.aggregate( avg_victim=Avg('match_losers__victim_number', filter=Q(match_losers__profile=profile), distinct=True), wins=Count('match_winners', filter=Q(match_winners__profile=profile), distinct=True)) total_matches = profile.rosters.filter(match__in=ms).count() all_chat_qs = MatchMessage.objects.filter(profile=profile, match__in=ms, message_type='all_chat') message_aggregates = all_chat_qs.aggregate( count=Count('message'), num_ggs=Count('message', filter=Q(message__icontains='gg'))) profile_segments_qs = SegmentProfileItem.objects.filter( profile=profile, match__in=ms) segment_aggregates = profile_segments_qs.aggregate( num_first_team_eliminated=Count( 'match', filter=Q(eliminated=True, segment__measure='three_teams'), distinct=True), num_times_final=Count('match', filter=Q(eliminated=False, segment__measure='two_teams'), distinct=True)) result['total_matches'] = total_matches result['wins'] = aggregates['wins'] result['losses'] = total_matches - aggregates['wins'] result['avg_gg_all_chat'] = message_aggregates[ 'num_ggs'] / total_matches if total_matches > 0 else 0 result['avg_victim'] = aggregates['avg_victim'] result['avg_all_chats'] = message_aggregates[ 'count'] / total_matches if total_matches > 0 else 0 result['avg_first_bunker_cancelled'] = ( bunkers_cancelled / total_matches) if total_matches > 0 else 0 result['avg_first_team_eliminated'] = segment_aggregates[ 'num_first_team_eliminated'] / total_matches if total_matches > 0 else 0 result['avg_times_in_final'] = segment_aggregates[ 'num_times_final'] / total_matches if total_matches > 0 else 0 result['win_rate_from_final'] = result['wins'] / segment_aggregates[ 'num_times_final'] if segment_aggregates[ 'num_times_final'] > 0 else 0 return Response(result, status=200)
def get_annotation(self, annotators, min_field, min_rounds): return Window( expression=Rank(), order_by=self._get_ordering(annotators, min_field, min_rounds), partition_by=F('institution_id'), )
class StandingView(viewsets.ModelViewSet): serializer_class = serializers.StandingsSerializer permission_classes = (permissions.IsAuthenticatedOrReadOnly,) filterset_class = filters.StandingsFilter rank_window = Window(expression=Rank(), order_by=F('adjusted_win_rate').desc()) def build_adj_rates(self): container = [] for i in range(16): if i <= 5: container.append(When(total_matches=i, then=i*0.1)) elif i == 6: container.append(When(total_matches=i, then=0.6)) elif 6 < i <= 10: container.append(When(total_matches=i, then=((i-6.0) * 0.05) + 0.6)) elif i == 11: container.append(When(total_matches=i, then=0.85)) elif 11 < i <= 15: container.append(When(total_matches=i, then=((i-11.0)*0.03) +0.85)) return container def get_queryset(self): print(self.request.GET.get('limit')) rank_window = Window(expression=Rank(), order_by=F('adjusted_win_rate').desc()) filter = Q(rosters__match__ranked=True) season = self.request.GET.get('season', '') league = self.request.GET.get('league', '') if league != '': filter &= Q(rosters__match__league__id=league) if season != '': filter &= Q(rosters__match__season__id=season) profiles = ( models.SC2Profile .objects .annotate( total_matches=Count( 'rosters__match', filter=filter, distinct=True ), total_wins=Count( 'rosters__match__match_winners', filter=filter & Q(rosters__match__match_winners__profile__id=F('id')), distinct=True ), total_losses=Count( 'rosters__match__match_losers', filter=filter & Q(rosters__match__match_losers__profile__id=F('id')), distinct=True ), total_draws=F('total_matches') - F('total_wins') - F('total_losses'), win_rate=Case( When(total_matches=0, then=0), default=(Decimal('1.0') * F("total_wins") / ( F("total_wins") + F("total_losses") + (0.5 * F("total_draws")))) * 100, output_field=DecimalField(), ), rate=Case(*self.build_adj_rates(), default=1, output_field=DecimalField(decimal_places=3, max_digits=5) ), adjusted_win_rate=F('rate') * F('win_rate'), rank=rank_window ) .filter(total_matches__gte=1) .order_by('rank') ) return profiles
def _group_by_ranks(self, query, data): # noqa: C901 """Handle grouping data by filter limit.""" group_by_value = self._get_group_by() gb = group_by_value if group_by_value else ["date"] tag_column = self._mapper.tag_column rank_orders = [] rank_annotations = {} if "delta" in self.order: if "__" in self._delta: a, b = self._delta.split("__") rank_annotations = { a: self.report_annotations[a], b: self.report_annotations[b] } rank_orders.append( getattr(F(a) / F(b), self.order_direction)()) else: rank_annotations = { self._delta: self.report_annotations[self._delta] } rank_orders.append( getattr(F(self._delta), self.order_direction)()) elif self._limit and "offset" in self.parameters.get( "filter", {}) and self.parameters.get("order_by"): if self.report_annotations.get(self.order_field): rank_annotations = { self.order_field: self.report_annotations.get(self.order_field) } # AWS is special and account alias is a foreign key field so special_rank was annotated on the query if self.order_field == "account_alias": rank_orders.append( getattr(F("special_rank"), self.order_direction)()) else: rank_orders.append( getattr(F(self.order_field), self.order_direction)()) else: for key, val in self.default_ordering.items(): order_field, order_direction = key, val rank_annotations = { order_field: self.report_annotations.get(order_field) } rank_orders.append(getattr(F(order_field), order_direction)()) if tag_column in gb[0]: rank_orders.append(self.get_tag_order_by(gb[0])) # this is a sub-query, but not really. # in the future, this could be accomplished using CTEs. rank_by_total = Window(expression=Rank(), order_by=rank_orders) if rank_annotations: ranks = (query.annotate(**self.annotations).values( *group_by_value).annotate(**rank_annotations).annotate( rank=rank_by_total)) else: ranks = query.annotate(**self.annotations).values( *group_by_value).annotate(rank=rank_by_total) rankings = [] for rank in ranks: rank_value = rank.get(group_by_value[0]) rank_value = self.check_missing_rank_value(rank_value) if rank_value not in rankings: rankings.append(rank_value) for query_return in data: query_return = self._apply_group_null_label(query_return, gb) return self._ranked_list(data, rankings)
def update_rankings(): #generate averages Player.objects.update( average_score=Subquery( Player.objects.filter( osu_id=OuterRef('osu_id') ).annotate( avg_score=Avg('scores__score') ).values('avg_score')[:1] ), average_acc=Subquery( Player.objects.filter( osu_id=OuterRef('osu_id') ).annotate( avg_acc=Avg('scores__accuracy') ).values('avg_acc')[:1] ), average_contrib=Subquery( Player.objects.filter( osu_id=OuterRef('osu_id') ).annotate( avg_contrib=Avg('scores__contrib') ).values('avg_contrib')[:1] ) ) #update ranks rank_score = Window(expression=Rank(), order_by=F('average_score').desc()) rank_acc = Window(expression=Rank(), order_by=F('average_acc').desc()) rank_contrib = Window(expression=Rank(), order_by=F('average_contrib').desc()) d = Player.objects.filter().annotate(rank_s=rank_score, rank_a=rank_acc, rank_c=rank_contrib) for p in d: Player.objects.filter(osu_id=p.osu_id).update(score_rank=p.rank_s, acc_rank=p.rank_a, contrib_rank=p.rank_c) #i don't know of a way to do the same as above in one db query without adding a "team" fk to Score Team.objects.update( average_score=Subquery( Team.objects.filter( id=OuterRef('id') ).annotate( avg_score=Avg('scores__score') ).values('avg_score')[:1] ), average_acc=Subquery( Team.objects.filter( id=OuterRef('id') ).annotate( avg_acc=Avg('scores__accuracy') ).values('avg_acc')[:1] ) ) trank_score = Window(expression=Rank(), order_by=F('average_score').desc()) trank_acc = Window(expression=Rank(), order_by=F('average_acc').desc()) f = Team.objects.filter().annotate(rank_s=trank_score, rank_a=trank_acc) for t in f: Team.objects.filter(id=t.id).update(score_rank=t.rank_s, acc_rank=t.rank_a) '''i suspect the reason as to why this doesn't work is because windows will be relative to the subqueries; it works above because the aggregation avg(whatever) can be calculated without knowledge of the other rows on the other hand, this completely falls apart because when the Subquery is executed, the scope of Window() will be for each player alone, and so each player (relative to themselvses) is rank #1, and that's what shows up i have no idea how to isolate the Window function or give it the information it needs''' ''' rank_w = Window(expression=Rank(), order_by=F('average_score').desc()) d = Player.objects.filter().annotate(rank=rank_w) print(d) print(d.values()) Player.objects.update(contrib_rank=Subquery( Player.objects.filter( osu_id=OuterRef('osu_id') ).annotate( rank=rank_w ).values('rank')[:1] ) ) ''' #supposedly it's possible that it's because RowNumber(), Rank() etc don't work #properly on SQLite, but do work properly on Postgres ''' for index, p in enumerate(Player.objects.order_by('-average_score')): p.score_rank = index+1 for index, p in enumerate(Player.objects.order_by('-average_acc')): p.acc_rank = index+1 for index, p in enumerate(Player.objects.order_by('-average_contrib')): p.contrib_rank = index+1 ''' '''
def get_context_data(self, **kwargs): context = super(EventAthletes, self).get_context_data() event = self.get_event() gender = gender_name_to_int(self.kwargs.get('gender')) result_filter = self.get_filter() if 'alltimes' in result_filter and result_filter['alltimes'] == 'on': results = IndividualResult.public_objects.only_valid_results( ).filter(event=event, athlete__gender=gender) results = results.annotate(rank=Window(expression=Rank(), order_by=F('time').asc()), ) if 'date_range_start' in result_filter: results = results.filter( competition__date__gte=result_filter['date_range_start']) if 'date_range_end' in result_filter: results = results.filter( competition__date__lte=result_filter['date_range_end']) if 'nationality' in result_filter and type( result_filter['nationality']) is Country: results = results.filter( athlete__nationalities__in=result_filter['nationality']. get_all_children(include_self=True)) if 'yob_start' in result_filter and result_filter['yob_start'] > 0: results = results.filter( athlete__year_of_birth__gte=result_filter['yob_start']) if 'yob_end' in result_filter and result_filter['yob_end'] > 0: results = results.filter( athlete__year_of_birth__lte=result_filter['yob_end']) results = results.select_related('athlete', 'competition') results = results.prefetch_related('athlete__nationalities') else: athletes = Athlete.objects.filter(gender=gender) athlete_results = IndividualResult.public_objects.only_valid_results( ).filter(athlete=OuterRef('pk'), event=event) if 'date_range_start' in result_filter: athlete_results = athlete_results.filter( competition__date__gte=result_filter['date_range_start']) if 'date_range_end' in result_filter: athlete_results = athlete_results.filter( competition__date__lte=result_filter['date_range_end']) athletes = athletes.filter(Exists(athlete_results)) if 'nationality' in result_filter and type( result_filter['nationality']) is Country: athletes = athletes.filter( nationalities__in=result_filter['nationality']. get_all_children(include_self=True)) if 'yob_start' in result_filter and result_filter['yob_start'] > 0: athletes = athletes.filter( year_of_birth__gte=result_filter['yob_start']) if 'yob_end' in result_filter and result_filter['yob_end'] > 0: athletes = athletes.filter( year_of_birth__lte=result_filter['yob_end']) personal_best_query = IndividualResult.public_objects.only_valid_results( ).filter(event=event, athlete=OuterRef('pk')).values('time') if 'date_range_start' in result_filter: personal_best_query = personal_best_query.filter( competition__date__gte=result_filter['date_range_start']) if 'date_range_end' in result_filter: personal_best_query = personal_best_query.filter( competition__date__lte=result_filter['date_range_end']) athletes = athletes.annotate(personal_best=personal_best_query[:1]) athletes = athletes.order_by('personal_best') athletes = athletes.annotate(rank=Window( expression=Rank(), order_by=F('personal_best').asc()), ) results_qs = IndividualResult.public_objects.only_valid_results( ).filter(event=event) if 'date_range_start' in result_filter: results_qs = results_qs.filter( competition__date__gte=result_filter['date_range_start']) if 'date_range_end' in result_filter: results_qs = results_qs.filter( competition__date__lte=result_filter['date_range_end']) athletes = athletes.prefetch_related( Prefetch('individualresult_set', queryset=results_qs)) athletes = athletes.prefetch_related( 'individualresult_set__competition', 'nationalities') results = athletes per_page = 25 paginator = Paginator(results, per_page) page_number = self.request.GET.get('page', 1) context['page_obj'] = paginator.get_page(page_number) context['paginator'] = paginator context['gender'] = self.kwargs.get('gender') context['event'] = event context['filter'] = result_filter return context