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)))
>>>