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
