Opened 10 years ago

Last modified 10 years ago

#23669 closed Bug

ProgrammingError when aggregating over an annotated & grouped ORM query — at Initial Version

Reported by: Gregory Goltsov Owned by: nobody
Component: Database layer (models, ORM) Version: 1.6
Severity: Normal Keywords: orm, annotate, aggregate, values, avg, max
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

I have a simplified Django model:

class Module(models.Model):
    score = models.IntegerField()
    user = models.ForeignKey(User)

I was trying to construct a query to get the average highest score per module per user. In SQL, the query would look something like:

SELECT
    m.id
    AVG(max_score)
FROM
    (
        SELECT
            m.id
            Max(m.score) AS max_score
        FROM
            module m
        GROUP BY
            m.user_id,
            m.id
    ) f
GROUP BY
    m.id

In Django ORM the query looks like:

Module.objects.values(     # GROUP BY module_id, user_id
    'id', 'user'
).annotate(                # adding MAX(score) to the SELECT clause
    max_score=Max('score')
).aggregate(               # actually getting the average
    Avg('max_score')
)

This case is even documented in the docs: ​https://docs.djangoproject.com/en/1.6/topics/db/aggregation/#aggregating-annotations

However, upon evaluating, I encounter the following ProgrammingError:

ProgrammingError: syntax error at or near "FROM"
LINE 1: SELECT  FROM (SELECT

The ORM query looks like a perfectly normal query, no hacking, so I was really surprised to see this behaviour. Furthermore, I'm not the only one having this issue, dating as far as 2010:

Change History (0)

Note: See TracTickets for help on using tickets.
Back to Top