Обобщенная аннотация подзапроса с фильтром по внешней ссылке ForeignKey

Question

Я пытаюсь закодировать эквивалентный запрос Django из этого SQL-запроса, но застрял. Любая помощь приветствуется. Я получаю гонкуid и по этой гонке я хочу сделать некоторую статистику: nb_race = количество скачек лошади перед данной гонкой, best_chrono = лучшее время лошади перед данной гонкой.

SELECT *, (SELECT count(run.id)
                FROM runner run
                INNER JOIN race
                ON run.race_id = race.id
                WHERE run.horse_id = r.horse_id
                AND race.datetime_start < rc.datetime_start 
                ) AS nb_race, 
          (SELECT min(run.chrono)
                FROM runner run
                INNER JOIN race
                ON run.race_id = race.id
                WHERE run.horse_id = r.horse_id
                AND race.datetime_start < rc.datetime_start 
                ) AS best_time
FROM runner r, race rc
WHERE r.race_id = rc.id
AND rc.id = 7890

Модели Django:

class Horse(models.Model):
    id = AutoField(primary_key=True)
    name = models.CharField(max_length=255, blank=True, null=True, default=None)

class Race(models.Model):
    id = AutoField(primary_key=True)
    datetime_start = models.DateTimeField(blank=True, null=True, default=None)
    name = models.CharField(max_length=255, blank=True, null=True, default=None)

class Runner(models.Model):
    id = AutoField(primary_key=True)
    horse = models.ForeignKey(Horse, on_delete=models.PROTECT)
    race = models.ForeignKey(Race, on_delete=models.PROTECT)
    chrono = models.DecimalField(max_digits=10, decimal_places=2, blank=True, null=True, default=None)

Answer 1

Выражение подзапроса можно использовать для компиляции дополнительного набора запросов как подзапроса, который зависит от основного набора запросов, и выполнять их вместе как один SQL.

from django.db.models import OuterRef, Subquery, Count, Min, F

# prepare a repeated expression about previous runners, but don't execute it yet
prev_run = (
    Runner.objects
    .filter(
        horse=OuterRef('horse'),
        race__datetime_start__lt=OuterRef('race__datetime_start'))
    .values('horse')
)
queryset = (
    Runner.objects
    .values('id', 'horse_id', 'race_id', 'chrono', 'race__name', 'race__datetime_start')
    .annotate(
        nb_race=Subquery(prev_run.annotate(nb_race=Count('id')).values('nb_race')),
        best_time=Subquery(prev_run.annotate(best_time=Min('chrono')).values('best_time'))
    )
)

Некоторые используемые здесь приемы описаны в связанных документах:

• Поля вывода подзапроса должны быть ограничены .values(...) в одно поле: только агрегированное значение
• Подзапрос должен быть набором запросов (который оценивается лениво и объединяется вместе), а не значением (которое будет оцениваться немедленно и завершиться ошибкой). Следовательно.annotate() используется в подзапросе (не .aggregate()). Это добавляетGROUP BY race.horse_id, но это не проблема, потому что есть WHERE race.horse_id = ... и "group by" будет окончательно проигнорирована оптимизатором SQL в современной бэкэнде базы данных.

Он компилируется в запрос, эквивалентный SQL в примере. Проверьте SQL:

>>> print(str(queryset.query))
SELECT ...,
  (SELECT COUNT(U0.id)
   FROM runner U0 INNER JOIN race U1 ON (U0.race_id = U1.id)
   WHERE (U0.horse_id = runner.horse_id AND U1.datetime_start < race.datetime_start)
   GROUP BY U0.horse_id
   ) AS nb_race,
   ...
FROM runner INNER JOIN race ON (runner.race_id = race.id)

Незначительное различие заключается в том, что подзапрос использует некоторые внутренние псевдонимы, такие как U0 и U1.