Django annotate и count: как фильтровать те, которые нужно включить в count

Учитывая набор запросов, я добавляю счет связанных объектов (ModelA) со следующим:

qs = User.objets.all()
qs.annotate(modela__count=models.Count('modela'))

Однако есть ли способ подсчитать ModelA, который соответствует только критериям? Например, подсчитайте ModelA, где deleted_at имеет значение null?

Я пробовал два решения, которые не работают должным образом.

1) По предложению @knbk используйте фильтр, прежде чем комментировать.

qs = User.objects.all().filter(modela__deleted_at__isnull=True).annotate(modela__count=models.Count('modela', distinct=True))

Вот упрощенная версия запроса, созданного django:

SELECT COUNT(DISTINCT "modela"."id") AS "modela__count", "users".*
FROM "users"
LEFT OUTER JOIN "modela" ON ( "users"."id" = "modela"."user_id" ) 
WHERE "modela"."deleted_at" IS NULL 
GROUP BY "users"."id"

Проблема возникает из предложения WHERE. В самом деле, есть ЛЕВОЕ ПРИСОЕДИНЕНИЕ, но более поздние условия WHERE заставили его быть простым JOIN. Мне нужно вывести условия в предложение JOIN, чтобы оно работало по назначению.

Итак, вместо

LEFT OUTER JOIN "modela" ON ( "users"."id" = "modela"."user_id" ) 
WHERE "modela"."deleted_at" IS NULL

Мне нужно следующее, которое работает, когда я выполняю его непосредственно в простом SQL.

LEFT OUTER JOIN "modela" ON ( "users"."id" = "modela"."user_id" ) 
AND "modela"."deleted_at" IS NULL

Как я могу изменить набор запросов, чтобы получить это, не выполняя необработанный запрос?

2) Как и другие, я мог бы использовать условную агрегацию.

Я попробовал следующее:

qs = User.objects.all().annotate(modela__count=models.Count(Case(When(modela__deleted_at__isnull=True, then=1))))

который превращается в следующий SQL-запрос:

SELECT COUNT(CASE WHEN "modela"."deleted_at" IS NULL THEN 1 ELSE NULL END) AS "modela__count", "users".*
FROM "users" LEFT OUTER JOIN "modela" ON ( "users"."id" = "modela"."user_id" )
GROUP BY "users"."id"

Таким образом, я получаю всех пользователей (поэтому LEFT JOIN работает правильно), но я получаю "1" (вместо 0) для modela__count для всех пользователей, у которых вообще нет модели ModelA. Почему я получаю 1, а не 0, если нечего подсчитывать? Как это можно изменить?

Ответ 1

В a LEFT JOIN каждое поле modela может быть NULL из-за отсутствия соответствующей строки. Так

modela.deleted_at IS NULL

... применим не только для совпадающих строк, но и для тех users, у которых нет соответствующих строк modela.

Я думаю, что правильный SQL должен быть:

SELECT COUNT(
    CASE
      WHEN
        `modela`.`user_id` IS NOT NULL  -- Make sure modela rows exist
        AND `modela`.`deleted_at` IS NULL
        THEN 1
      ELSE NULL
    END
  ) AS `modela__count`,
  `users`.*
FROM `users`
LEFT OUTER JOIN `modela`
  ON ( `users`.`id` = `modela`.`user_id` )
GROUP BY `users`.`id`

В Django 1.8 это должно быть:

from django.db import models
qs = User.objects.all().annotate(
    modela_count=models.Count(
        models.Case(
            models.When(
                modela__user_id__isnull=False,
                modela__deleted_at__isnull=True,
                then=1,
            )
        )
    )
)

Примечание

@YAmikep обнаружил, что ошибка в Django 1.8.0 делает сгенерированный SQL INNER JOIN вместо LEFT JOIN, поэтому вы потеряете строки без соответствующего отношения внешнего ключа. Используйте версию Django 1.8.2 или выше, чтобы исправить это.

Ответ 2

В Django 1.8 я считаю, что этого можно достичь с помощью условной агрегации. Однако для предыдущих версий я бы сделал это с помощью .extra

ModelA.objects.extra(select={
    'account_count': 'SELECT COUNT(*) FROM account WHERE modela.account_id = account.id AND account.some_prop IS NOT NULL'
})

Ответ 3

Вы можете просто фильтровать, прежде чем комментировать:

from django.db.models import Q, Count

qs = ModelA.objects.filter(account__prop1__isnull=False).annotate(account_count=Count('account'))