Django ORM: count total and online screens per playlist in one query

Backend Live Coding November 13, 2026 python

Watch out: two Count aggregations over different relations in the same annotate multiply the rows (JOIN fan-out) and give wrong numbers.

python 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
# ======================================================================
# Q: I want, for each playlist, how many screens play it and how many of those
# are online, in a single query.
#
# Watch out: two Count aggregations over different relations in the same
# annotate multiply the rows (JOIN fan-out) and give wrong numbers. In that case
# I use a Subquery or Count(..., distinct=True).
# ======================================================================

from django.db.models import Count, Q

online_since = now() - timedelta(minutes=5)
playlists = (Playlist.objects
    .filter(workspace=ws)
    .annotate(screens_total=Count('screens'),
              screens_online=Count('screens', filter=Q(screens__last_seen__gte=online_since)))
    .order_by('-screens_total'))

Related Posts

Python shallow copy vs deep copy
Python shallow copy vs deep copy
Python closures and late binding in lambdas
Python closures and late binding in lambdas
Storing heartbeats from 250,000 screens with Redis write coalescing
Storing heartbeats from 250,000 screens with Redis write coalescing
Writing a retry decorator with exponential backoff in Python
Writing a retry decorator with exponential backoff in Python
SQL: what a CTE is and how to find duplicate records
SQL: what a CTE is and how to find duplicate records
Threads vs processes vs asyncio: when to use each
Threads vs processes vs asyncio: when to use each