Django:根据最新的子模型字段订购QuerySet

Question

假设我想显示按其最新冲刺时间排序的运行者列表。

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)

这是我将在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

Django QuerySet documentation指出:

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.

我想我需要在这里应用一些过滤器，但是我不确定Django到底期望什么...

需要注意的是，在此示例中它并不明显:Runner表将具有数百个条目，sprint也将具有数百个条目，并且在以后的日子中可能会有数千个条目。数据将显示在分页 View 中，因此无法使用Python进行排序。

我看到的唯一另一种可能性是自己编写SQL，但我想不惜一切代价避免这种情况。

Answer 1

我认为没有办法通过仅一个查询的ORM来做到这一点，您可以获取运行者列表并使用annotate添加其最新的sprint ID-然后过滤并排序这些sprint。

>>> 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

这非常快，并且仍将利用数据库的索引和缓存。

几千条记录并没有那么多，对于那些数字来说应该可以很好地工作。如果您开始遇到问题，建议您硬着头皮使用原始SQL。