[Django]-Django annotate exclude with Case & When (Conditional Expression)

by

4👍

✅

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
👤willeM_ Van Onsem

0👍

Apologies for the reply to a super old question, but this one hits high on searches for this topic. I needed a very similar thing and wanted a count but had some odd conditions I couldn’t work out with ~Q and landed on an annotate that looked like the following. Posting here only for case for someone that happens to need something similar.

I required a count of Reviews completed, and those in progress, but if the review.status was UNTOUCHED it wasn’t to get counted in the ‘in progress’ or ‘completed’ bin. I used Case with the default value set to 1 for the "not" condition (not completed) then wrapped the Case in a Sum as shown. There were about 9 different status’s that indicated ‘in progress’ and I didn’t want to name them all.

.values(___bunch_of_group_by_fields_here___)\
.annotate(
    completed=Sum(Case(
        When(status__in=[Review.REVIEW_COMPLETE,
                         ], then=Value(1)),
        default=Value(0),
        output_field=IntegerField(),
    )),
    # essentially: ( not (review complete or untouched) )
    # gets all the status between untouched (default first step) and 
    # complete (final status in the workflow for a review) without having 
    # to specify all the in between statuses
    inprogress=Sum(Case(
        When(status__in=[Review.REVIEW_COMPLETE,
                         Review.UNTOUCHED
                         ], then=Value(0)),
        default=Value(1),
        output_field=IntegerField(),
    ))
👤AMG

Leave a comment