Django условный агрегат подзапроса

Question

Упрощенный пример структуры моей модели будет

class Corporation(models.Model):
    ...

class Division(models.Model):
    corporation = models.ForeignKey(Corporation)

class Department(models.Model):
    division = models.ForeignKey(Division)
    type = models.IntegerField()

Теперь я хочу отобразить таблицу, в которой отображаются корпорации, в столбце которых будет указано количество отделов определенного типа, например type=10, В настоящее время это реализуется с помощью помощника на Corporation модель, которая извлекает те, например,

class Corporation(models.Model):
    ...
    def get_departments_type_10(self):
        return (
            Department.objects
            .filter(division__corporation=self, type=10)
            .count()
        )

Проблема здесь в том, что это абсолютно убивает производительность из-за проблемы N+1.

Я пытался подойти к этой проблеме с select_related, prefetch_related, annotate, а также subquery, но я не смог получить результаты, которые мне нужны.

В идеале каждый Corporation в наборе запросов должен быть аннотирован целым type_10_count который отражает количество отделов этого типа.

Я уверен, что я мог бы сделать что-то с сырым SQL в .extra(), но документы объявляют, что это устарело (я на Django 1.11)

РЕДАКТИРОВАТЬ: Пример исходного решения SQL

corps = Corporation.objects.raw("""
SELECT
*,
(
    SELECT COUNT(*)
    FROM foo_division div ON div.corporation_id = c.id
    JOIN foo_department dept ON dept.division_id = div.id
    WHERE dept.type = 10
) as type_10_count
FROM foo_corporation c
""")

Answer 1

Я думаю с Subquery мы можем получить SQL, аналогичный тому, который вы предоставили, с этим кодом

# Get amount of departments with GROUP BY division__corporation [1]
# .order_by() will remove any ordering so we won't get additional GROUP BY columns [2]
departments = Department.objects.filter(type=10).values(
    'division__corporation'
).annotate(count=Count('id')).order_by()

# Attach departments as Subquery to Corporation by Corporation.id.
# Departments are already grouped by division__corporation
# so .values('count') will always return single row with single column - count [3]
departments_subquery = departments.filter(division__corporation=OuterRef('id'))
corporations = Corporation.objects.annotate(
    departments_of_type_10=Subquery(
        departments_subquery.values('count')
    )
)

Сгенерированный SQL

SELECT "corporation"."id", ... (other fields) ...,
  (
    SELECT COUNT("division"."id") AS "count"
    FROM "department"
    INNER JOIN "division" ON ("department"."division_id" = "division"."id") 
    WHERE (
      "department"."type" = 10 AND
      "division"."corporation_id" = ("corporation"."id")
    ) GROUP BY "division"."corporation_id"
  ) AS "departments_of_type_10"
FROM "corporation"

Некоторые проблемы здесь заключаются в том, что подзапрос может быть медленным с большими таблицами. Тем не менее, оптимизаторы запросов к базе данных могут быть достаточно умными для продвижения подзапроса в OUTER JOIN, по крайней мере, я слышал, что PostgreSQL делает это.

1. GROUP BY с использованием.values ​​и.annotate

2. проблемы order_by()

3. Подзапрос

Answer 2

Вы должны быть в состоянии сделать это с Case() выражение для запроса количества отделов, которые имеют тип, который вы ищете:

from django.db.models import Case, IntegerField, Sum, When, Value

Corporation.objects.annotate(
    type_10_count=Sum(
        Case(
            When(division__department__type=10, then=Value(1)),
            default=Value(0),
            output_field=IntegerField()
        )
    )
)

Answer 3

Мне нравится такой способ:

      departments = Department.objects.filter(
    type=10,
    division__corporation=OuterRef('id')
).annotate(
    count=Func('id', 'Count')
).values('count').order_by()

corporations = Corporation.objects.annotate(
    departments_of_type_10=Subquery(depatments)
)

Более подробную информацию об этом методе вы можете увидеть в этом ответе: /questions/8884513/django-111-annotirovanie-agregata-podzaprosa/58949886#58949886