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