Geek Answers Handbook

Using Extra () in ValuesQuerySet in Django

I am trying to calculate a percentage with two values ​​that are themselves aggregated. The SQL query explaining what I need is the following:

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

I tried using the following to build a QuerySet, but unfortunately it does not contain an additional 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'})

It annoys me that - according to the Django documentation - this is apparently the way to go:

When the values ​​() condition is used to restrict 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 combination of fields specified in the values ​​() clause . An annotation is provided for each unique group; abstract designed for all members of the group.

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

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

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

+6
sql django django-queryset extra
source share
4 answers

Aggregate expressions make it easy to express such expressions in Aggregate functions with 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

(Mentioned issue No. 15546 was soon closed by writing documentation that extra () after values ​​() would not work - commit a4a250a .)

+2
source share

Since his mistake is impossible ... Why can't you just complete the estimated amount of the message? Do you have all the necessary information?

0
source share

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

But as a workaround, you can put the load on the actual calculation in 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')]

Since this solution forces you to iterate over all the data, depending on what you want to do, it may or may not be acceptable.

0
source share

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

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

the "percent" field added to select can be explicitly added to the values ​​clause and must be added to the query.

 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')
0
source share

More articles:


All Articles