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])