Django combo queries with expressions

Question

I have an XYZ model, and I need to get the maximum value for fields a, b and expression x / y for a given set of queries.

It works great for fields. Something like:

>>> XYZ.all().aggregate(Max('a')) ... {'a__max': 10} 

However, I cannot find a way to do this for expressions. Try something like:

 >>> XYZ.all().aggregate(Max('x/y')) 

Gives an error:

 *** FieldError: Cannot resolve keyword 'x/y' into field. Choices are: a, b, x, y, id 

Try something like:

 >>> XYZ.all().aggregate(Max(F('x')/F('y'))) 

Gives an error:

 *** AttributeError: 'ExpressionNode' object has no attribute 'split' 

And even something like:

 XYZ.all().extra(select={'z':'x/y'}).aggregate(Max('z')) 

Also does not work and gives the same error as above:

 FieldError: Cannot resolve keyword 'z' into field. Choices are: a, b, x, y, id 

One hack I found is:

 XYZ.all().extra(select={'z':'MAX(x/y)'})[0].z 

Actually, this works because it generates the correct SQL, but it is confusing because I get the correct value in z atttribute, but not in the correct instance, the one with the maximum value.

Of course, I could also use raw queries or tricks with optional () and order_by (), but it really doesn't make sense to me that Django fully supports aggregated queries, but can't support expressions even with its own F. expressions.

Is there any way to do this?

Answer 1

In SQL, what you want is actually

 SELECT x/y, * FROM XYZ ORDER BY x/y DESC LIMIT 1; # Or more verbose version of the #1 SELECT x/y, id, a, b, x, y FROM XYZ GROUP BY x/y, id, a, b, x, y ORDER BY x/y DESC LIMIT 1; # Or SELECT * FROM XYZ WHERE x/y = (SELECT MAX(x/y) FROM XYZ) LIMIT 1; 

So in Django ORM:

 XYZ.objects.extra(select={'z':'x/y'}).order_by('-z')[0] # Or XYZ.objects.extra(select={'z':'x/y'}).annotate().order_by('-z')[0] # Or x/y=z => x=y*z XYZ.objects.filter(x=models.F('y') * XYZ.objects.extra(select={'z':'MAX(x/y)'})[0].z)[0] 

Version

 XYZ.all().extra(select={'z':'MAX(x/y)'})[0].z 

doesn't have the correct x, y and instances, because the MAX function is evaluated among all rows when there is no GROUP BY , so all instances of the returned QuerySet will have the same z value as MAX(x/y) .

Answer 2

Your example, which uses F() objects, should work fine with Django 1.8:

 XYZ.all().aggregate(Max(F('x')/F('y'))) 

Here's a snippet demonstrating aggregation with the Sum() and F() objects in the jj page of the Django aggregation cheat :

 Book.objects.all().aggregate(price_per_page=Sum(F('price')/F('pages')) 

Answer 3

For versions below 1.8, you can achieve the same (undocumented) method.

 Book.objects.all().aggregate(price_per_page=Sum('price_per_page', field='book_price/book_pages')) 

This works for Postgres, I don't know about MySQL.

Source: Django Aggregation: Summing Multiplication of Two Fields

Answer 4

I think you should get Maximum values ​​separately

 result = XYZ.aggregate(Max('x'), Max('y')) 

And then separate the two fields

 result['x__max'] \ result['y__max']