0

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?

Jasur
  • 99
  • 2
  • 6

1 Answers1

0

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"        
)
Brad Martsberger
  • 1,747
  • 13
  • 7
  • The task is to calculate the average for the last N months. The way that I need to solve this is: 1. Calculate average for each of N months. 2. Find average of those averages. Thanks for your response! – Jasur May 09 '23 at 17:45