The Real League Table: Points, Goal Difference, Won/Drawn/Lost

Premier League Predictor: Django & MySQL

Chapter 7 · The Real League Table: Points, Goal Difference, Won/Drawn/Lost

Every fixture Chapter 6 fills in a result for is a real, scored match. This chapter turns that pile of results into the table every Premier League fan actually recognizes — 20 rows, sorted by points, then goal difference, then goals scored. It's a genuinely harder query than it looks, and the reason why is worth walking through in full rather than skipping to the answer.

Starting From SeasonTeam, Not From Fixture

A team with zero games played this season still needs to appear in the table — with 0 played, 0 points, a blank row waiting to be filled in. Build the table by querying Fixture directly and no such team ever shows up at all, since a team with no fixtures produces no rows to aggregate. Chapter 2's own SeasonTeam model — the real record of which 20 teams are competing this season — is the correct starting point instead, exactly the same membership reasoning Chapter 3's own admin tooling and Chapter 9's own promotion/relegation logic both already lean on.

A Tempting, Genuinely Wrong Django ORM Attempt

Fixture has two separate foreign keys to Team — home_team and away_team — so it's natural to reach for both reverse relations, home_fixtures and away_fixtures, in one annotate() call:

# DO NOT USE — this looks reasonable and compiles fine, but is genuinely wrong
from django.db.models import Sum, Case, When, IntegerField, F

teams = Team.objects.filter(seasonteam__season_id=season_id).annotate(
    home_goals=Sum(
        Case(
            When(
                home_fixtures__gameweek__season_id=season_id,
                home_fixtures__status='played',
                then=F('home_fixtures__home_score'),
            ),
            default=0, output_field=IntegerField(),
        )
    ),
    away_goals=Sum(
        Case(
            When(
                away_fixtures__gameweek__season_id=season_id,
                away_fixtures__status='played',
                then=F('away_fixtures__away_score'),
            ),
            default=0, output_field=IntegerField(),
        )
    ),
)

Two separate reverse relations, joined in the same query, silently multiply the results

Django's own documentation warns about exactly this shape of query, under "Combining multiple aggregations": joining two different reverse foreign key relations in a single annotate() call produces one combined SQL JOIN across both relations, and each combination of a home fixture row and an away fixture row for the same team becomes its own row in that join — a real cross-join, not two independent sums. Say a team played 4 home fixtures (scoring 8 goals total at home) and 4 away fixtures (scoring 6 goals total away) — the join produces 4×4 = 16 combined rows. Sum('home_fixtures__home_score') now adds up every home-goals value once for each of the 4 away-fixture rows it got paired with, inflating the true total of 8 up to 8×4 = 32. The away sum inflates the same way in the other direction, to 6×4 = 24. The team's real goals-for total is 8 + 6 = 14 — this query would report 32 + 24 = 56, a number that looks plausible on a screen but is four times too high, with no error raised anywhere to flag it.

Nothing about this bug is specific to football or to this schema — it's a well-documented, general Django ORM gotcha that shows up any time an annotate() call aggregates over more than one distinct reverse relation at once.

The Real Fix: Normalize Before Aggregating, With a Raw Query

The actual fix is the same one plpredict-fastapi1's own PostgreSQL sibling reaches for: don't aggregate two separate relations in one pass at all — normalize home rows and away rows into one shared shape first (a real SQL UNION ALL), then aggregate that single, already-flat result set. Django's ORM has no way to express a UNION ALL inside a subquery cleanly, so this is exactly the kind of genuinely complex aggregate worth dropping into a real raw query for — Django's own equivalent of SQLAlchemy's text() escape hatch is the model manager's .raw() method:

# predictor/views.py (additions)
LEAGUE_TABLE_SQL = """
    SELECT
        t.id,
        t.name AS team_name,
        COALESCE(agg.played, 0) AS played,
        COALESCE(agg.won, 0) AS won,
        COALESCE(agg.drawn, 0) AS drawn,
        COALESCE(agg.lost, 0) AS lost,
        COALESCE(agg.goals_for, 0) AS goals_for,
        COALESCE(agg.goals_against, 0) AS goals_against,
        COALESCE(agg.goals_for, 0) - COALESCE(agg.goals_against, 0) AS goal_difference,
        COALESCE(agg.points, 0) AS points
    FROM predictor_seasonteam st
    JOIN predictor_team t ON t.id = st.team_id
    LEFT JOIN (
        SELECT
            team_id,
            COUNT(*) AS played,
            SUM(CASE WHEN goals_for > goals_against THEN 1 ELSE 0 END) AS won,
            SUM(CASE WHEN goals_for = goals_against THEN 1 ELSE 0 END) AS drawn,
            SUM(CASE WHEN goals_for < goals_against THEN 1 ELSE 0 END) AS lost,
            SUM(goals_for) AS goals_for,
            SUM(goals_against) AS goals_against,
            SUM(
                CASE WHEN goals_for > goals_against THEN 3
                     WHEN goals_for = goals_against THEN 1
                     ELSE 0 END
            ) AS points
        FROM (
            SELECT f.home_team_id AS team_id, f.home_score AS goals_for, f.away_score AS goals_against
            FROM predictor_fixture f
            JOIN predictor_gameweek gw ON gw.id = f.gameweek_id
            WHERE gw.season_id = %s AND f.status = 'played'
            UNION ALL
            SELECT f.away_team_id AS team_id, f.away_score AS goals_for, f.home_score AS goals_against
            FROM predictor_fixture f
            JOIN predictor_gameweek gw ON gw.id = f.gameweek_id
            WHERE gw.season_id = %s AND f.status = 'played'
        ) AS team_fixtures
        GROUP BY team_id
    ) AS agg ON agg.team_id = t.id
    WHERE st.season_id = %s
    ORDER BY points DESC, goal_difference DESC, goals_for DESC
"""


def league_table(request, season_id):
    rows = Team.objects.raw(LEAGUE_TABLE_SQL, [season_id, season_id, season_id])
    results = [
        {
            '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
    ]
    return JsonResponse(results, safe=False)

Django's raw() attaches every extra column as a real attribute, not just the model's own fields

team_name, played, won, goal_difference, and every other column above aren't real fields on the Team model — only id and name are. Django's own documentation is explicit that .raw() only strictly requires the model's primary key column to appear in the result set (here, t.id); every other selected column, whether or not it matches a real model field, is attached to each returned instance as an ordinary Python attribute anyway. That's what makes row.team_name, row.played, and row.points all work above despite none of them being declared anywhere on Team itself — a genuinely different mechanism from SQLAlchemy's own text(), which needs an explicit .mappings() call to get dict-like row access rather than model-instance access.

The ORDER BY has to live in the raw SQL itself

A RawQuerySet — what .raw() returns — has no .order_by() of its own the way an ordinary Django QuerySet does. If a raw query doesn't specify its own ordering, Django's documentation is upfront that the rows may come back in no particular order at all. That's exactly why ORDER BY points DESC, goal_difference DESC, goals_for DESC is written directly into LEAGUE_TABLE_SQL above, rather than left for Django to add afterward.

No head-to-head or fair-play tiebreak is implemented

Two teams finishing genuinely level on points, goal difference, and goals for are left in whatever order MySQL happens to return them — the same honest scope boundary the FastAPI/PostgreSQL sibling course carries. A real head-to-head record or disciplinary-points tiebreak would need its own dedicated query on top of this one.

Where This Course Is Headed

The prediction league table — aggregating every source's own points_awarded across the season, including the guest-averaging-by-fixture Chapter 6 resolved (Chapter 8); promotion and relegation, calculated directly from this chapter's own bottom-three ordering (Chapter 9); and styling plus a real gameweek/season selector (Chapter 10).

Hands-On Exercises

Exercise 1

Using this chapter's own worked example (a team with 4 played home fixtures totaling 8 goals, and 4 played away fixtures totaling 6 goals), explain step by step exactly how the naive dual-annotate() query arrives at 56 goals for instead of the real 14, tracing through the join multiplication that causes it.

📄 View solution

Exercise 2

Explain why normalizing home and away fixture rows into one shared shape with UNION ALL before grouping avoids the join-multiplication problem entirely, rather than just producing a smaller version of the same bug.

📄 View solution

Exercise 3

Call league_table for a real season containing at least one team with zero played fixtures so far, confirm that team still appears in the results with played=0 and points=0, then record a real result for one of that team's fixtures via enter_result and confirm the row updates correctly on the next call.

📄 View solution

Chapter 7 Quick Reference

Start from SeasonTeam — not Fixture, so a team with zero games played still shows an honest zero-played row
Real Django gotcha — annotating Sum() over two different reverse relations (home_fixtures/away_fixtures) in one call cross-joins them, silently multiplying (not just adding) the wrong totals
The fix — normalize home/away rows into one shape with a real UNION ALL subquery, then aggregate once
Team.objects.raw() — Django's own equivalent of SQLAlchemy's text() escape hatch; needs only the model's pk column present, attaches every other selected column as a real instance attribute
RawQuerySet has no order_by() — the ORDER BY has to be written directly into the raw SQL
Honest scope note — no head-to-head or fair-play tiebreaker implemented
Next chapter: The prediction league table — aggregating predictor performance across the season