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>
signature.asc
Description: This is a digitally signed message part
