id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
6811	Add functions, expressions and grouping to QuerySet.values()	Justin	Justin	"This proposal is for adding aggregation support to {{{QuerySet.values()}}}.

An E object is introduced that represents a query expression, along with several subclasses:
 * F: a field/column
 * Function: an SQL function
 * Aggregate: an SQL aggregation function

The basic syntax for values remains unchanged, field names or E objects are passed as position arguments. Grouping is implicit based on the types of arguments to {{{values()}}}. Non-aggregate fields and expressions are used in the GROUP BY clause. If no non-aggregate fields are passed, {{{values()}}} returns a single result. Functions objects have default names in the output based on the function name and field.

Examples:

Sum field1:
{{{
>>> Model.objects.values(Sum('field1'))
[ {'field1_sum': 1000} ]
}}}


Group by field1, sum field2:
{{{
>>> Model.objects.values('field1', Sum('field2'))
[ {'field1':10, 'field2_sum':1000 },
  {'field1':20, 'field2_sum':1234 } ]
}}}


Complex expression with a specified output alias:
{{{
>>> Model.objects.values('field1', E(Sum('field2') + Sum('field3'), alias='my_sum'))
[ {'field1':10, 'my_sum':1000 },
  {'field1':20, 'my_sum':1234 } ]
}}}


Use of F objects:
{{{
>>> Invoice.objects.values('id', E(F('subtotal') + F('tax'), alias='total'))
[ {'id':1, 'total':1000.00 },
  {'id':2, 'total':1234.00 } ]
}}}
Note that no grouping happens here since there are no aggregate functions.


Output aliases could possibly be specified via kwargs:
{{{
>>> Model.objects.values('field1', my_sum=Sum('field2') + Sum('field3'))
[ {'field1':10, 'my_sum':1000 },
  {'field1':20, 'my_sum':1234 } ]
}}}


It would be nice to support grouping by functions as well:
{{{
>>> Sale.objects.values(Month('date'), Sum('payment'))
[ {'date_month':1, 'payment_sum':1000.00 },
  {'date_month':2, 'payment_sum':1234.00 } ]
}}}
In this case Month is non-aggregating, so it's included in GROUP BY. Also, backends will have to be extended so that non-standard functions work.


User defined function using built-in functions:
{{{
>>> def my_func(value_col, quantity_col):
>>>    return Sum(value_col) / Sum(quantity_col)
>>> Product.objects.values('type', avg_price=my_func('price','quantity'))
[ {'type':'widget', 'avg_price':1.00 },
  {'type':'gadget', 'avg_price':2.00 } ]
}}}


Aggregation should be supported across relationships:
{{{
>>> Customer.objects.values('name', Sum('purchases__payment'))
[ {'name':'John', 'purchases__payment_sum':1000.00 },
  {'name':'Jane', 'purchases__payment_sum':1234.00 } ]
}}}


I think this proposal adds minimal new syntax and fits nicely with existing features like Q objects. It's possible that changing the behavior of {{{value()}}} to trigger grouping could be confusing.

This proposal so far only covers adding aggregation to {{{QuerySet.values()}}}. Another function (such as {{{annotate()}}} suggested by Russell) could be added to support aggregation with Models. It might be good to eventually support E objects in {{{QuerySet.filter()}}}. It might also be possible to use the E objects in Models via some sort of ExpressionField (but that's getting far ahead of myself).

Some of this already works in a haskish, fragile way (auto-grouping and simple aggregation functions, but not expressions), but I'm re-working my changes as per Malcolm's suggestions. I'll post a very preliminary patch for review when I get some of his suggestions finished. I'm not married to this proposal, and enjoy the work for itself, so if people think we should a different direction, that's fine.

So, thoughts, suggestions, test cases?



Most recent discussion:
http://groups.google.com/group/django-developers/browse_thread/thread/3d4039161f6d2f5e

Past discussions:
http://groups.google.com/group/django-developers/browse_thread/thread/f433edf7b0ebdbcb/9758e11744381e67[#5420]
http://groups.google.com/group/django-developers/browse_thread/thread/e53a62e1c73871b8/2f91fa217bc465bb?#2f91fa217bc465bb

Another proposal:
http://code.djangoproject.com/ticket/3566

"		closed	Database layer (models, ORM)	queryset-refactor		duplicate	aggregation grouping		Unreviewed	1	0	1	0	0	0