[Django]-Django annotate on boolean Field

7👍

✅

other solution: you can try compare all approved with approved=True

from django.db.models import Count, Case, When, BooleanField

Forecast.objects.values(
    'type'
).annotate(
    cnt_app=Count(Case(When(approved=True, then=1)))
).annotate(
    all_approved=Case(
        When(cnt_app=Count('approved'), then=True),
        default=False,
        output_field=BooleanField()
   )
).values('type', 'all_approved')

where

Count(Case(When(approved=True, then=1))) gives us count of the approved with status True for the type,

Count('approved') gives us total count of the all for the type,

and if the values is equal then all_approved is True other way False

2👍

You can use a subquery to flip all_approved to False for types that have at least one False value:

from django.db.models import BooleanField
from django.db.models.expressions import Case, When

(Forecast.objects
    .annotate(all_approved=Case(
        When(type__in=Forecast.objects.filter(approved=False).values('type'), 
             then=False),
        default=True,
        output_field=BooleanField()
    ))
    .values('type', 'all_approved')
    .distinct()
)

The question you’ve linked is a bit different because it relates to a one-to-many relationship between two models that are joined automatically by Django.

Here you have just one model, which you’d have to join with itself to use the same solution. Since Django only supports joins defined by relationships, you need the subquery as a workaround.

Leave a comment