Promotion & Relegation: Auto-Calculating the Bottom Three, Manually Entering the Promoted Three

Premier League Predictor: Django & MySQL

Chapter 9 · Promotion & Relegation: Auto-Calculating the Bottom Three, Manually Entering the Promoted Three

Chapter 3's own tip-box previewed this chapter directly: promotion and relegation is really just more SeasonTeam writes, at a season boundary instead of mid-season — and it went further, promising that SeasonTeamInline would get reused here rather than rebuilt. What's genuinely new in this chapter is where the bottom three come from — not a guess, not a manual entry, but Chapter 7's own real league table, reused as the actual source of truth.

Reusing Chapter 7's Table as the Real Source of Truth

Rather than duplicating the league-table SQL, Chapter 7's own league_table view gets a small, honest refactor — its query logic pulled into a plain function both it and this chapter's new views can call. LEAGUE_TABLE_SQL itself is completely unchanged from Chapter 7:

# predictor/views.py (refactored)
def compute_league_table(season_id):
    rows = Team.objects.raw(LEAGUE_TABLE_SQL, [season_id, season_id, season_id])
    return [
        {
            'team_id': row.id,
            'team_name': row.team_name,
            'played': row.played,
            'won': row.won,
            'drawn': row.drawn,
            'lost': row.lost,
            'goals_for': row.goals_for,
            'goals_against': row.goals_against,
            'goal_difference': row.goal_difference,
            'points': row.points,
        }
        for row in rows
    ]


def league_table(request, season_id):
    get_object_or_404(Season, pk=season_id)
    return JsonResponse(compute_league_table(season_id), safe=False)

compute_league_table still runs the exact same ORDER BY points DESC, goal_difference DESC, goals_for DESC from Chapter 7 — which matters here more than it did there, since this chapter is about to trust that ordering to identify real relegation.

A Read-Only Preview Before Anything Is Written

# predictor/views.py (additions)
REQUIRED_TEAMS_PER_SEASON = 20
RELEGATION_COUNT = 3


def relegation_preview(request, season_id):
    get_object_or_404(Season, pk=season_id)
    table = compute_league_table(season_id)
    if len(table) != REQUIRED_TEAMS_PER_SEASON:
        return JsonResponse(
            {'error': f'Season has {len(table)} teams, expected {REQUIRED_TEAMS_PER_SEASON}'},
            status=400,
        )

    bottom_three_ids = [row['team_id'] for row in table[-RELEGATION_COUNT:]]
    teams = Team.objects.filter(id__in=bottom_three_ids)
    return JsonResponse(
        [{'id': t.id, 'name': t.name, 'short_name': t.short_name} for t in teams],
        safe=False,
    )

A plain GET — nothing about calling it changes any data. Checking this before actually rolling anything over is exactly how a mistake (a wrong result entered somewhere, silently shifting who's really bottom three) gets caught before it's baked into next season's own competing set.

Carrying the Survivors Forward, For Real

# predictor/views.py (additions)
from django.db import transaction, IntegrityError


@require_POST
def rollover_survivors(request, new_season_id, old_season_id):
    new_season = get_object_or_404(Season, pk=new_season_id)
    old_season = get_object_or_404(Season, pk=old_season_id)

    if SeasonTeam.objects.filter(season=new_season).exists():
        return JsonResponse(
            {'error': 'New season already has teams; rollover expects a freshly created, empty season'},
            status=400,
        )

    old_table = compute_league_table(old_season_id)
    if len(old_table) != REQUIRED_TEAMS_PER_SEASON:
        return JsonResponse(
            {'error': f'Old season has {len(old_table)} teams, expected {REQUIRED_TEAMS_PER_SEASON}'},
            status=400,
        )

    # old_table is already correctly ordered by compute_league_table's own real sort
    survivors = old_table[:-RELEGATION_COUNT]

    try:
        with transaction.atomic():
            SeasonTeam.objects.bulk_create([
                SeasonTeam(season=new_season, team_id=row['team_id'])
                for row in survivors
            ])
    except IntegrityError:
        return JsonResponse(
            {'error': 'This rollover could not be completed cleanly — check whether it already ran'},
            status=409,
        )

    return JsonResponse({
        'new_season_id': new_season.id,
        'survivors_carried_forward': len(survivors),
        'next_step': "Add the 3 promoted teams via the Season admin page's own SeasonTeamInline",
    })

bulk_create() — the write-side counterpart to Chapter 6's own bulk_update()

Looping 17 individual SeasonTeam.objects.create(...) calls would issue 17 separate INSERT statements. bulk_create() collapses that into far fewer real INSERT statements — batched, the same way bulk_update() batched Chapter 6's own rescoring — and it still fully respects Chapter 2's own UniqueConstraint("season_id", "team_id") at the database level, since the constraint is enforced by MySQL itself on the real INSERT, regardless of which Django-level method produced it.

"Removing" a relegated team doesn't delete anything

Nothing about rollover_survivors touches old_season's own SeasonTeam rows at all — the relegated three keep their real, historical record of having competed in that season, exactly as Chapter 2's own "Team rows persist forever" design intended. "Removing" a relegated team means exactly one thing here: it simply never gets a matching SeasonTeam row created for new_season. survivors — every row in old_table except the bottom three — is what actually gets carried forward; the omission itself is the entire relegation mechanism.

The same friendly-IntegrityError pattern as Chapter 4, on a bigger operation

If this view is accidentally called twice for the same new_season_id, the second call would try to insert 17 SeasonTeam rows that already exist, colliding with Chapter 2's own UniqueConstraint. The try/except IntegrityError here is the exact same discipline Chapter 4 established for a single duplicate gameweek, just wrapping an operation that touches 17 rows instead of one — transaction.atomic() is what guarantees the whole batch either commits together or none of it does, so a mid-batch failure never leaves the new season half-populated.

Adding the Promoted Teams: Chapter 3's Admin Tool, Reused Rather Than Rebuilt

plpredict-fastapi1's own PostgreSQL sibling builds one combined route that both carries the survivors forward and accepts the 3 promoted teams' own names in the same request body. This course deliberately splits the two halves instead — rollover_survivors above handles only the mechanical part (auto-calculated, no human judgment involved), and the 3 promoted teams — a genuinely human decision, not something derivable from any query — are added directly through the Season admin page's own SeasonTeamInline, exactly the tool Chapter 3 built and explicitly promised this chapter would reuse.

The "+" popup covers the case where a promoted team doesn't exist yet

SeasonTeamInline's own team field uses autocomplete_fields (Chapter 3), which still shows Django admin's real "add another" "+" icon next to the search widget — clicking it opens a small popup to create a brand-new Team without ever leaving the Season admin page, provided the logged-in user has add_team permission. A promoted team newly arrived from the Championship, with no existing Team row at all, is handled by this popup directly; a promoted team that's been in the Premier League before (and already has a Team row from a previous season) is handled by the ordinary autocomplete search instead — both cases go through the exact same widget.

In practice: a site admin runs rollover_survivors (or clicks a button that calls it), then opens the new Season in the admin and adds exactly 3 rows to SeasonTeamInline — the same max_num = 20/validate_max = True cap from Chapter 3 means the inline itself refuses to let the season exceed 20 teams once the 17 survivors are already there, a small, real safety net that a hand-rolled promoted-teams route would have had to reimplement by hand.

No undo, and no partial-rollover recovery

If rollover_survivors is run against the wrong old season, there's no dedicated "undo" route — fixing it means manually removing the incorrect SeasonTeam rows through the admin (or the shell) and running it again correctly. transaction.atomic() guarantees the 17-row batch itself is all-or-nothing; it says nothing about a mistake made one level up, in which two seasons were confused for each other.

Using It From the Frontend

// static/predictor/rollover.js
async function previewRelegation(seasonId) {
  const res = await fetch(`/seasons/${seasonId}/relegation-preview/`);
  const teams = await res.json();
  alert('Relegated: ' + teams.map(t => t.short_name).join(', '));
}

async function rolloverSurvivors(newSeasonId, oldSeasonId) {
  const res = await fetch(`/seasons/${newSeasonId}/rollover-survivors-from/${oldSeasonId}/`, {
    method: 'POST',
    headers: { 'X-CSRFToken': csrftoken },
  });

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

  const result = await res.json();
  alert(`${result.survivors_carried_forward} survivors carried forward. ${result.next_step}`);
}

Where This Course Is Headed

A real gameweek/season selector — replacing every hardcoded season/gameweek id this course has used since Chapter 4 with a genuine dropdown backed by GET /seasons/, tying every route built across the whole course into one working interface (Chapter 10); deployment (Chapter 11); and a capstone integrating the finished predictor into the existing Astro-based site (Chapter 12).

Hands-On Exercises

Exercise 1

Explain why survivors is computed as old_table[:-RELEGATION_COUNT] directly from compute_league_table's own result, rather than by querying the database a second time for "every team except the bottom three," and what real guarantee this relies on from compute_league_table.

📄 View solution

Exercise 2

Explain why this chapter deliberately splits promotion/relegation into a programmatic rollover_survivors view plus Chapter 3's own SeasonTeamInline, rather than building one combined route that also accepts the 3 promoted teams' names the way the FastAPI/PostgreSQL sibling course does — and why that split is a genuinely good fit for Django specifically.

📄 View solution

Exercise 3

Complete a full real season (enter results for enough fixtures to give every team a real record), call relegation_preview, create a new empty Season in the admin, call rollover_survivors, confirm the new season has exactly 17 SeasonTeam rows, then add 3 promoted teams via SeasonTeamInline (using the "+" popup for at least one genuinely new team) and confirm the new season's roster reaches exactly 20.

📄 View solution

Chapter 9 Quick Reference

compute_league_table() — Chapter 7's own query logic, extracted so this chapter can reuse it as the real source of truth for relegation
relegation_preview — a read-only GET, a sanity check before anything real is committed
rollover_survivors — carries the 17 real survivors forward via bulk_create() inside transaction.atomic(), the write-side counterpart to Chapter 6's bulk_update()
Deliberate split from the sibling course — survivors are automated; the 3 promoted teams go through Chapter 3's own SeasonTeamInline, including its real "+" popup for a genuinely new team
"Removing" a relegated team — never deletes anything; the old season's own history is untouched, the team simply gets no SeasonTeam row in the new season
Real limit — no dedicated undo route; a double-call is caught by the same friendly-IntegrityError pattern from Chapter 4
Next chapter: A real gameweek/season selector, replacing every hardcoded id this course has used since Chapter 4