Django annotate on BooleanField

I know this is an old question, but I ran up against this recently. Django v1.8 now has built in support for case/when, so you can use the ORM instead of hacking around with custom SQL.

https://docs.djangoproject.com/en/1.8/ref/models/conditional-expressions/#case

Foo.objects.annotate(    all_successful=Case(        When(bar__is_successful=False, then=False),        When(bar__is_successful=True, then=True),        default=False,        output_field=BooleanField()    ))

I haven't tried this out, but something similar worked for me on a recent project.

FOR DJANGO <= 1.7: to get an annotation I think you can simply use Extra

foos = Foo.objects.extra(select={'all_successful': 'CASE WHEN COUNT(b.foo) > 0 THEN 0 ELSE 1 END FROM yourapp_bar as b WHERE b.is_successful = false and b.foo = yourapp_foo.id' })

if your system is running Django 1.8+ please follow Dav3xor answer.

being inspired by https://docs.djangoproject.com/en/dev/topics/db/managers/ I suggest to use a custom manager for Bar class instead of annotation

class BarManager(models.Manager):    def get_all_successful_foos_ids(self):        from django.db import connection        cursor = connection.cursor()        cursor.execute("""            SELECT foo, COUNT(*)            FROM yourapp_bar            GROUP BY 1            WHERE is_successful = true""")  # <-- you have to write the correct table name here        result_list = []        for row in cursor.fetchall():            if row[1] > 0:                result_list.append(row[0])        return result_listclass Bar(models.Model):    foo = models.ForeignKey(Foo)    is_successful = models.BooleanField()    objects = BarManager()  # here I'm changing the default manager

then, in your code:

foos = foo.objects.filter(id__in=Bar.objects.get_all_successful_foos_ids())