Djano annotate with subtract operation returns None when the subtrahend is None

user_13319582 0
Viewed 51

My objective is to get the balance for a billing instance by subtracting the payments posted within the period. In my Billing model, I have a backlog field that contains the backlogs from previous period. The Billing model has m2m relationship with Payment model through PaidBills model.

In my queryset: '''

qs = Billing.objects.filter(
            bill_batch=prev_batch, balance__gt=0).annotate(
            payment=Sum('paidbills__amount_paid', filter=Q(
                paidbills__pay_period=batch.period))).order_by(
            'reservation__tenant__name', 'reservation__apt_unit').only(
                'id', 'bill_batch', 'reservation')
    
qs = qs.annotate(new_bal=F('backlog') - F('payment'))

'''

The result is correct when the expression F('payment') contains a value, but will give a result None when F('payment') returns None. I have tried to replace the expression F('payment') with any fixed value, say 5000, and it worked as expected.

How to go about this? (Django 3.2.7, Python 3.9.5)

django python
1 Answers

I haven't tested this, but Coalesce should do the job when the sum aggregation is None.

from django.db.models import Sum, Value

qs = Billing.objects.filter(bill_batch=prev_batch, balance__gt=0) \
    .annotate(
        payment=Coalesce(
            Sum('paidbills__amount_paid', filter=Q(paidbills__pay_period=batch.period)),
            Value(0)
        )
    ) \
    .order_by('reservation__tenant__name', 'reservation__apt_unit') \
    .only('id', 'bill_batch', 'reservation')
user_62456500
Related