Django annotate exclude with Case & When (Conditional Expression)

Question

I'm using Django 2.2

While making queryset, I want count of related model, based on few conditions like

    queryset = self.model.objects.filter(user=self.request.user).annotate(
        count_videos=Count('video'),
        count_completed=Count(
            Case(
                When(video__status__in=Video.STATUS_LIST_COMPLETED)
            )
        ),
        count_failed=Count(
            Case(
                When(video__status__in=Video.STATUS_LIST_FAILED)
            )
        ),
        count_pending=Count(
            Case(
                When(
                    video__status__not_in=Video.STATUS_LIST_PENDING_EXCLUDE
                )
            )
        )
    )

Here 3 counts are working, but in last count count_pending, I have to count against exlude(). i.e., count number of records excluding the passed list.

How can I use exclude with the above statement?

Answer 1

We can negate the value we pass to the filter= parameter [Django-doc]:

from django.db.models import Count, Q

queryset = self.model.objects.filter(user=self.request.user).annotate(
    count_videos=Count('video'),
    count_completed=Count(
        'video',
        filter=Q(video__status__in=STATUS_LIST_COMPLETED)
    ),
    count_failed=Count(
        'video',
        filter=Q(video__status__in=Video.STATUS_LIST_FAILED)
    ),
    count_pending=Count(
        'video',
        filter=~Q(video__status__in=Video.STATUS_LIST_PENDING_EXCLUDE)
    )
)

This will result in a query like:

SELECT model.*,
    COUNT(
        CASE WHEN NOT video.status IN STATUS_LIST_PENDING_EXCLUDE
                  AND video.status IS NOT NULL
        THEN video.id
        ELSE NULL END
    ) AS count_pending
FROM model
LEFT OUTER JOIN video ON model.id = video.model_id
GROUP BY model.id