Aggregate of aggregates in outerquery

Using Django Using the ORM
#1

Hi,

Whenever I want to aggregate an aggregate field in an Outer query, I got this exception, even if I’m assigning the queryset in a variable.

>>> qs = QuestionAnswer.objects.filter(invitation=OuterRef("pk")).annotate(correct_choices=Count("selected_choices", filter=Q(selected_choices__choice__is_correct=True))).aggregate(score=Sum(F('correct_choices')))
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/home/vincent/.cache/pypoetry/virtualenvs/qaas-o_KvCZGd-py3.9/lib/python3.9/site-packages/django/db/models/query.py", line 605, in aggregate
    return query.get_aggregation(self.db, kwargs)
  File "/home/vincent/.cache/pypoetry/virtualenvs/qaas-o_KvCZGd-py3.9/lib/python3.9/site-packages/django/db/models/sql/query.py", line 544, in get_aggregation
    result = compiler.execute_sql(SINGLE)
  File "/home/vincent/.cache/pypoetry/virtualenvs/qaas-o_KvCZGd-py3.9/lib/python3.9/site-packages/django/db/models/sql/compiler.py", line 1382, in execute_sql
    sql, params = self.as_sql()
  File "/home/vincent/.cache/pypoetry/virtualenvs/qaas-o_KvCZGd-py3.9/lib/python3.9/site-packages/django/db/models/sql/compiler.py", line 1913, in as_sql
    inner_query_sql, inner_query_params = self.query.inner_query.get_compiler(
  File "/home/vincent/.cache/pypoetry/virtualenvs/qaas-o_KvCZGd-py3.9/lib/python3.9/site-packages/django/db/models/sql/compiler.py", line 615, in as_sql
    self.compile(self.where) if self.where is not None else ("", [])
  File "/home/vincent/.cache/pypoetry/virtualenvs/qaas-o_KvCZGd-py3.9/lib/python3.9/site-packages/django/db/models/sql/compiler.py", line 503, in compile
    sql, params = node.as_sql(self, self.connection)
  File "/home/vincent/.cache/pypoetry/virtualenvs/qaas-o_KvCZGd-py3.9/lib/python3.9/site-packages/django/db/models/sql/where.py", line 112, in as_sql
    sql, params = compiler.compile(child)
  File "/home/vincent/.cache/pypoetry/virtualenvs/qaas-o_KvCZGd-py3.9/lib/python3.9/site-packages/django/db/models/sql/compiler.py", line 503, in compile
    sql, params = node.as_sql(self, self.connection)
  File "/home/vincent/.cache/pypoetry/virtualenvs/qaas-o_KvCZGd-py3.9/lib/python3.9/site-packages/django/db/models/fields/related_lookups.py", line 185, in as_sql
    return super().as_sql(compiler, connection)
  File "/home/vincent/.cache/pypoetry/virtualenvs/qaas-o_KvCZGd-py3.9/lib/python3.9/site-packages/django/db/models/lookups.py", line 357, in as_sql
    return super().as_sql(compiler, connection)
  File "/home/vincent/.cache/pypoetry/virtualenvs/qaas-o_KvCZGd-py3.9/lib/python3.9/site-packages/django/db/models/lookups.py", line 225, in as_sql
    rhs_sql, rhs_params = self.process_rhs(compiler, connection)
  File "/home/vincent/.cache/pypoetry/virtualenvs/qaas-o_KvCZGd-py3.9/lib/python3.9/site-packages/django/db/models/lookups.py", line 118, in process_rhs
    sql, params = compiler.compile(value)
  File "/home/vincent/.cache/pypoetry/virtualenvs/qaas-o_KvCZGd-py3.9/lib/python3.9/site-packages/django/db/models/sql/compiler.py", line 503, in compile
    sql, params = node.as_sql(self, self.connection)
  File "/home/vincent/.cache/pypoetry/virtualenvs/qaas-o_KvCZGd-py3.9/lib/python3.9/site-packages/django/db/models/expressions.py", line 837, in as_sql
    raise ValueError(
ValueError: This queryset contains a reference to an outer query and may only be used in a subquery.

If I remove the aggregate, the assignement is working properly:

>>> qs = QuestionAnswer.objects.filter(invitation=OuterRef("pk")).annotate(correct_choices=Count("selected_choices", filter=Q(selected_choices__choice__is_correct=True)))
>>>
#2

This isn’t valid as-is.

This only defines a queryset, it has not attempted to resolve it.

What happens if you then resolve the queryset? (e.g. print(qs))

I would expect that to generate the same error.

#3

Yes I agree, we should first set this query as a variable without evaluating it and then use it a Subquery as documented here: Query Expressions | Django documentation | Django

The problem I’m facing with the query with the aggregate is that I cannot even store it somewhere, an error is raised and I cannot make it a Subquery.

For some reason, it seems that having an aggregate in the query forces its evaluation.

#4

That’s correct, because an aggregation returns a dict and not a queryset. So it resolves the queryset to create the dict.

#5

Understood, thank you!