Predictions: Recording the User, Expert, Guest(s) & AI Predictions Per Fixture

Premier League Predictor: Django & MySQL

Chapter 5 · Predictions: Recording the User, Expert, Guest(s) & AI Predictions Per Fixture

Before a fixture's real result exists, four real sources each predict what they think will happen. This chapter builds the one model that records all four — and finally resolves the real gap Chapter 1 flagged: MySQL has no equivalent to PostgreSQL's own partial unique index, and this chapter needs exactly that shape of rule.

One Model, Four Sources

# predictor/models.py (additions)
from django.db.models import Case, When, Value, F
from django.db.models.functions import Concat


class Prediction(models.Model):
    SOURCE_USER = 'user'
    SOURCE_EXPERT = 'expert'
    SOURCE_GUEST = 'guest'
    SOURCE_AI = 'ai'
    SOURCE_CHOICES = [
        (SOURCE_USER, 'User'), (SOURCE_EXPERT, 'Expert'),
        (SOURCE_GUEST, 'Guest'), (SOURCE_AI, 'AI'),
    ]

    fixture = models.ForeignKey(Fixture, on_delete=models.CASCADE, related_name='predictions')
    source = models.CharField(max_length=10, choices=SOURCE_CHOICES)
    guest_name = models.CharField(max_length=100, null=True, blank=True)
    predicted_home_score = models.PositiveSmallIntegerField()
    predicted_away_score = models.PositiveSmallIntegerField()
    points_awarded = models.PositiveSmallIntegerField(null=True, blank=True)

    unique_key = models.GeneratedField(
        expression=Case(
            When(source=SOURCE_GUEST, then=Value(None)),
            default=Concat(F('fixture_id'), Value('-'), F('source')),
        ),
        output_field=models.CharField(max_length=30, null=True),
        db_persist=True,
    )

    class Meta:
        constraints = [
            models.UniqueConstraint(fields=['unique_key'], name='uq_prediction_single_source_per_fixture')
        ]

MySQL's Own Real Workaround: A Generated Column Exploiting NULL's Own Uniqueness Rules

Three of the four sources genuinely predict exactly once per fixture; guest deliberately doesn't — some weeks have several. plpredict-fastapi1's own PostgreSQL sibling solved this with a partial unique index, WHERE source != 'guest'. MySQL has no such feature — it doesn't support a conditional clause on an index at all. The real, standard MySQL workaround exploits a different, universal SQL rule instead: a unique index never treats two NULL values as equal to each other, so any number of NULLs can coexist in a column a unique index otherwise enforces strictly.

unique_key above is a real, database-computed column — Django's own GeneratedField, added in Django 5.0 — that evaluates, for every row, to either NULL (for a guest prediction) or a real, deterministic string like "42-expert" (fixture id + source, for everyone else). The UniqueConstraint is placed on that generated column, not on fixture/source directly. It compiles to genuine MySQL syntax:

unique_key VARCHAR(30) GENERATED ALWAYS AS (
    CASE WHEN source = 'guest' THEN NULL
         ELSE CONCAT(fixture_id, '-', source)
    END
) STORED UNIQUE;

The identical real guarantee, a genuinely different mechanism

A guest row's unique_key is always NULL — MySQL never rejects a second, third, or tenth NULL in a unique-indexed column, so any number of guest predictions coexist freely on the same fixture, exactly as intended. A user/expert/AI row's unique_key is a real, non-null string unique per (fixture, source) — the unique index rejects a genuine second attempt outright, exactly like the PostgreSQL sibling's own partial index does. Two structurally different database features — a conditional index in one case, NULL-exempt uniqueness on a computed column in the other — arrive at the identical real rule.

db_persist=True matters specifically for MySQL: GeneratedField with db_persist=True creates a real STORED column (its value genuinely written to disk on every insert/update), which MySQL 5.7+ supports and can build a real index on — a virtual, non-stored generated column has real limitations around indexing that make STORED the safer, portable choice here.

Recording (and Correcting) a Prediction: One Real Built-In Call

plpredict-fastapi1's own upsert route had to manually query for an existing row, branch, and either update or insert. Django's ORM already has this exact pattern built in:

# predictor/views.py (additions)
from .models import Prediction


@require_POST
def upsert_prediction(request, fixture_id):
    fixture = get_object_or_404(Fixture, pk=fixture_id)
    payload = json.loads(request.body)
    source = payload.get('source')
    guest_name = payload.get('guest_name')

    is_guest = source == Prediction.SOURCE_GUEST
    if is_guest and not guest_name:
        return JsonResponse({'error': 'guest_name is required for a guest prediction'}, status=400)
    guest_name = guest_name if is_guest else None

    lookup = {'fixture': fixture, 'source': source}
    if is_guest:
        lookup['guest_name'] = guest_name

    prediction, created = Prediction.objects.update_or_create(
        **lookup,
        defaults={
            'predicted_home_score': payload.get('predicted_home_score'),
            'predicted_away_score': payload.get('predicted_away_score'),
        },
    )
    return JsonResponse({'id': prediction.id, 'created': created})


def list_predictions(request, fixture_id):
    predictions = Prediction.objects.filter(fixture_id=fixture_id).values(
        'id', 'source', 'guest_name', 'predicted_home_score', 'predicted_away_score', 'points_awarded'
    )
    return JsonResponse(list(predictions), safe=False)

A real ORM built-in, where the FastAPI sibling had to hand-write the same logic

Prediction.objects.update_or_create(**lookup, defaults={...}) looks for an existing row matching lookup, updates it with defaults if found, or creates a brand-new row with both lookup and defaults combined if not — the exact find-then-update-or-insert logic plpredict-fastapi1's own Chapter 5 wrote out explicitly as a query.first() check followed by an if/else. SQLAlchemy's Session API has no single built-in call for this; Django's QuerySet does, the same category of "gets this for free" payoff as Chapter 3's own SeasonTeamInline.

.values('id', 'source', ...) in list_predictions is a small, real convenience of its own — a queryset that returns plain dictionaries directly, ready for JsonResponse(list(...), safe=False), with no separate serializer layer needed for a response this simple.

Turning several guest scorelines into one comparable figure is still Chapter 6's job

This chapter only records what each individual guest predicted — a real, separate Prediction row per guest, each with its own guest_name. Averaging multiple real scorelines into the single "guest" figure the prediction league table eventually scores is deliberately left for Chapter 6, exactly as in the FastAPI sibling course.

No prediction deadline is enforced yet

Nothing here checks fixture.kickoff_time against the current time before accepting an upsert — the same honest, deliberately unaddressed gap the FastAPI sibling course also carries.

Recording Predictions From the Frontend

A compact, reusable form, sending the CSRF token established in Chapter 4 on every submission:

// static/predictor/predictions.js
async function submitPrediction(fixtureId, source, guestName, homeScore, awayScore) {
  const res = await fetch(`/fixtures/${fixtureId}/predictions/upsert/`, {
    method: 'POST',
    headers: {
      'Content-Type': 'application/json',
      'X-CSRFToken': csrftoken,
    },
    body: JSON.stringify({
      source,
      guest_name: guestName || null,
      predicted_home_score: Number(homeScore),
      predicted_away_score: Number(awayScore),
    }),
  });

  if (!res.ok) {
    const error = await res.json();
    alert(error.error);
    return;
  }

  alert('Prediction saved.');
}

Where This Course Is Headed

Entering real results, defining how a correct score and a correct result actually get calculated, and resolving how multiple guest predictions become one comparable figure (Chapter 6); the real league table (Chapter 7); the prediction league table (Chapter 8); and promotion/relegation (Chapter 9).

Hands-On Exercises

Exercise 1

Explain why unique_key evaluates to NULL for a guest prediction but a real string for every other source, and why that specific choice is what makes the UniqueConstraint on unique_key behave correctly for both cases.

📄 View solution

Exercise 2

Explain why db_persist=True is specifically required for this GeneratedField to work correctly on MySQL, and what real database feature it corresponds to.

📄 View solution

Exercise 3

Record a user, an expert, an AI, and two separately-named guest predictions against a single real fixture using upsert_prediction, then submit a second user prediction for the same fixture and confirm it updates the existing row (created: false) rather than creating a duplicate.

📄 View solution

Chapter 5 Quick Reference

Prediction — one model, four sources (user/expert/guest/ai), keyed by fixture + source (+ guest_name for guests)
unique_key — a real GeneratedField (Django 5.0+): NULL for guest rows, a deterministic fixture-source string otherwise
Real MySQL workaround — NULL is never unique-constrained against another NULL, exploited here to get the identical guarantee PostgreSQL's own partial index provides
db_persist=True — creates a real STORED generated column, required for MySQL 5.7+ to index it at all
update_or_create() — Django's own real built-in upsert, replacing the FastAPI sibling's own hand-written find-then-branch logic
Still deferred to Chapter 6 — how multiple guest scorelines become one comparable figure
Next chapter: Entering results and calculating correct score vs. correct result