Django Subquery Sum with no results returns None instead of 0-CodePudding

I have a Django Subquery which returns a Sum. If the subquery finds at least one result, it works fine. But, if the subquery finds no records, it returns None which then causes any other calculations using this result (I use it later in my query in an F expression) to also result in None.

I am trying to Sum all non-null 'consumed' values - sometimes, they are all null and therefore there are no rows upon which to sum. I would like this to result in a 0 instead of None

...
annotate(tapx=Subquery(InvTrx.objects.filter(job=OuterRef('job')).\
                                                filter(consumed__isnull=False).\
                                                filter(inventory__inv_type='APX').\
                                                values('job__job').\
                                                annotate(tot_cons=Sum('consumed', default=0)).\
                                                values('tot_cons')
                                  )).\
...

I've tried Coalesce with and without Value(0)

annotate(tot_cons=Coalesce(Sum('consumed', default=0)), 0).\
annotate(tot_cons=Coalesce(Sum('consumed', default=0)), Value(0)).\

the value of tapx (which I reuse in F expressions in another part of the query) = None if no rows are returned. If at least one row is returned, this works fine. If no rows are returned, I would like the value of tapx to be 0 instead of None so that the value of fg_total in the following annotation results in a number and not None:

annotate(fg_total=F('fg')   F('tapx'))

Doing this outside a subquery, I have used "or 0" to force the value to 0 if the result is None - is there a way to do this inside a subquery?

I also tried, when I do use the result of the Subquery in an F expression, to use a conditional like so:

annotate(fg_total=F('fg')   Case(When(~Exists('tapx'), then=F('tapx')),default=Value(0)))

but this results in an error - 'str' object has no attribute 'order_by' (I am not sure what this means in this context since I am not using 'order_by' anywhere?)

I am using Django 3.0. I'm stuck.

CodePudding user response：

You should not Coalesce the .annotate(…) in the Subquery, but the result of the Subquery, so:

.annotate(tapx=Coalesce(
    Subquery(InvTrx.objects.filter(
        job=OuterRef('job'), consumed__isnull=False, inventory__inv_type='APX'
    ).values('job_id').annotate(tot_cons=Sum('consumed')).values('tot_cons')),
    Value(0))
)