如何在Django体育博彩游戏中计算并存储分数？技术方案求解

Hey there! Let's work through the score calculation challenges you're facing with your first Django sports betting game. I've been in similar spots with Django projects, so let's break down each option and land on a practical solution.

1. Why Database Views Clash with Django Migrations #

First off, database views are great for simple read-only calculations, but Django's migration system is built around model definitions—it doesn't track views natively. That means you'd have to manually create/update views via RunSQL operations in migrations, which gets messy fast (especially if your view logic changes often). Ditching the view approach is definitely the right call here.

2. The SQL Trigger Problem: Passing User IDs #

SQL triggers live at the database level, which makes them tricky to use with business context like user IDs. Triggers can only access data from the row that triggered the event (e.g., the updated match's goals), but user IDs are tied to your Bet or User models in Django.

You could try linking triggers to your bet table (joining on match ID to get associated users), but this couples your logic tightly to the database schema. Plus, maintaining triggers in Django requires writing raw SQL in migrations, which is hard to debug and not very "Django-like." I'd recommend skipping triggers unless you have a very specific performance need that can't be met otherwise.

3. Adding a scores Table: The Django-Friendly Approach #

This is the most sustainable solution, as it plays nicely with Django's ORM and migration system. Here's how to implement it step-by-step:

3.1 Define the Score Model #

Start by creating a Score model that links users to matches and stores calculated scores:

from django.db import models
from django.contrib.auth.models import User
from .models import Match  # Import your existing Match model

class Score(models.Model):
    user = models.ForeignKey(User, on_delete=models.CASCADE, related_name='bet_scores')
    match = models.ForeignKey(Match, on_delete=models.CASCADE, related_name='match_scores')
    total_score = models.IntegerField(default=0)
    updated_at = models.DateTimeField(auto_now=True)

    class Meta:
        unique_together = ('user', 'match')  # Ensure one score per user per match

3.2 Trigger Score Calculation When Matches Update #

You need to recalculate scores whenever a match's goal count changes. Django's signals are perfect for this, or you can trigger calculations manually in your view/command code.

Option A: Use Django Signals

Signals automatically run code when a model is saved. Here's an example:

from django.db.models.signals import post_save
from django.dispatch import receiver
from django.db.models import F
from .models import Match, Score, Bet  # Import your Bet model (stores user predictions)

@receiver(post_save, sender=Match)
def update_match_scores(sender, instance, created, **kwargs):
    # Only run when updating an existing match, not creating a new one
    if not created:
        # Get all bets linked to this updated match
        bets = Bet.objects.filter(match=instance)
        
        for bet in bets:
            # Calculate score based on your rules
            calculated_score = calculate_bet_score(bet, instance)
            
            # Update or create the score record for the user/match
            Score.objects.update_or_create(
                user=bet.user,
                match=instance,
                defaults={'total_score': calculated_score}
            )

def calculate_bet_score(bet, match):
    # Example scoring logic: adjust this to your game's rules
    score = 0
    # Full correct prediction (goals for both teams)
    if bet.predicted_home == match.actual_home and bet.predicted_away == match.actual_away:
        score = 15
    # Correct outcome (win/loss/draw) but wrong goal counts
    elif (bet.predicted_home > bet.predicted_away and match.actual_home > match.actual_away) or \
         (bet.predicted_home < bet.predicted_away and match.actual_home < match.actual_away) or \
         (bet.predicted_home == bet.predicted_away and match.actual_home == match.actual_away):
        score = 5
    return score

Option B: Manual Trigger (More Control)

If you update matches through a specific view or management command, you can directly call the score calculation function there instead of using signals. This avoids unexpected triggers (e.g., if you're testing match updates) and makes debugging easier.

3.3 Performance Tips #

If you have a lot of users or matches, batch operations will help speed things up:

• Use bulk_update instead of looping through each bet individually
• Offload score calculation to an async task queue like Celery if updates are frequent (prevents blocking web requests)

4. Alternative: Calculate Scores On-the-Fly #

If you don't want to maintain a scores table, you can calculate scores dynamically using Django's ORM annotations when querying bets. This works well if your scoring logic is simple and you don't need to display total scores often:

from django.db.models import Case, When, IntegerField, Value, F

# Query a user's bets with calculated scores
user_bets = Bet.objects.filter(user=request.user).annotate(
    score=Case(
        When(
            predicted_home=F('match__actual_home'),
            predicted_away=F('match__actual_away'),
            then=Value(15)
        ),
        When(
            (F('predicted_home') > F('predicted_away')) & (F('match__actual_home') > F('match__actual_away')) |
            (F('predicted_home') < F('predicted_away')) & (F('match__actual_home') < F('match__actual_away')) |
            (F('predicted_home') == F('predicted_away')) & (F('match__actual_home') == F('match__actual_away')),
            then=Value(5)
        ),
        default=Value(0),
        output_field=IntegerField()
    )
)

The downside here is that you'll recalculate scores every time you query, which can get slow if you have thousands of bets or need to display user totals frequently.

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