Dealing with None values in annotation and F expressions

Hi everyone.

Just got to rewrite some logic I made sometime ago :upside_down_face:
I’m inneed of making some calculations for a Django model so I wanted to use the full power of the ORM, to make it much more efficient.

Problem

If total1 or total2 resolve to None => total3 will be None.
I need it to be a number.

model_queryset
    .values("A", "B")
    .annotate(total1=Sum("C"))
    .annotate(total2=Sum("D"))
     # If total1 or total2 == None, total3 == None
    .annotate(total3=F("total1") + F("total2")) 

Ideas

  1. Checked Sum docs, saw it has a default argument but only in Django >4.0.
    I’m using Django 3.2

  2. Tried wrapping total3 with Sum

FieldError("Cannot compute Sum('<CombinedExpression: F() - F() ... >'): '<CombinedExpression: F(total_solicitados) - F() ... >' is an aggregate")
  1. Tried with ExpressionWrapper, defining an output_field=IntegerField.
    Same result as original problem.
.annotate(total3=ExpressionWrapper(
    F("total1") + F("total2"),
    output_field=IntegerField()
))

Maybe I’m overcomplicating the annotation but it seems really nice to be able to do this in just one query.
It’s also way less code than before, improving legibility.

Thanks!

I’m not sure I’m following what you’re asking for here - but if my guess is correct, what you’re really looking for is a way for total1 to be an integer even if Sum("C") is null. (And the same for total2.)

In that case, I think Coalesce would work for you - something like .annotate(total1=Coalesce(Sum("C"), 0))

Yeah, I just clarified the problem.

I knew there was some ORM/SQL magic in the docs. Thanks Ken!

PD: BTW, Sum supports a default value in 4.0, so there’s no need to do this.