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

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.

Tricky

Are you sure the 'unknown column' error refers to the extra rather than the annotate? Show the traceback, please. – Rutter

The query runs fine when only annotate is used: both sums are added as fields to the returned objects. Only when extra is used, the exception is raised. – Tricky

+1 : having the same problem, still didnt find a solution! – Herschel

Answer 1

it this what you want?

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

Catherin

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.

Hibiscus