Counting on Multiple Columns with the Django ORM
Series Part 8 of 11 in Nifty Django Features
This feature is about counting on multiple columns in the ORM. The Count expression currently only supports counting on a single column, which can be a bit restrictive. Let’s see how we can extend the ORM to do more.
Here are the models we’ll be working with:
from django.db import models
from django.db.models import Count
class Species(models.Model):
name = models.CharField(max_length=100)
class Pet(models.Model):
species = models.ForeignKey(Species, on_delete=models.CASCADE)
name = models.CharField(max_length=100)
class Vet(models.Model):
name = models.CharField(max_length=100)
class Appointment(models.Model):
vet = models.ForeignKey(Vet, on_delete=models.CASCADE)
pet = models.ForeignKey(Pet, on_delete=models.CASCADE)
Let’s try to count the number of dogs that each vet has seen.
from django.db.models import Count
Appointment.objects.filter(
pet__species__name="dog",
).values("pet__name", "vet__name")
# [{'pet__name': 'Buddy', 'vet__name': 'Dr. Smith'},
# {'pet__name': 'Buddy', 'vet__name': 'Dr. Smith'},
# {'pet__name': 'Buddy', 'vet__name': 'Dr. Jones'},
# {'pet__name': 'Rex', 'vet__name': 'Dr. Smith'}]
Our data shows that Buddy visited Dr. Smith twice and Dr. Jones once, while Rex visited Dr. Smith. So we have four appointments for two pets across two vets, resulting in three unique pet-vet pairs.
Unfortunately trying to annotate a Count expression won’t work for us here.
Species.objects.filter(name="dog").annotate(
count_by_appointment=Count("pet__appointment", distinct=True),
count_by_vet=Count("pet__appointment__vet", distinct=True),
).values("count_by_appointment", "count_by_vet").first()
# {'count_by_appointment': 4, 'count_by_vet': 2}
# Unique (pet, vet) pairs = 3, but neither approach finds it.
Trying to count on appointment or vet alone won’t work for us. Counting on appointment will return 4 and counting by vet will return 2. The trouble is that Count only works on a single column, but we want to count on two.
CountSubquery: Count on distinct rows of data
To make this work, we can create a new Subquery class that can count rows from a nested query. This way Subquery produces one row per unique pair; and the outer COUNT(*) counts those rows.
from django.db.models import IntegerField, OuterRef, Subquery
class CountSubquery(Subquery):
template = "(SELECT COUNT(*) FROM (%(subquery)s) _count)"
output_field = IntegerField()
Species.objects.filter(pet__species__name="dog").annotate(
count_pairs=CountSubquery(
Appointment.objects.filter(pet__species=OuterRef("pk"))
.values("pet", "vet")
.distinct()
),
).values("count_pairs").first()
# {"count_pairs": 3}
While that is cool, the real feature here is Django’s expression system’s flexibility. Many of the expressions have a template that controls how it renders to SQL. This makes it straightforward to customize the ORM to generate the SQL that you want. While there are times to drop to raw SQL, the ORM’s API does a good job of providing the hooks to avoid it.
For more information take a look at the following:
- Func() expressions
- Creating your own Aggregate Functions
- Subquery() expressions
- Writing your own Query Expressions
If you have thoughts, comments, or questions, please let me know. You can set up a meeting with me, or find me on the Fediverse, Django Discord server or use email.
Written by Tim Schilling
Django 6.x Steering Council member, maintainer of django-debug-toolbar and django-simple-history, and a professional software engineer since 2009. These days, I mentor developers one-on-one.
