Django ORM: This queryset contains a reference to an outer query and may only be used in a subquery

Question

In the queryset, I want to get the average of what subquery returns and then group by 'store_id' and 'avg_sales'. However, when I used the following queries:

subquery = StoreStatistics.objects.filter(
    store=OuterRef("store_id"),
    products__in=products,   # list of ids
    created_date__gte='2023-01-01',
).annotate(
    month=TruncMonth("created_date")
).values(
    "month", "store"
).annotate(
    avg_sales_per_month=Avg("quantity")
)

queryset = Company.objects.filter(
    company_id=company_id
).annotate(
    avg_sales=Subquery(subquery.aggregate(Avg("avg_sales_per_month")))
).values(
    "store_id", "avg_sales"        
)

I got the following error:

This queryset contains a reference to an outer query and may only be used in a subquery.

Can anyone tell where am I making a mistake?

Answer 1

When you call .aggregate on a queryset, this causes the queryset to be evaluated (the SQL is generated, sent to the database, and the results are returned and cached), but you must pass an unevaluated queryset to Subquery. The subquery cannot be evaluated until it is in the context of the outer query because it contains a reference to the outer query.

Secondly, the subquery you have written on StoreStatistics will return multiple results for a single Company, one for each month of data. A Subquery has to return a single result.

If the reason you are calling .aggregate(Avg("avg_sales_per_month")) is to get a single result averaged over all the months instead of a result for each month, you may want to not group by month in the subquery to begin with. (Though it's not clear what you are actually trying to calculate.)

This should run without the error, but I'm not sure if it's calculating your desired result.

subquery = StoreStatistics.objects.filter(
    store=OuterRef("store_id"),
    products__in=products,   # list of ids
    created_date__gte='2023-01-01',
).values(
    'store_id'
).annotate(
    avg_sales=Avg("quantity")
).values(
    'avg_sales_per_month'
)

queryset = Company.objects.filter(
    company_id=company_id
).annotate(
    avg_sales=Subquery(subquery)
).values(
    "store_id", "avg_sales"        
)