BIG FAT DISCLAIMER I'm not sure this has anything to do with your issue, but it's easy to miss when reading the docs.
https://docs.djangoproject.com/en/dev/topics/db/aggregation/#interaction-with-default-ordering-or-order-by If your model has an 'ordering' attribute defined in Meta, make sure you add a "blank" .order_by() to your query. Sincerely, André Terra On Tue, Jun 21, 2011 at 10:58 AM, Michał Sawicz <mic...@sawicz.net> wrote: > Hi all, > > I'm not sure it's actually supposed to work, but looks like a common > enough approach that it should. > > Say you have an aggregate query: > > Model.objects.values('field').annotate(Sum('count')) > that results in an aggregate query that follows: > > SELECT "models_model"."field", SUM("models_model"."count") AS > > "count__sum" FROM "models_model" GROUP BY "models_model"."field" > > And that's all fine, but if you want to get additional fields: > > Model.objects.values('field').annotate(Sum('count')).values('field', > > 'field2', 'count_sum') > the additional fields get added to the GROUP BY clause: > > SELECT "models_model"."field", "models_model"."field2", > > SUM("models_model"."count") AS "count__sum" FROM "models_model" GROUP > > BY "models_model"."field", "models_model"."field", > > "models_model"."field2" > > That doesn't, in itself, pose any problems. "field" is added twice, but > that's probably optimized down within SQL itself. I'm only afraid that > grouping by "field" and "field2" is slower than grouping by "field" is. > Maybe my approach here is wrong? What would you do to group by "field", > annotate with SUM("count") and retrieve "field2", too? > > Now, if you want to leverage some more advanced queries: > > Model.objects.values('field').annotate(Sum('count')).values('field', > > 'field2', 'count_sum').extra({'value': 'SUM("models_model"."field3" * > > "models_model"."count")'}) > that breaks with: > > DatabaseError: aggregate functions are not allowed in the GROUP BY > > clause > because the resulting query looks like this: > > SELECT (SUM("models_model"."field3" * "models_model"."count")) AS > > "value", "models_model"."field", "models_model"."field2", > > SUM("models_model"."count") AS "count__sum" FROM "models_model" GROUP > > BY "models_model"."field", "models_model"."field", > > "models_model"."field2", (SUM("models_model"."field3" * > > "models_model"."count")) > As you can see, the field definitions from extra() gets added to the > GROUP BY clause, and fails. > If I drop the last added GROUP BY element (or even all of them, bar > "field"), the query is working fine, and the results are as expected. > > Is this an eccentric enough usecase that I should forget about that? Or > is my approach simply wrong? > > Best regards, > -- > Michał (Saviq) Sawicz <mic...@sawicz.net> > -- You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com. To unsubscribe from this group, send email to django-users+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/django-users?hl=en.
