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.

Are you sure the 'unknown column' error refers to the `extra` rather than the `annotate`? Show the traceback, please. — Daniel Roseman, Feb 04 '11 at 14:33
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. — jnns, Mar 04 '11 at 10:43

score 6 · Accepted Answer · answered Feb 14 '12 at 21:23

6

it this what you want?

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

answered Feb 14 '12 at 21:23

yedpodtrzitko

9,035
2
40
42

score 0 · Answer 2 · edited May 13 '16 at 11:01

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.

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

2 Answers2

Linked