Using extra() on ValuesQuerySet in Django

Question

I'm trying to calculate a percentage with two values which are themselves aggregated. The SQL query that explains what I'm after is as follows:

SELECT (SUM(field_a) / SUM(field_b) * 100) AS percent
FROM myapp_mymodel 
GROUP BY id
ORDER BY id

I tried to use the following to construct a QuerySet, but unfortunately it doesn't contain the extra field:

MyModel.objects.values('id').annotate(
   sum_field_a=Sum('field_a'),
   sum_field_b=Sum('field_b')).extra(
      select={'percent': 'sum_field_a / sum_field_b * 100'})

What irritates me is that - according to the Django documentation - this seems to be the way to go:

When a values() clause is used to constrain the columns that are returned in the result set […] instead of returning an annotated result for each result in the original QuerySet, the original results are grouped according to the unique combinations of the fields specified in the values() clause. An annotation is then provided for each unique group; the annotation is computed over all members of the group.

Source: http://docs.djangoproject.com/en/dev/topics/db/aggregation/#values

If you use a values() clause after an extra() clause, any fields defined by a select argument in the extra() must be explicitly included in the values() clause. However, if the extra() clause is used after the values(), the fields added by the select will be included automatically.

Source: http://docs.djangoproject.com/en/dev/ref/models/querysets/#values

score 3 · Accepted Answer · answered Sep 05 '17 at 21:53

3

Aggregate expressions allow such expressions on Aggregate functions easily since Django 1.8 without the problematic 'extra()' method.

qs = (
    MyModel.objects.values('id')
    .annotate(percent=Sum('field__a') / Sum('field__b') * 100)
    .order_by('id')
)

>>> print(str(qs.query))
SELECT id, ((SUM(field_a) / SUM(field_b)) * 100) AS percent
FROM app_mymodel GROUP BY id ORDER BY id ASC

(The mentioned issue #15546 has been closed soon by a documentation note that extra() after values() will not work - commit a4a250a.)

answered Sep 05 '17 at 21:53

hynekcer

12,545
3
54
86

I have yet to test this because I have since not looked into this problem again. But as the aggregate expressions seem like the way to go on this issue I mark this answer as solution to my question. – jnns Sep 06 '17 at 08:27
I tried to check SQL also with a group on ForeignKey or a query set started from the reverse relation side. A simple example is for the purpose of answer the best. – hynekcer Sep 06 '17 at 10:27

score 0 · Answer 2 · answered Mar 11 '11 at 01:26

As you pointed out (#15546) there may be a bug in django.

But as a workaround, you can place the burden of the actual computation on python instead of the SQL database, by doing something like this:

[{'field_c': model['field_c'],
  'percent': m['sum_field_a'] * 100.0 / m['sum_field_b']}
 for model in MyModel.objects.values('field_c').annotate(
    sum_field_a=Sum('field_a'),
    sum_field_b=Sum('field_b')).order_by('field_c')]

As this solution forces you to loop through all the data, depending on what you want to do it may or may not be acceptable.

Thanks. I was hoping to get around doing it in python but this may be the way to go until there's further development on the bug. — jnns, Mar 13 '11 at 09:42

score 0 · Answer 3 · edited Jun 20 '20 at 09:12

0

If you use a values() clause after an extra() clause, any fields defined by a select argument in the extra() must be explicitly included in the values() clause.

Source: http://docs.djangoproject.com/en/dev/ref/models/querysets/#values

the 'percent' field added in the select can be explicitly added to the values clause and it should be added to the queryset.

MyModel.objects.annotate(
              sum_field_a=Sum('field_a'),
              sum_field_b=Sum('field_b')).extra(
              select={'percent': 'sum_field_a / sum_field_b * 100'}
         ).values('id', 'percent')

edited Jun 20 '20 at 09:12

Community

1
1

answered Mar 13 '11 at 14:32

Thomas

11,032
4
37
53

The results need to be grouped before annotation (hence `values()` before `annotate()`). Additionally I get *Unknown column field_a* when trying to select a field created via annotation. – jnns Mar 16 '11 at 00:19
have edited the snippet to add put 'id' in place of field_c try it now? – Thomas Mar 20 '11 at 12:23

Using extra() on ValuesQuerySet in Django

3 Answers3