Аннотирование Django QuerySet с подсчетом подзапроса

Я создаю доску объявлений о работе. Каждая Job может иметь несколько связанных с ней Location объектов.

Я разработал свои модели местоположения и работы следующим образом:

class Location(BaseModel):
    slug = models.CharField(unique=True)
    city = models.OneToOneField(to="City", null=True)
    state = models.ForeignKey(to="State", null=True)
    country = models.ForeignKey(to="Country")


class Job(BaseModel):
    title = models.CharField()
    description = models.TextField()
    locations = models.ManyToManyField(
        to="Location",
        related_name="jobs",
        through="JobLocation",
    )

Итак, объект Location имеет возможность ссылаться на наше представление о стране (например, United States), штате (например, New York) или городе (например, Manhattan). Я заполняю поле slug модели Location следующим образом:

  • Если объект Location - страна, я использую название страны.

    • united-states

  • Если объект Location является государством, я использую (название государства + название страны).

    • new-york-united-states

  • Если объект Location - город, я использую (название города + название штата + название страны).

    • manhattan-new-york-united-states

Заполнив таким образом поле slug, я могу упростить запрос всех вакансий в определенном месте с помощью поиска endswith. Например, если есть вакансия Python в Манхэттене, штат Нью-Йорк, и вакансия React в Бруклине, штат Нью-Йорк, я могу получить все вакансии в штате Нью-Йорк следующим образом:

Job.objects.filter(locations__slug__endswith="new-york-united-states").distinct()

Теперь я хочу получить список всех моих объектов Location, и чтобы каждый элемент Location был аннотирован number_of_jobs. Это number_of_jobs должно быть количество всех рабочих мест в данном конкретном местоположении.

Например:

  • Объект местоположения Манхэттен должен иметь number_of_jobs=1 (задание Python)
  • Объект локации Бруклин должен иметь number_of_jobs=1 (Работа с React)
  • Объект локации Нью-Йорк должен иметь number_of_jobs=2 (задание Python + задание React)

Попытка 1:

Самое простое решение, заключающееся в том, чтобы Count подставить Location.jobs, не работает. Он показывает 1 задание для Манхэттена и 1 задание для Бруклина, но 0 заданий для Нью-Йорка.

Location.objects.annotate(number_of_jobs=Count("jobs", distinct=True))
# 1 job for Manhattan
# 1 job for Brooklyn
# 0 job for New York

Попытка 2:

subquery = Job.objects.filter(locations__slug__endswith=OuterRef("slug")).distinct().count()

Location.objects.annotate(number_of_jobs=Subquery(subquery))

Это также не работает, потому что count() выполняет набор запросов немедленно.

Попытка 3:

Основываясь на ответе Ювраджа, мне удалось приблизиться к ответу, но не совсем. 

jobs = Job.objects.filter(locations__slug__endswith=OuterRef("slug"))

subquery = jobs.annotate(job_count=Count("id", distinct=True)).values("job_count")[:1]

Location.objects.annotate(number_of_jobs=Subquery(subquery))

Если я запускаю эти выражения, используя debugsqlshell из django-debug-toolbar, и копирую SQL, который создает Django, и удаляю предложение GROUP BY, я получаю 100% правильный ответ. Я пока не понимаю, зачем Django добавляет GROUP BY и почему его удаление дает правильный ответ.

Я нахожусь в затруднительном положении с этим набором вопросов. Буду благодарен за любую помощь.

Есть более эффективный способ решения этой задачи. Вы можете обработать агрегацию с помощью Subquery без прямого вызова .count(), который, как вы заметили, оценивает набор запросов раньше времени. Вместо этого следует использовать annotate() и условный Count для связанных экземпляров Job.

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

# Subquery to count the jobs related to a location slug usingendswithlookup
subquery = Job.objects.filter(
locations__slug__endswith=OuterRef('slug')).values('id')

# Annotating the location objects with the count of jobs
locations_with_job_count = Location.objects.annotate(
number_of_jobs=Subquery(
    Job.objects.filter(locations__slug__endswith=OuterRef('slug'))
    .annotate(job_count=Count('id'))
    .values('job_count')[:1]
))

Прежде всего, спасибо Ювраджу за ваш ответ. Этот ответ основывается на вашем.

Оказывается, Django автоматически добавляет предложение GROUP BY, когда вы используете агрегатную функцию Count. Я не совсем понимаю, зачем он добавляет предложение GROUP BY и почему его удаление дает правильные результаты в моем случае, поэтому не буду рассуждать. Но вот ответ, также основанный на комментарии Сергея к этому ответу:

class NonAggregateCount(Count):
    # Gets rid of Django's automatic GROUP BY clause
    contains_aggregate = False

jobs = Job.objects.filter(locations__slug__endswith=OuterRef("slug"))

subquery = jobs.annotate(job_count=NonAggregateCount("id", distinct=True)).values("job_count")

Location.objects.annotate(number_of_jobs=Subquery(subquery))
Вернуться на верх