Using .extra() on fields created by .annotate() in

Question

I want to retrieve a sum of two fields (which are aggregations themselves) for each object in a table.

The following may describe a bit better what I'm after but results in an Unknown column in field list-Error:

items = MyModel.objects.annotate(
                field1=Sum("relatedModel__someField"),
                field2=Sum("relatedModel__someField")).extra(
                        select={"sum_field1_field2": "field1 + field2"})

I also tried using F() for the field lookups but that gives me an invalid sql statement.

Any ideas on how to solve this are much appreciated.

Answer 1

it this what you want?

items = MyModel.objects.extra(
    select = {'sum_field1_field2': 'SUM(relatedModel__someField) + SUM(relatedModel__someField)'},
)

Answer 2

To make it work for many to many or for many to one (reverse) relations, you may use the following:

items = MyModel.objects.extra(
    select = {'sum_field1_field2': 'SUM("relatedModel"."someField") + SUM("relatedModel"."someField")'},
      )

But this will break also if you need another annotate, like for a count, because extra will add the statement to the GROUP BY clause, whereas aggregate functions are not allowed in there.