def get_unfiltered_queryset(self): queryset = Submission.objects.all() use_straight_join(queryset) join_sql_subquery( queryset, subquery=Problem.get_visible_problems(self.request.user).distinct().only('id').query, params=[], join_fields=[('problem_id', 'id')], alias='visible_problems', ) return ( queryset .select_related('problem', 'user__user', 'language') .order_by('id') .only( 'id', 'problem__code', 'user__user__username', 'date', 'language__key', 'time', 'memory', 'points', 'result', ) )
def filter_submissions_by_visible_problems(queryset, user): join_sql_subquery( queryset, subquery=str(Problem.get_visible_problems(user).distinct().only('id').query), params=[], join_fields=[('problem_id', 'id')], alias='visible_problems', )
def get_queryset(self): params = [self.problem.id] if self.in_contest: contest_join = """INNER JOIN judge_contestsubmission AS cs ON (sub.id = cs.submission_id) INNER JOIN judge_contestparticipation AS cp ON (cs.participation_id = cp.id)""" points = 'cs.points' constraint = ' AND cp.contest_id = %s' params.append(self.contest.id) else: contest_join = '' points = 'sub.points' constraint = '' if self.selected_languages: lang_ids = Language.objects.filter( key__in=self.selected_languages).values_list('id', flat=True) if lang_ids: constraint += f' AND sub.language_id IN ({", ".join(["%s"] * len(lang_ids))})' params.extend(lang_ids) self.selected_languages = set() queryset = super(RankedSubmissions, self).get_queryset().filter(user__is_unlisted=False) join_sql_subquery( queryset, subquery=""" SELECT sub.id AS id FROM ( SELECT sub.user_id AS uid, MAX(sub.points) AS points FROM judge_submission AS sub {contest_join} WHERE sub.problem_id = %s AND {points} > 0 {constraint} GROUP BY sub.user_id ) AS highscore STRAIGHT_JOIN ( SELECT sub.user_id AS uid, sub.points, MIN(sub.time) as time FROM judge_submission AS sub {contest_join} WHERE sub.problem_id = %s AND {points} > 0 {constraint} GROUP BY sub.user_id, {points} ) AS fastest ON (highscore.uid = fastest.uid AND highscore.points = fastest.points) STRAIGHT_JOIN judge_submission AS sub ON (sub.user_id = fastest.uid AND sub.time = fastest.time) {contest_join} WHERE sub.problem_id = %s AND {points} > 0 {constraint} GROUP BY sub.user_id """.format(points=points, contest_join=contest_join, constraint=constraint), params=params * 3, alias='best_subs', join_fields=[('id', 'id')], ) if self.in_contest: return queryset.order_by('-contest__points', 'time') else: return queryset.order_by('-points', 'time')
def get_queryset(self): queryset = self._get_queryset() if not self.in_contest: join_sql_subquery( queryset, subquery=str(Problem.get_visible_problems(self.request.user).distinct().only('id').query), params=[], join_fields=[('problem_id', 'id')], alias='visible_problems', ) return queryset
def get_queryset(self): if self.in_contest: contest_join = '''INNER JOIN judge_contestsubmission AS cs ON (sub.id = cs.submission_id) INNER JOIN judge_contestparticipation AS cp ON (cs.participation_id = cp.id)''' points = 'cs.points' constraint = 'AND cp.contest_id = %s' else: contest_join = '' points = 'sub.points' constraint = '' queryset = super(RankedSubmissions, self).get_queryset().filter(user__is_unlisted=False) join_sql_subquery( queryset, subquery=''' SELECT sub.id AS id FROM ( SELECT sub.user_id AS uid, MAX(sub.points) AS points FROM judge_submission AS sub {contest_join} WHERE sub.problem_id = %s AND {points} > 0 {constraint} GROUP BY sub.user_id ) AS highscore STRAIGHT_JOIN ( SELECT sub.user_id AS uid, sub.points, MIN(sub.time) as time FROM judge_submission AS sub {contest_join} WHERE sub.problem_id = %s AND {points} > 0 {constraint} GROUP BY sub.user_id, {points} ) AS fastest ON (highscore.uid = fastest.uid AND highscore.points = fastest.points) STRAIGHT_JOIN judge_submission AS sub ON (sub.user_id = fastest.uid AND sub.time = fastest.time) {contest_join} WHERE sub.problem_id = %s AND {points} > 0 {constraint} GROUP BY sub.user_id '''.format(points=points, contest_join=contest_join, constraint=constraint), params=[self.problem.id, self.contest.id] * 3 if self.in_contest else [self.problem.id] * 3, alias='best_subs', join_fields=[('id', 'id')], ) if self.in_contest: return queryset.order_by('-contest__points', 'time') else: return queryset.order_by('-points', 'time')