ProgrammingError when aggregating over an annotated & grouped Django ORM query
- by ento
I'm trying to construct a query to get the "average, maximum, minimum number of items purchased by a single user".
The data source is this simple sales record table:
class SalesRecord(models.Model):
id = models.IntegerField(primary_key=True)
user_id = models.IntegerField()
product_code = models.CharField()
price = models.IntegerField()
created_at = models.DateTimeField()
A new record is inserted into this table for every item purchased by a user.
Here's my attempt at building the query:
q = SalesRecord.objects.all()
q = q.values('user_id').annotate( # group by user and count the # of records
count=Count('id'), # (= # of items)
).order_by()
result = q.aggregate(Max('count'), Min('count'), Avg('count'))
When I try to execute the code, a ProgrammingError is raised at the last line:
(1064, "You have an error in your SQL
syntax; check the manual that
corresponds to your MySQL server
version for the right syntax to use
near 'FROM (SELECT
sales_records.user_id AS
user_id, COUNT(sales_records.`'
at line 1")
Django's error screen shows that the SQL is
SELECT FROM
(SELECT
`sales_records`.`player_id` AS `player_id`,
COUNT(`sales_records`.`id`) AS `count`
FROM `sales_records`
WHERE (`sales_records`.`created_at` >= %s AND `sales_records`.`created_at` <= %s )
GROUP BY `sales_records`.`player_id` ORDER BY NULL) subquery
It's not selecting anything! Can someone please show me the right way to do this?
Hacking Django
I've found that clearing the cache of selected fields in django.db.models.sql.BaseQuery.get_aggregation() seems to solve the problem. Though I'm not really sure this is a fix or a workaround.
@@ -327,10 +327,13 @@
# Remove any aggregates marked for reduction from the subquery
# and move them to the outer AggregateQuery.
+ self._aggregate_select_cache = None
+ self.aggregate_select_mask = None
for alias, aggregate in self.aggregate_select.items():
if aggregate.is_summary:
query.aggregate_select[alias] = aggregate
- del obj.aggregate_select[alias]
+ if alias in obj.aggregate_select:
+ del obj.aggregate_select[alias]
... yields result:
{'count__max': 267, 'count__avg': 26.2563, 'count__min': 1}
