How to perfrom an aggregate query with condicional filter in django

Asked Jul 25 '19 at 20:51

Active Jul 25 '19 at 21:08

Viewed 61 times

I have this data on the model:

[
  {"Field": "value1", "Flag": true},
  {"Field": "value2", "Flag": false},
  {"Field": "value1", "Flag": true},
  {"Field": "value2", "Flag": true},
  {"Field": "value1", "Flag": false},
  {"Field": "value2", "Flag": false},
  {"Field": "value1", "Flag": false}
]

I try to aggregate the data with this query when the flag is True:

model.objects.values('Field').order_by('Field').annotate(total=Count('Field'), Flag_field=Count('Flag', filter=Q(Flag=True)))

And I expect this output in the result of the query:

[
   {"Field": "value1", "total": 4, "Flag": 2},
   {"Field": "value2", "total": 3, "Flag": 1}
]

try with conditional aggregation but display this error:

SQLDecodeError

edited Jul 25 '19 at 21:08

asked Jul 25 '19 at 20:51

Enrique Alonso

What Django version do you use? Note that usually you can not annotate with the *same* name as the original field. – Willem Van Onsem Jul 25 '19 at 20:52
the version of Django is 2.2.1, yes is true but in the real query not use the same name this is an example! – Enrique Alonso Jul 25 '19 at 20:57
What if you use `flags=Sum('Flag')` instead? – Willem Van Onsem Jul 25 '19 at 20:58
Ummm I try with Sum but the flag field is a booblean fiield, Sum is for integer or float field I think!, Now I try with Sum and have the same error! – Enrique Alonso Jul 25 '19 at 21:01
no `True` and `False` are just aliasses for `1` and `0`. – Willem Van Onsem Jul 25 '19 at 21:03
Same error with Sum, meaby other sintax error? – Enrique Alonso Jul 25 '19 at 21:06
can you show what query is generated with `print(qs.query)` (with `qs` the queryset)? – Willem Van Onsem Jul 25 '19 at 21:15
this is the raw query: SELECT "model"."Field", COUNT("model"."field") AS "total", SUM(CASE WHEN "model"."flag" = True THEN "model"."flag" ELSE NULL END) AS "bot2" FROM "model" GROUP BY "model"."field" ORDER BY "model"."field" ASC – Enrique Alonso Jul 25 '19 at 21:21
In the SQL output you posted, sometimes "Field" has an uppercase "F", sometimes it has a lowercase "f". That seems odd. – Brad Martsberger Jul 26 '19 at 05:28
`True` and `False` are not always aliases for `1` and `0`. In MySQL they are, in Postgres they are not. – Brad Martsberger Jul 26 '19 at 05:29

How to perfrom an aggregate query with condicional filter in django

0 Answers0