def test_forward(self):
books = Book.objects.annotate(has_authors=Exists('authors')).order_by('id')
for book in books:
self.assertTrue(book.has_authors)
# Only book 4 has editors
books = Book.objects.annotate(has_editors=Exists('editors')).order_by('id')
editors = {book.title: book.has_editors for book in books}
self.assertEqual(editors, {'Book 1': False,
'Book 2': False,
'Book 3': False,
'Book 4': True})
def neighbors(self):
viewed = set()
Contest.title_neighbors_(self.title, deep=1, viewed=viewed)
cond = Q()
for title in viewed:
cond |= Q(title=title)
resource_contests = Contest.objects.filter(resource=self.resource_id)
resource_contests = resource_contests.annotate(has_statistics=Exists('statistics')).filter(has_statistics=True)
for query, order in (
(Q(end_time__lt=self.start_time), '-end_time'),
(Q(start_time__gt=self.end_time), 'start_time'),
):
c = resource_contests.filter(query).order_by(order).first()
if c:
cond |= Q(pk=c.pk)
if self.title_path is not None:
qs = resource_contests.filter(query).exclude(title=self.title)
qs = qs.extra(select={'lcp': f'''nlevel(lca(title_path, '{self.title_path}'))'''})
qs = qs.order_by('-lcp', order)
c = qs.first()
if c and c.lcp:
cond |= Q(pk=c.pk)
qs = resource_contests.filter(cond).exclude(pk=self.pk).order_by('end_time')
return qs
def test_easy_negated_exists(self):
ps = Parent.objects.annotate(
has_children=~Exists('child')).order_by('pk')
ps = list(ps)
self.assertEqual(ps[0].has_children, False)
self.assertEqual(ps[1].has_children, True)
def test_negated_exists(self):
ps = Parent.objects.annotate(
has_children=~Exists(Child.objects.filter(
parent=OuterRef('pk')))).order_by('pk')
ps = list(ps)
self.assertEqual(ps[0].has_children, False)
self.assertEqual(ps[1].has_children, True)
def authors_with_no_books_django_exists(request, **context):
authors = Author.objects.annotate(no_books=~Exists(
Book.objects.filter(authors=OuterRef('pk'))
)).filter(no_books=True)
context.update({
'authors': authors[:50]
})
return render(request, 'author_list.html', context)
def get_view_contests(request, coder):
user_contest_filter = Q()
group_list = settings.GROUP_LIST_
if coder:
categories = request.GET.getlist('filter', ['list'])
user_contest_filter = coder.get_contest_filter(categories)
group_list = bool(coder.settings.get("group_in_list", group_list))
else:
categories = request.GET.getlist('filter')
if categories:
user_contest_filter = Coder.get_contest_filter(None, categories)
group = request.GET.get('group')
if group is not None:
group_list = bool(group)
base_contests = Contest.visible.filter(user_contest_filter)
if request.user.has_perm('reset_contest_statistic_timing'):
base_contests = base_contests.select_related('timing')
now = timezone.now()
result = []
for group, query, order, limit in (
("running", Q(start_time__lte=now,
end_time__gte=now), "end_time", None),
("coming", Q(start_time__gt=now), "start_time", None),
):
group_by_resource = {}
contests = base_contests.filter(query).order_by(order)
contests = contests.select_related('resource')
contests = contests.annotate(has_statistics=Exists('statistics'))
if limit:
contests = contests[:limit]
if order.startswith('-'):
contests = list(contests)
contests.reverse()
for contest in contests:
contest.state = group
if group_list:
group_by_resource.setdefault(contest.resource.id,
[]).append(contest)
if group_list:
for contest in contests:
rid = contest.resource.id
if rid in group_by_resource:
contest.group_size = len(group_by_resource[rid]) - 1
result.append(contest)
for c in group_by_resource[rid][1:]:
c.sub_contest = True
result.append(c)
del group_by_resource[rid]
else:
result.extend(contests)
return result
def test_exists(self):
annotation = {'has_an_owner': Exists('owner')}
cats = Cat.objects.annotate(**annotation)
cats = {cat.name: cat.has_an_owner for cat in cats}
self.assertEqual(cats, {'Muffin': False,
'Grumpy': False,
'Garfield': True})
def test_reverse(self):
authors = Author.objects.annotate(has_books=Exists('authored_books')).order_by('id')
books = {author.name: author.has_books for author in authors}
self.assertEqual(books, {'Author 1': True,
'Author 2': True,
'Author 3': True,
'Author 4': True,
'Author 5': True,
'Author 6': False})
def test_filter(self):
publisher_id = Publisher.objects.get(name='Publisher 1').id
authors = Author.objects.annotate(published_by_1=Exists('authored_books', filter=Q(book__publisher_id=publisher_id)))
authors = {author.name: author.published_by_1 for author in authors}
self.assertEqual(authors, {'Author 1': True,
'Author 2': True,
'Author 3': True,
'Author 4': False,
'Author 5': False,
'Author 6': False})
def test_two_joins(self):
authors = Author.objects.annotate(has_editors=Exists('authored_books__editors')).order_by('id')
# Only author 5 has written a book with editors
editors = {author.name: author.has_editors for author in authors}
self.assertEqual(editors, {'Author 1': False,
'Author 2': False,
'Author 3': False,
'Author 4': False,
'Author 5': True,
'Author 6': False})
def coders(request, template='coders.html'):
coders = Coder.objects.select_related('user')
params = {}
search = request.GET.get('search')
if search:
filt = get_iregex_filter(search, 'username', logger=request.logger)
coders = coders.filter(filt)
countries = request.GET.getlist('country')
countries = set([c for c in countries if c])
if countries:
coders = coders.annotate(filter_country=Exists('account', filter=Q(account__country__in=countries)))
coders = coders.filter(Q(country__in=countries) | Q(filter_country=True))
params['countries'] = countries
resources = request.GET.getlist('resource')
if resources:
resources = [r for r in resources if r]
resources = list(Resource.objects.filter(pk__in=resources))
for r in resources:
coders = coders.annotate(**{f'{r.pk}_rating': SubqueryMax('account__rating', filter=Q(resource=r))})
coders = coders.annotate(**{f'{r.pk}_n_contests': SubquerySum('account__n_contests', filter=Q(resource=r))})
params['resources'] = resources
# ordering
orderby = request.GET.get('sort_column')
if orderby in ['username', 'created', 'n_accounts']:
pass
elif orderby and orderby.startswith('resource_'):
_, pk = orderby.split('_')
orderby = [f'{pk}_rating', f'{pk}_n_contests']
elif orderby:
request.logger.error(f'Not found `{orderby}` column for sorting')
orderby = []
orderby = orderby if not orderby or isinstance(orderby, list) else [orderby]
order = request.GET.get('sort_order')
if order in ['asc', 'desc']:
orderby = [getattr(F(o), order)(nulls_last=True) for o in orderby]
elif order:
request.logger.error(f'Not found `{order}` order for sorting')
orderby = orderby or ['-created']
coders = coders.order_by(*orderby)
context = {
'coders': coders,
'params': params,
}
return template, context
def test_name_doesnt_match_m2m(self):
annotation = {
'has_bits': Exists('collection_key__bits')
}
items = Item.objects.annotate(**annotation)
items = {item.name: item.has_bits for item in items}
self.assertEqual(items, {'item one': True,
'item two': False,
'item three': False,
'item four': False,
'item five': False,
'item six': False,
})
def check_level_up(student: Student) -> bool:
if not student.semester.active:
return False
level_info = get_level_info(student)
level_number = level_info['level_number']
if level_number <= student.last_level_seen:
return False
bonuses = BonusLevel.objects.filter(active=True, level__lte=level_number)
bonuses = bonuses.annotate(
gotten=Exists('bonuslevelunlock', filter=Q(
student__user=student.user)))
bonuses = bonuses.exclude(gotten=True)
if bonuses.exists():
psets = PSet.objects.filter(student=student)
counts = psets.aggregate(
b=Count('pk', unique=True, filter=Q(unit__code__startswith='B')),
d=Count('pk', unique=True, filter=Q(unit__code__startswith='D')),
z=Count('pk', unique=True, filter=Q(unit__code__startswith='Z')),
)
r = compute_insanity_rating(b=counts['b'],
d=counts['d'],
z=counts['z'])
for bonus in bonuses:
units = bonus.group.unit_set
if r >= 0.5:
unit = units.filter(code__startswith='Z').first()
elif r <= -0.5:
unit = units.filter(code__startswith='B').first()
else:
unit = units.filter(code__startswith='D').first()
if unit is not None:
student.curriculum.add(unit)
BonusLevelUnlock.objects.create(bonus=bonus, student=student)
logger.log(SUCCESS_LOG_LEVEL,
f"{student} obtained special unit {unit}")
student.last_level_seen = level_number
student.save()
return True
def resource(request, host, template='resource.html', extra_context=None):
now = timezone.now()
resource = get_object_or_404(Resource, host=host)
params = {}
contests = resource.contest_set.filter(invisible=False).annotate(has_statistics=Exists('statistics'))
accounts = Account.objects.filter(resource=resource)
has_country = accounts.filter(country__isnull=False).exists()
countries = request.GET.getlist('country')
countries = set([c for c in countries if c])
if countries:
params['countries'] = countries
accounts = accounts.filter(country__in=countries)
period = request.GET.get('period', 'half')
periods = ['month', 'quarter', 'half', 'year', 'all']
params['period'] = period
delta_period = {
'month': timedelta(days=30 * 1),
'quarter': timedelta(days=30 * 3),
'half': timedelta(days=30 * 6),
'year': timedelta(days=30 * 12),
'all': None,
}[period]
if delta_period:
accounts = accounts.annotate(in_period=Exists('statistics', filter=Q(contest__end_time__gt=now - delta_period)))
accounts = accounts.filter(in_period=True)
countries = accounts \
.filter(country__isnull=False) \
.values('country') \
.annotate(count=Count('country'))
# .order_by('-count')
width = 50
ratings = defaultdict(int)
qs = accounts.filter(info__rating__isnull=False).values('info__rating')
for a in qs:
rating = a['info__rating']
rating = math.floor(rating / width) * width
ratings[rating] += 1
idx = 0
data = []
labels = []
for k, v in sorted(ratings.items()):
if k > resource.ratings[idx]['high']:
idx += 1
labels.append(k)
data.append({
'title': f'{k}..{k + width - 1}',
'rating': k,
'count': v,
'info': resource.ratings[idx],
})
context = {
'resource': resource,
'accounts': resource.account_set.filter(coders__isnull=False).prefetch_related('coders').order_by('-modified'),
'countries': countries,
'rating': {
'labels': labels,
'data': data,
},
'contests': [
('running', contests.filter(start_time__lt=now, end_time__gt=now).order_by('start_time')),
('coming', contests.filter(start_time__gt=now).order_by('start_time')),
('past', contests.filter(end_time__lt=now).order_by('-end_time')),
],
'has_country': has_country,
'periods': periods,
'params': params,
}
if extra_context is not None:
context.update(extra_context)
return render(request, template, context)
def get_queryset(self, request):
return super().get_queryset(request).annotate(
has_coder=Exists('coders'))
def resource(request, host, template='resource.html', extra_context=None):
now = timezone.now()
resource = get_object_or_404(Resource, host=host)
if request.user.is_authenticated:
coder = request.user.coder
coder_account = coder.account_set.filter(resource=resource,
rating__isnull=False).first()
coder_account_ids = set(
coder.account_set.filter(resource=resource).values_list('id',
flat=True))
show_coder_account_rating = True
else:
coder = None
coder_account = None
coder_account_ids = set()
show_coder_account_rating = False
params = {}
contests = resource.contest_set.annotate(
has_statistics=Exists('statistics'))
accounts = Account.objects.filter(resource=resource)
has_country = accounts.filter(country__isnull=False).exists()
countries = request.GET.getlist('country')
countries = set([c for c in countries if c])
if countries:
params['countries'] = countries
accounts = accounts.filter(country__in=countries)
period = request.GET.get('period', 'all')
params['period'] = period
deltas_period = {
'month': timedelta(days=30 * 1),
'quarter': timedelta(days=30 * 3),
'half': timedelta(days=30 * 6),
'year': timedelta(days=30 * 12),
'all': None,
}
periods = list(deltas_period.keys())
delta_period = deltas_period.get(period, None)
if delta_period:
accounts = accounts.filter(last_activity__gte=now - delta_period)
default_variables = resource.info.get('default_variables', {})
for field, operator in (
('min_rating', 'rating__gte'),
('max_rating', 'rating__lte'),
('min_n_participations', 'n_contests__gte'),
('max_n_participations', 'n_contests__lte'),
):
value = request.GET.get(field, default_variables.get(field))
if value:
params[field] = value
if field in params:
accounts = accounts.filter(**{operator: params[field]})
countries = accounts \
.filter(country__isnull=False) \
.values('country') \
.annotate(count=Count('country')) \
.order_by('-count', 'country')
n_x_axis = resource.info.get('ratings', {}).get('chartjs',
{}).get('n_x_axis')
coloring_field = resource.info.get('ratings',
{}).get('chartjs',
{}).get('coloring_field')
width = 50
min_rating = params.get('min_rating')
max_rating = params.get('max_rating')
if n_x_axis or min_rating and max_rating and int(max_rating) - int(
min_rating) <= 100:
width = 1
rating_field = 'rating50' if width == 50 else 'rating'
ratings = accounts.filter(**{f'{rating_field}__isnull': False})
if n_x_axis:
rs = ratings.aggregate(max_rating=Max(rating_field),
min_rating=Min(rating_field))
if rs['max_rating'] is not None:
width = max((rs['max_rating'] - rs['min_rating']) // n_x_axis, 1)
else:
width = 1
ratings = ratings.annotate(ratingw=F(rating_field) / width)
rating_field = 'ratingw'
annotations = {'count': Count(rating_field)}
if coloring_field:
ratings = ratings.annotate(rank=Cast(
KeyTextTransform(coloring_field, 'info'), IntegerField()))
annotations['coloring_field'] = Avg('rank')
ratings = ratings \
.values(rating_field) \
.annotate(**annotations) \
.order_by(rating_field)
ratings = list(ratings)
labels = []
data = []
if ratings and resource.ratings:
idx = 0
for rating in ratings:
low = rating[rating_field] * width
high = low + width - 1
val = rating.get('coloring_field', low)
while val > resource.ratings[idx]['high']:
idx += 1
while idx and val <= resource.ratings[idx - 1]['high']:
idx -= 1
data.append({
'title': f'{low}..{high}',
'rating': low,
'count': rating['count'],
'info': resource.ratings[idx],
})
min_rating = ratings[0][rating_field]
max_rating = ratings[-1][rating_field]
labels = list(range(min_rating * width, max_rating * width + 1, width))
context = {
'resource':
resource,
'coder':
coder,
'coder_accounts_ids':
coder_account_ids,
'accounts':
resource.account_set.filter(coders__isnull=False).prefetch_related(
'coders').order_by('-modified'),
'countries':
countries,
'rating': {
'labels': labels,
'data': data,
'account': coder_account if show_coder_account_rating else None,
'width': width,
},
'contests': {
'past': {
'contests':
contests.filter(end_time__lt=now).order_by('-end_time'),
'field': 'end_time',
},
'running': {
'contests':
contests.filter(start_time__lt=now,
end_time__gt=now).order_by('end_time'),
'field':
'time_left',
},
'coming': {
'contests':
contests.filter(start_time__gt=now).order_by('start_time'),
'field':
'start_time',
},
},
'contest_key':
None,
'has_country':
has_country,
'periods':
periods,
'params':
params,
'first_per_page':
10,
'per_page':
50,
'last_activities':
accounts.filter(last_activity__isnull=False).order_by(
'-last_activity', 'id'),
'top':
accounts.filter(rating__isnull=False).order_by('-rating', 'id'),
'most_participated':
accounts.order_by('-n_contests', 'id'),
'most_writer':
accounts.filter(n_writers__gt=0).order_by('-n_writers', 'id'),
'problems':
resource.problem_set.filter(url__isnull=False).order_by(
'-time', 'contest_id', 'index'),
}
if extra_context is not None:
context.update(extra_context)
return render(request, template, context)
def resource(request, host, template='resource.html', extra_context=None):
now = timezone.now()
resource = get_object_or_404(Resource, host=host)
min_rating = request.GET.get('min_rating')
max_rating = request.GET.get('max_rating')
if request.user.is_authenticated:
coder = request.user.coder
coder_account = coder.account_set.filter(resource=resource,
rating__isnull=False).first()
coder_account_ids = set(
coder.account_set.filter(resource=resource).values_list('id',
flat=True))
show_coder_account_rating = not min_rating and not max_rating
else:
coder = None
coder_account = None
coder_account_ids = set()
show_coder_account_rating = False
params = {}
contests = resource.contest_set.annotate(
has_statistics=Exists('statistics'))
accounts = Account.objects.filter(resource=resource)
has_country = accounts.filter(country__isnull=False).exists()
countries = request.GET.getlist('country')
countries = set([c for c in countries if c])
if countries:
params['countries'] = countries
accounts = accounts.filter(country__in=countries)
period = request.GET.get('period', 'all')
params['period'] = period
deltas_period = {
'month': timedelta(days=30 * 1),
'quarter': timedelta(days=30 * 3),
'half': timedelta(days=30 * 6),
'year': timedelta(days=30 * 12),
'all': None,
}
periods = list(deltas_period.keys())
delta_period = deltas_period.get(period, None)
if delta_period:
accounts = accounts.filter(last_activity__gte=now - delta_period)
if min_rating:
accounts = accounts.filter(rating__gte=int(min_rating))
if max_rating:
accounts = accounts.filter(rating__lte=int(max_rating))
countries = accounts \
.filter(country__isnull=False) \
.values('country') \
.annotate(count=Count('country')) \
.order_by('-count', 'country')
width = 50
if min_rating and max_rating and int(max_rating) - int(min_rating) <= 100:
width = 1
rating_field = 'rating50' if width == 50 else 'rating'
ratings = accounts \
.filter(**{f'{rating_field}__isnull': False}) \
.values(rating_field) \
.annotate(count=Count(rating_field)) \
.order_by(rating_field)
ratings = list(ratings)
labels = []
data = []
if ratings and resource.ratings:
idx = 0
for rating in ratings:
low = rating[rating_field] * width
high = low + width - 1
while low > resource.ratings[idx]['high']:
idx += 1
data.append({
'title': f'{low}..{high}',
'rating': low,
'count': rating['count'],
'info': resource.ratings[idx],
})
min_rating = ratings[0][rating_field]
max_rating = ratings[-1][rating_field]
if coder_account and show_coder_account_rating:
min_rating = min(min_rating, coder_account.rating // width)
max_rating = max(max_rating, coder_account.rating // width)
labels = list(range(min_rating * width, max_rating * width + 1, width))
context = {
'resource':
resource,
'coder':
coder,
'coder_accounts_ids':
coder_account_ids,
'accounts':
resource.account_set.filter(coders__isnull=False).prefetch_related(
'coders').order_by('-modified'),
'countries':
countries,
'rating': {
'labels': labels,
'data': data,
'account': coder_account if show_coder_account_rating else None,
'width': width,
},
'contests': {
'past': {
'contests':
contests.filter(end_time__lt=now).order_by('-end_time'),
'field': 'end_time',
},
'running': {
'contests':
contests.filter(start_time__lt=now,
end_time__gt=now).order_by('end_time'),
'field':
'time_left',
},
'coming': {
'contests':
contests.filter(start_time__gt=now).order_by('start_time'),
'field':
'start_time',
},
},
'contest_key':
None,
'has_country':
has_country,
'periods':
periods,
'params':
params,
'first_per_page':
10,
'per_page':
50,
'last_activities':
accounts.filter(last_activity__isnull=False).order_by(
'-last_activity', 'id'),
'top':
accounts.filter(rating__isnull=False).order_by('-rating', 'id'),
'most_participated':
accounts.order_by('-n_contests', 'id'),
'most_writer':
accounts.filter(n_writers__gt=0).order_by('-n_writers', 'id'),
}
if extra_context is not None:
context.update(extra_context)
return render(request, template, context)
def get_events(request):
if request.user.is_authenticated:
coder = request.user.coder
else:
coder = None
categories = request.POST.getlist('categories')
ignore_filters = request.POST.getlist('ignore_filters')
has_filter = False
referer = request.META.get('HTTP_REFERER')
if referer:
parsed = urlparse(referer)
query_dict = parse_qs(parsed.query)
as_coder = query_dict.get('as_coder')
if as_coder and request.user.has_perm('as_coder'):
coder = Coder.objects.get(user__username=as_coder[0])
has_filter = 'filter' in query_dict
categories = query_dict.get('filter', categories)
tzname = get_timezone(request)
offset = get_timezone_offset(tzname)
query = Q()
if coder:
query = coder.get_contest_filter(categories, ignore_filters)
elif has_filter:
query = Coder.get_contest_filter(None, categories, ignore_filters)
if not coder or coder.settings.get('calendar_filter_long', True):
if categories == ['calendar'] and '0' not in ignore_filters:
query &= Q(duration_in_secs__lt=timedelta(days=1).total_seconds())
start_time = arrow.get(request.POST.get('start', timezone.now())).datetime
end_time = arrow.get(
request.POST.get('end',
timezone.now() + timedelta(days=31))).datetime
query = query & Q(end_time__gte=start_time) & Q(start_time__lte=end_time)
search_query = request.POST.get('search_query', None)
if search_query:
search_query_re = verify_regex(search_query)
query &= Q(host__iregex=search_query_re) | Q(
title__iregex=search_query_re)
party_slug = request.POST.get('party')
if party_slug:
party = get_object_or_404(Party.objects.for_user(request.user),
slug=party_slug)
query = Q(rating__party=party) & query
contests = Contest.objects if party_slug else Contest.visible
contests = contests.select_related('resource')
contests = contests.annotate(has_statistics=Exists('statistics'))
try:
result = []
for contest in contests.filter(query):
c = {
'id':
contest.pk,
'title':
contest.title,
'host':
contest.host,
'url': (reverse('ranking:standings',
args=(slug(contest.title),
contest.pk)) if contest.has_statistics
else contest.standings_url or contest.url),
'start':
(contest.start_time +
timedelta(minutes=offset)).strftime("%Y-%m-%dT%H:%M:%S"),
'end':
(contest.end_time +
timedelta(minutes=offset)).strftime("%Y-%m-%dT%H:%M:%S"),
'countdown':
contest.next_time,
'hr_duration':
contest.hr_duration,
'color':
contest.resource.color,
'icon':
contest.resource.icon,
}
result.append(c)
except Exception as e:
return JsonResponse(
{'message': f'query = `{search_query}`, error = {e}'},
safe=False,
status=400)
return JsonResponse(result, safe=False)
def resource(request, host, template='resource.html', extra_context=None):
now = timezone.now()
resource = get_object_or_404(Resource, host=host)
params = {}
contests = resource.contest_set.filter(invisible=False).annotate(
has_statistics=Exists('statistics'))
accounts = Account.objects.filter(resource=resource)
has_country = accounts.filter(country__isnull=False).exists()
countries = request.GET.getlist('country')
countries = set([c for c in countries if c])
if countries:
params['countries'] = countries
accounts = accounts.filter(country__in=countries)
period = request.GET.get('period', 'all')
periods = ['month', 'quarter', 'half', 'year', 'all']
params['period'] = period
delta_period = {
'month': timedelta(days=30 * 1),
'quarter': timedelta(days=30 * 3),
'half': timedelta(days=30 * 6),
'year': timedelta(days=30 * 12),
'all': None,
}[period]
if delta_period:
accounts = accounts.filter(last_activity__gte=now - delta_period)
countries = accounts \
.filter(country__isnull=False) \
.values('country') \
.annotate(count=Count('country')) \
.order_by('-count')
# width = 50
# ratings = defaultdict(int)
# qs = accounts.filter(info__rating__isnull=False).values('info__rating')
# for a in qs.iterator():
# rating = a['info__rating']
# rating = math.floor(rating / width) * width
# ratings[rating] += 1
# idx = 0
# data = []
# labels = []
# for k, v in sorted(ratings.items()):
# if k > resource.ratings[idx]['high']:
# idx += 1
# labels.append(k)
# data.append({
# 'title': f'{k}..{k + width - 1}',
# 'rating': k,
# 'count': v,
# 'info': resource.ratings[idx],
# })
context = {
'resource':
resource,
'accounts':
resource.account_set.filter(coders__isnull=False).prefetch_related(
'coders').order_by('-modified'),
'countries':
countries,
# 'rating': {
# 'labels': labels,
# 'data': data,
# },
'contests': [
('running',
contests.filter(start_time__lt=now,
end_time__gt=now).order_by('start_time')),
('coming',
contests.filter(start_time__gt=now).order_by('start_time')),
('past', contests.filter(end_time__lt=now).order_by('-end_time')),
],
'has_country':
has_country,
'periods':
periods,
'params':
params,
'first_per_page':
10,
'per_page':
50,
'last_activities':
accounts.filter(
last_activity__isnull=False).order_by('-last_activity'),
'top':
accounts.filter(rating__isnull=False).order_by('-rating'),
'most_participated':
accounts.filter(n_contests__gt=0).order_by('-n_contests'),
}
if extra_context is not None:
context.update(extra_context)
return render(request, template, context)
def problems(request, template='problems.html'):
problems = Problem.objects.all()
problems = problems.select_related('contest', 'resource')
problems = problems.prefetch_related('tags')
problems = problems.order_by('-time', 'contest_id', 'index')
problems = problems.filter(contest__end_time__lt=timezone.now(),
visible=True)
search = request.GET.get('search')
if search:
cond, problems = get_iregex_filter(
search,
'name',
'contest__title',
'contest__host',
'contest__resource__host',
logger=request.logger,
mapping={
'name': {
'fields': ['name__iregex']
},
'contest': {
'fields': ['contest__title__iregex']
},
'resource': {
'fields': ['resource__host__iregex']
},
'tag': {
'fields': ['problemtag__name__iregex'],
'exists': 'tags'
},
'cid': {
'fields': ['contest_id'],
'func': lambda v: int(v)
},
'rid': {
'fields': ['resource_id'],
'func': lambda v: int(v)
},
'pid': {
'fields': ['id'],
'func': lambda v: int(v)
},
},
queryset=problems,
)
problems = problems.filter(cond)
resources = [r for r in request.GET.getlist('resource') if r]
if resources:
problems = problems.filter(contest__resource_id__in=resources)
resources = list(Resource.objects.filter(pk__in=resources))
tags = [r for r in request.GET.getlist('tag') if r]
if tags:
problems = problems.annotate(
has_tag=Exists('tags', filter=Q(problemtag__pk__in=tags)))
problems = problems.filter(has_tag=True)
tags = list(ProblemTag.objects.filter(pk__in=tags))
context = {
'problems': problems,
'params': {
'resources': resources,
'tags': tags,
},
}
return template, context
def authors_with_no_books(request, **context):
authors = Author.objects.annotate(no_books=~Exists('book')).filter(no_books=True)
context.update({
'authors': authors[:50]
})
return render(request, 'author_list.html', context)
def get_profile_context(request, statistics, writers):
history_resources = statistics \
.filter(contest__resource__has_rating_history=True) \
.filter(contest__stage__isnull=True) \
.annotate(new_rating=Cast(KeyTextTransform('new_rating', 'addition'), IntegerField())) \
.filter(new_rating__isnull=False) \
.annotate(host=F('contest__resource__host')) \
.values('host') \
.annotate(num_contests=Count('contest')) \
.order_by('-num_contests')
stats = statistics \
.select_related('contest', 'account') \
.filter(addition__medal__isnull=False) \
.order_by('-contest__end_time')
resource_medals = {}
account_medals = {}
for stat in stats:
resource_medals.setdefault(stat.contest.resource_id, []).append(stat)
account_medals.setdefault(stat.account.id, []).append(stat)
statistics = statistics \
.select_related('contest', 'contest__resource', 'account') \
.order_by('-contest__end_time')
search = request.GET.get('search')
filters = {}
if search:
filt = get_iregex_filter(
search,
'contest__resource__host',
'contest__title',
mapping={
'writer': {
'fields': ['contest__info__writers__contains']
},
'contest': {
'fields': ['contest__title__iregex']
},
'resource': {
'fields': ['contest__resource__host']
},
'account': {
'fields': ['account__key']
},
'medal': {
'fields': ['addition__medal'],
'func': lambda v: False if not v or v == 'any' else v,
'suff': lambda v: '__isnull' if v is False else '',
},
'cid': {
'fields': ['contest_id'],
'func': lambda v: int(v)
},
'rid': {
'fields': ['contest__resource_id'],
'func': lambda v: int(v)
},
},
values=filters,
logger=request.logger,
)
statistics = statistics.filter(filt)
filter_resources = filters.pop('resource', [])
for val in filter_resources:
history_resources = history_resources.filter(
contest__resource__host=val)
search_resource = filter_resources[0] if len(
filter_resources) == 1 else None
if search_resource:
writers = writers.filter(resource__host=search_resource)
writers = writers.order_by('-end_time')
writers = writers.annotate(has_statistics=Exists('statistics'))
context = {
'statistics': statistics,
'writers': writers,
'history_resources': history_resources,
'show_history_ratings': not filters,
'resource_medals': resource_medals,
'account_medals': account_medals,
'search_resource': search_resource,
'timezone': get_timezone(request),
'timeformat': get_timeformat(request),
}
return context
def search(request, **kwargs):
query = request.GET.get('query', None)
if not query or not isinstance(query, str):
return HttpResponseBadRequest('invalid query')
count = int(request.GET.get('count', django_settings.DEFAULT_COUNT_QUERY_))
count = min(count, django_settings.DEFAULT_COUNT_LIMIT_)
page = int(request.GET.get('page', 1))
if query == 'themes':
ret = {}
for t in django_settings.THEMES_:
ret[t] = t.title()
return JsonResponse(ret)
elif query == 'timezones':
ret = {}
for tz in get_timezones():
ret[tz["name"]] = f'{tz["name"]} {tz["repr"]}'
return JsonResponse(ret)
elif query == 'resources':
qs = Resource.objects.all()
if 'regex' in request.GET:
qs = qs.filter(get_iregex_filter(request.GET['regex'], 'host'))
qs = qs.order_by('-n_accounts', 'pk')
qs = qs[(page - 1) * count:page * count]
ret = [{'id': r.id, 'text': r.host, 'icon': r.icon} for r in qs]
elif query == 'resources-for-add-account' and request.user.is_authenticated:
coder = request.user.coder
coder_accounts = coder.account_set.filter(resource=OuterRef('pk'))
qs = Resource.objects \
.annotate(has_coder_account=Exists(coder_accounts)) \
.annotate(has_multi=F('module__multi_account_allowed')) \
.annotate(disabled=Case(
When(module__isnull=True, then=Value(True)),
When(has_coder_account=True, has_multi=False, then=Value(True)),
default=Value(False),
output_field=BooleanField(),
))
if 'regex' in request.GET:
qs = qs.filter(get_iregex_filter(request.GET['regex'], 'host'))
qs = qs.order_by('disabled', 'pk')
qs = qs[(page - 1) * count:page * count]
ret = [{
'id': r.id,
'text': r.host,
'disabled': r.disabled,
} for r in qs]
elif query == 'accounts-for-add-account' and request.user.is_authenticated:
coder = request.user.coder
qs = Account.objects.all()
resource = request.GET.get('resource')
if resource:
qs = qs.filter(resource__id=int(resource))
else:
qs = qs.select_related('resource')
order = ['disabled']
if 'user' in request.GET:
re_search = request.GET.get('user')
exact_qs = qs.filter(key__iexact=re_search)
if exact_qs.exists():
qs = exact_qs
else:
qs = qs.filter(get_iregex_filter(re_search, 'key', 'name'))
search_striped = re_search.rstrip('$').lstrip('^')
qs = qs.annotate(match=Case(
When(Q(key__iexact=search_striped)
| Q(name__iexact=search_striped),
then=Value(True)),
default=Value(False),
output_field=BooleanField(),
))
order.append('-match')
qs = qs.annotate(
has_multi=F('resource__module__multi_account_allowed'))
qs = qs.annotate(disabled=Case(
When(coders=coder, then=Value(True)),
When(coders__isnull=False, has_multi=False, then=Value(True)),
default=Value(False),
output_field=BooleanField(),
))
qs = qs.order_by(*order, 'pk')
qs = qs[(page - 1) * count:page * count]
ret = []
for r in qs:
fields = {
'id':
r.key,
'text':
f'{r.key} - {r.name}'
if r.name and r.key.find(r.name) == -1 else r.key,
'disabled':
r.disabled,
}
if not resource:
fields['text'] += f', {r.resource.host}'
fields['resource'] = {
'id': r.resource.pk,
'text': r.resource.host
}
ret.append(fields)
elif query == 'organization':
qs = Organization.objects.all()
name = request.GET.get('name')
if name:
qs = qs.filter(
Q(name__icontains=name) | Q(name_ru__icontains=name)
| Q(abbreviation__icontains=name))
qs = qs[(page - 1) * count:page * count]
ret = [{'id': o.name, 'text': o.name} for o in qs]
elif query == 'team':
qs = Team.objects.all()
name = request.GET.get('name')
if name:
qs = qs.filter(name__icontains=name)
event = kwargs.get('event')
if event:
qs = qs.filter(event=event)
qs = qs.annotate(
disabled=Case(When(status=TeamStatus.NEW, then=Value(False)),
default=Value(True),
output_field=BooleanField())).order_by(
'disabled', '-modified', 'pk')
qs = qs[(page - 1) * count:page * count]
ret = [{
'id': r.id,
'text': r.name,
'disabled': r.disabled
} for r in qs]
elif query == 'country':
qs = list(countries)
name = request.GET.get('name')
if name:
name = name.lower()
qs = [(c, n) for c, n in countries if name in n.lower()]
qs = qs[(page - 1) * count:page * count]
ret = [{'id': c, 'text': n} for c, n in qs]
elif query == 'notpast':
title = request.GET.get('title')
qs = Contest.objects.filter(title__iregex=verify_regex(title),
end_time__gte=timezone.now())
qs = qs[(page - 1) * count:page * count]
ret = [{'id': c.id, 'text': c.title} for c in qs]
elif query == 'field-to-select':
contest = get_object_or_404(Contest, pk=request.GET.get('cid'))
text = request.GET.get('text')
field = request.GET.get('field')
assert '__' not in field
if field == 'languages':
qs = contest.info.get('languages', [])
qs = ['any'
] + [q for q in qs if not text or text.lower() in q.lower()]
elif field == 'rating':
qs = ['rated', 'unrated']
else:
field = f'addition__{field}'
qs = contest.statistics_set
if text:
qs = qs.filter(**{f'{field}__icontains': text})
qs = qs.distinct(field).values_list(field, flat=True)
qs = qs[(page - 1) * count:page * count]
ret = [{'id': f, 'text': f} for f in qs]
elif query == 'coders':
qs = Coder.objects.all()
if 'regex' in request.GET:
qs = qs.filter(get_iregex_filter(request.GET['regex'], 'username'))
order = ['-n_accounts', 'pk']
if request.user.is_authenticated:
qs = qs.annotate(
iam=Case(When(pk=request.user.coder.pk, then=Value(0)),
default=Value(1),
output_field=IntegerField()))
order.insert(0, 'iam')
qs = qs.order_by(*order, 'pk')
qs = qs[(page - 1) * count:page * count]
ret = [{'id': r.id, 'text': r.username} for r in qs]
elif query == 'accounts':
qs = Account.objects.all()
if request.GET.get('resource'):
qs = qs.filter(resource_id=int(request.GET.get('resource')))
order = ['-n_contests', 'pk']
if 'regex' in request.GET:
re_search = request.GET['regex']
exact_qs = qs.filter(key__iexact=re_search)
if exact_qs.exists():
qs = exact_qs
else:
qs = qs.filter(get_iregex_filter(re_search, 'key', 'name'))
search_striped = re_search.rstrip('$').lstrip('^')
qs = qs.annotate(match=Case(
When(Q(key__iexact=search_striped)
| Q(name__iexact=search_striped),
then=Value(True)),
default=Value(False),
output_field=BooleanField(),
))
order.insert(0, '-match')
qs = qs.select_related('resource')
qs = qs.order_by(*order, 'pk')
qs = qs[(page - 1) * count:page * count]
ret = [{
'id':
r.id,
'text':
f'{r.key}, {r.name}, {r.resource.host}'
if r.name else f'{r.key}, {r.resource.host}'
} for r in qs]
else:
return HttpResponseBadRequest('invalid query')
result = {
'items': ret,
'more': len(ret) and len(ret) == count,
}
return HttpResponse(json.dumps(result, ensure_ascii=False),
content_type="application/json")
def party(request, slug, tab='ranking'):
party = get_object_or_404(Party.objects.for_user(request.user), slug=slug)
party_contests = Contest.objects \
.filter(rating__party=party) \
.annotate(has_statistics=Exists('statistics')) \
.order_by('-end_time')
filt = Q(rating__party=party, statistics__account__coders=OuterRef('pk'))
coders = party.coders \
.annotate(n_participations=SubqueryCount('account__resource__contest', filter=filt)) \
.order_by('-n_participations') \
.select_related('user')
set_coders = set(coders)
if request.user.is_authenticated:
ignore_filters = request.user.coder.filter_set.filter(
categories__contains=['calendar']).order_by('created')
ignore_filters = list(ignore_filters.values('id', 'name'))
else:
ignore_filters = []
ignore_filters.append({'id': 0, 'name': 'disable long'})
results = []
total = {}
contests = Contest.objects.filter(rating__party=party)
future = contests.filter(
end_time__gt=timezone.now()).order_by('start_time')
statistics = Statistics.objects.filter(
account__coders__in=party.coders.all(),
contest__in=party_contests.filter(start_time__lt=timezone.now()),
contest__end_time__lt=timezone.now(),
) \
.order_by('-contest__end_time') \
.select_related('contest', 'account') \
.prefetch_related('account__coders', 'account__coders__user')
contests_standings = collections.OrderedDict(
(c, {}) for c in contests.filter(
end_time__lt=timezone.now()).order_by('-end_time'))
for statistic in statistics:
contest = statistic.contest
for coder in statistic.account.coders.all():
if coder in set_coders:
standings = contests_standings[contest].setdefault(
statistic.addition.get('division', '__none__'), [])
standings.append({
'solving': statistic.solving,
'upsolving': statistic.upsolving,
'stat': statistic,
'coder': coder,
})
for contest, divisions in contests_standings.items():
standings = []
fields = collections.OrderedDict()
if len(divisions) > 1 or '__none__' not in divisions:
fields['division'] = ('Div', 'division', 'Division')
for division, statistics in divisions.items():
if statistics:
max_solving = max([s['solving'] for s in statistics]) or 1
max_total = max(
[s['solving'] + s['upsolving'] for s in statistics]) or 1
for s in statistics:
solving = s['solving']
upsolving = s['upsolving']
s['score'] = 4. * (
solving +
upsolving) / max_total + 1. * solving / max_solving
s['interpretation'] = f'4 * ({solving} + {upsolving}) / {max_total} + {solving} / {max_solving}'
s['division'] = s['stat'].addition.get('division',
'').replace(
'_', ' ')
max_score = max([s['score'] for s in statistics]) or 1
for s in statistics:
s['score'] = 100. * s['score'] / max_score
s['interpretation'] = [
f'100 * ({s["interpretation"]}) / {max_score}'
]
for s in statistics:
coder = s['coder']
d = total.setdefault(coder.id, {})
d['score'] = s['score'] + d.get('score', 0)
d['coder'] = coder
d['num'] = d.setdefault('num', 0) + 1
d['avg'] = f"{(d['score'] / d['num']):.2f}"
d, s = d.setdefault('stat', {}), s['stat']
solved = s.addition.get('solved', {})
d['solving'] = solved.get('solving', s.solving) + d.get(
'solving', 0)
d['upsolving'] = solved.get(
'upsolving', s.upsolving) + d.get('upsolving', 0)
standings.extend(statistics)
standings.sort(key=lambda s: s['score'], reverse=True)
results.append({
'contest': contest,
'standings': standings,
'fields': list(fields.values()),
})
total = sorted(list(total.values()),
key=lambda d: d['score'],
reverse=True)
results.insert(
0, {
'standings':
total,
'fields': [('Num', 'num', 'Number contests'),
('Avg', 'avg', 'Average score')],
})
for result in results:
place = 0
prev = None
for i, s in enumerate(result['standings']):
if prev != s['score']:
prev = s['score']
place = i + 1
s['place'] = place
return render(
request,
'party.html',
{
'ignore_filters': [],
'fixed_ignore_filters': ignore_filters,
'timezone': get_timezone(request),
'future': future,
'party': party,
'party_contests': party_contests,
'results': results,
'coders': coders,
'tab': 'ranking' if tab is None else tab,
},
)
def test_filter_on_exists(self):
exists = Exists('authored_books')
authors = Author.objects.filter(exists)
self.assertEqual([author.id for author in authors],
[1, 2, 3])
def get_iregex_filter(
expression,
*fields,
mapping=None,
logger=None,
values=None,
queryset=None,
suffix='__iregex',
):
ret = Q()
n_exists = 0
for dis in expression.split('||'):
cond = Q()
for con in dis.split('&&'):
r = con.strip()
fs = fields
suff = suffix
neg = False
if ':' in r and mapping:
k, v = r.split(':', 1)
if k.startswith('!'):
k = k[1:].strip()
neg = not neg
if k in mapping:
mapped = mapping[k]
try:
fs = mapped['fields']
r = mapped['func'](v) if 'func' in mapped else v
suff = mapped.get('suff', '')
if callable(suff):
suff = suff(r)
exists = mapped.get('exists')
if exists:
if isinstance(r, str) and 'regex' in fs[0]:
r = verify_regex(r, logger=logger)
n_exists += 1
field = f'exists{n_exists}'
queryset = queryset.annotate(**{
field:
Exists(exists, filter=Q(**{fs[0]: r}))
})
fs = [field]
r = True
except Exception as e:
if logger:
logger.error(f'Field "{k}" has error: {e}')
continue
if values is not None:
values.setdefault(k, []).append(r)
if isinstance(r, str) and 'regex' in suff:
if r.startswith('!'):
neg = not neg
r = r[1:].strip()
r = verify_regex(r, logger=logger)
cs = [Q(**{f'{field}{suff}': r}) for field in fs]
if neg:
cond &= functools.reduce(operator.iand, (~c for c in cs))
else:
cond &= functools.reduce(operator.ior, cs)
ret |= cond
if queryset is not None:
return ret, queryset
return ret
def test_filter_on_negated_exists(self):
exists = ~Exists('authored_books')
authors = Author.objects.filter(exists)
self.assertEqual([author.id for author in authors],
[4, 5])
def test_filter_exists_with_or(self):
exists = Exists('authored_books') | Exists('edited_books')
authors = Author.objects.filter(exists)
self.assertEqual([author.id for author in authors],
[1, 2, 3, 5])