Django 用 Case 和 When(条件表达式)注释排除

Question

我正在使用Django 2.2

在制作查询集时，我想要根据一些条件(例如

)计算相关模型的数量

    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
                )
            )
        )
    )

这里有 3 个计数正在工作，但在最后一个计数 count_pending 中，我必须对 exlude() 进行计数。即，计算不包括传递列表的记录数。

如何在上述语句中使用exclude？

Answer 1

我们可以否定传递给 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',
        <b>filter=~Q(video__status__in=Video.STATUS_LIST_PENDING_EXCLUDE)</b>
    )
)

这将导致如下查询:

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