Geek asks and answers

Django annotates on BooleanField

I have the following models:

class Foo(models.Model):
    pass

class Bar(models.Model):
    foo = models.ForeignKey(Foo)
    is_successful = models.BooleanField()

I would like to get all objects foowith annotation if all objects barassociated with the object foohave is_successfulasTrue

So far, my request is:

foos = Foo.objects.all().annotate(all_successful=Min('bar__is_successful'))

The idea of ​​annotation all_successfulis that if the minimum value of all lines is_successfulis 1, then they should all be True(it is assumed that 0is Falseand 1is True). Therefore, knowing that I can use a set of queries like this:

foo = foos[0]

if foo.all_successful == 1:
    print 'All bars are successful'
else:
    print 'Not all bars are successful'

This works fine in sqlite, however it doesn't work in PostgreSQL because PostgreSQL cannot aggregate MINin a boolean column. I think this works in sqlite because sqlite treats bools as integers, so it can execute an aggregate.

, PostgreSQL is_successful IntegerField?

Thanx

+5
3

, , . Django v1.8 case/when, ORM 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()
    ))

, - .

+10

DJANGO <= 1.7:, annotation , 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' })

Django 1.8 +, Dav3xor answer.

+3

inspired by https://docs.djangoproject.com/en/dev/topics/db/managers/ I suggest using a user manager for the 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_list

class 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())
+1
source

More articles:


All Articles