Django: Ordering a QuerySet based on a latest child models field

Question

Lets assume I want to show a list of runners ordered by their latest sprint time.

class Runner(models.Model):
    name = models.CharField(max_length=255)

class Sprint(models.Model):
    runner = models.ForeignKey(Runner)
    time = models.PositiveIntegerField()
    created = models.DateTimeField(auto_now_add=True)

This is a quick sketch of what I would do in SQL:

SELECT runner.id, runner.name, sprint.time
FROM runner
LEFT JOIN sprint ON (sprint.runner_id = runner.id)
WHERE 
  sprint.id = (
    SELECT sprint_inner.id
    FROM sprint as sprint_inner
    WHERE sprint_inner.runner_id = runner.id
    ORDER BY sprint_inner.created DESC
    LIMIT 1
  )
  OR sprint.id = NULL
ORDER BY sprint.time ASC

The Django QuerySet documentation states:

It is permissible to specify a multi-valued field to order the results by (for example, a ManyToManyField field). Normally this won’t be a sensible thing to do and it’s really an advanced usage feature. However, if you know that your queryset’s filtering or available data implies that there will only be one ordering piece of data for each of the main items you are selecting, the ordering may well be exactly what you want to do. Use ordering on multi-valued fields with care and make sure the results are what you expect.

I guess I need to apply some filter here, but I'm not sure what exactly Django expects...

One note because it is not obvious in this example: the Runner table will have several hundred entries, the sprints will also have several hundreds and in some later days probably several thousand entries. The data will be displayed in a paginated view, so sorting in Python is not an option.

The only other possibility I see is writing the SQL myself, but I'd like to avoid this at all cost.

Answer 1

I don't think there's a way to do this via the ORM with only one query, you could grab a list of runners and use annotate to add their latest sprint id's -- then filter and order those sprints.

>>> from django.db.models import Max

# all runners now have a `last_race` attribute,
# which is the `id` of the last sprint they ran
>>> runners = Runner.objects.annotate(last_race=Max("sprint__id"))

# a list of each runner's last sprint ordered by the the sprint's time,
# we use `select_related` to limit lookup queries later on
>>> results = Sprint.objects.filter(id__in=[runner.last_race for runner in runners])
...                         .order_by("time")
...                         .select_related("runner")

# grab the first result
>>> first_result = results[0]

# you can access the runner's details via `.runner`, e.g. `first_result.runner.name`
>>> isinstance(first_result.runner, Runner)
True

# this should only ever execute 2 queries, no matter what you do with the results
>>> from django.db import connection
>>> len(connection.queries)
2

This is pretty fast and will still utilize the databases's indices and caching.

A few thousand records isn't all that much, this should work pretty well for those kinds of numbers. If you start running into problems, I suggest you bite the bullet and use raw SQL.