function sum(boolean) does not exist

Question

Switching from sqlite to Postgres and I get this error in django:

function sum(boolean) does not exist LINE 1: ..."."longitude", "pins_pin"."category_id", COALESCE(SUM("pins_...

HINT: No function matches the given name and argument types. You might need to add explicit type casts.

viewsets.py:

class PinViewSet(viewsets.ModelViewSet):
    queryset = pin.objects.annotate(
        num_of_upvotes=Coalesce(Sum('upvoters__upvote'), Value(0))
    )
    ...

models.py:

class Pin(models.Model):
    ....

class UpvoteStory(models.Model):
    pin = models.ForeignKey("pin", on_delete=models.CASCADE, null=True, related_name='upvoters')
    user = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.CASCADE, null=True)
    upvote = models.BooleanField(default=False)

num_of_upvotes should count the positive upvotes for each pin.

Answer 1

You may be able to do this with Sum, Case, When to count every time upvoters__upvote is True

pin.objects.annotate(
    num_of_upvotes=Sum(Case(
        When(upvoters__upvote=True, then=1),
        default=Value(0),
        output_field=IntegerField()
    ))
)