Dealing with None values in annotation and F expressions

Using Django Using the ORM
1

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!

2

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))

1 Like
3

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.