如何使用Django ORM根据特定用户在指定竞赛中各题的最高分计算总得分

Got it, let's break down how to build this function using Django ORM. The goal is to fetch the highest score a user achieved for each problem in a contest, then sum those values for the total score.

Step-by-Step Implementation #

First, here's the complete function with explanations:

from django.db.models import Max
from django.contrib.auth import get_user_model
from .models import Contest, Submission

User = get_user_model()

def calculate_contest_total_score(contest_id, user_id):
    try:
        # Fetch the contest and user instances (handle missing entries)
        contest = Contest.objects.get(id=contest_id)
        user = User.objects.get(id=user_id)
        
        # Optional: Verify the user is a participant in the contest
        if user not in contest.participants.all():
            return 0
        
        # Get all problems associated with the contest
        contest_problems = contest.problems.all()
        
        # Use ORM to group submissions by problem, get max score per problem for the user
        problem_max_scores = Submission.objects.filter(
            participant=user,
            problem__in=contest_problems
        ).values('problem_id').annotate(
            highest_score=Max('score')
        )
        
        # Sum all the highest scores (problems with no submissions contribute 0)
        total_score = sum(score_entry['highest_score'] for score_entry in problem_max_scores)
        
        return total_score
    
    except (Contest.DoesNotExist, User.DoesNotExist):
        # Return 0 if either contest or user doesn't exist
        return 0

Key Details Explained #

• Model Validation: We first fetch the Contest and User instances, catching cases where either doesn't exist and returning 0 as a safe default.
• Participant Check: The optional check ensures we only calculate scores for users who are actual participants in the contest (remove this if your use case doesn't require it).
• ORM Grouping: Using values('problem_id') groups submissions by each problem, and annotate(Max('score')) computes the highest score the user got for that problem. This is efficient because it runs a single aggregated query instead of multiple per-problem queries.
• Summing Scores: We iterate over the aggregated results and sum the highest scores. Problems the user never submitted to won't appear in the results, so they automatically contribute 0 to the total.

Example Usage #

If we have the submission records you mentioned:
(1, 80), (1, 100), (2, 150), (2, 200), (3, 220), (3, 300)
Calling calculate_contest_total_score(contest_id=1, user_id=123) would return 600 (100 + 200 + 300), which matches your expected result.

内容的提问来源于stack exchange，提问作者Rorschach_88