Django annotate on boolean Field

Question

class Forecast(Model):
    id = UUID()
    type = StringField()
    approved = BooleanField()

I want to group on the field type by applying 'logical and' on approved field. Suppose the annotated field is all_approved. all_approved should be True if all the items with that type is True, and false if atleast one is False.

So finally in my queryset i want to have two fields type, all_approved.

How can i achieve this?

I tried something based on this answer, but couldn't get anything.

EDIT:

when i tried whats given in that answer, its not doing 'logical and'. Instead for each type it just give two items, one with all_approved as True, another with all_approved as False. I want a single item for each type.

Also i don't understand why that answer should work. Where is it specified if while grouping it should do 'logical and' or 'logical or'.

Answer 1

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

Answer 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.