Django 4 how to do annotation over boolean field queryset?-CodePudding

I found this discussion about annotation over querysets. I wonder, if that ever got implemented?

When I run the following annotation:

x = Isolate.objects.all().annotate(sum_shipped=Sum('shipped'))
x[0].sum_shipped
>>> True

shipped is a boolean field, so I would expect here the number of instances where shipped is set to True. Instead, I get True or 1. That is pretty inconvenient behaviour.

There is also this discussion on stackoverflow. However, this only covers django 1.

I would expect something like Sum([True, True, False]) -> 2. Instead, True is returned.

Seems this was not touched since then. Is that discussion in the second link still the state-of-the-art ???

Is there a better way to do statistics with the content of the database than this, now that Django is in version 4?

My current database is sqlite3 for development and testing, but will be Postgres in production. I would very much like to get database independent results.

SUM(bool_field) on Oracle will return 1 also, because bools in Oracle are just a bit (1 or 0). Postgres has a specific bool type, and you can't sum a True/False without an implicit conversion to int. This is why I say SUM(bool) is only accidentally supported for a subset of databases. The field type that is returned is based on the backend get_db_converters and the actual values that come back.

Example model

class Isolate(models.Model):
    isolate_nbr = models.CharField(max_length=10, primary_key=True)
    growing = models.BooleanField(default=True)
    shipped = models.BooleanField(default=True)
    ....

CodePudding user response：

I think you have a few options here, an annotate is not one of them at this time.

from django.db.models import Sum, Count

# Use aggregate with a filter
print(Isolate.objects.filter(shipped=True).aggregate(Sum('shipped')))

# Just filter then get the count of the queryset
print(Isolate.objects.filter(shipped=True).count())

# Just use aggregate without filter (this will only aggregate/Sum the True values)
print(Isolate.objects.aggregate(Sum('shipped')))

# WON'T WORK: annotate will only annotate on that row whatever that row's value is, not the aggregate across the table
print(Isolate.objects.annotate(sum_shipped==Count('shipped')).first().sum_shipped)