[SOLVED] Django annotate subquery's aggregation

Django annotate subquery's aggregation

So I have three models

class Advert(BaseModel):
    company = models.ForeignKey(Company, on_delete=CASCADE, related_name="adverts")

class Company(BaseModel):
    name = models.CharField(max_length=50)

class OrderRating(BaseModel):
    reported_company = models.ForeignKey(Company, on_delete=CASCADE, related_name='ratings')
    rating = models.DecimalField(
        max_digits=2,
        decimal_places=1,
        validators=[MinValueValidator(1.0), MaxValueValidator(5.0)],
        help_text='Rating from 1.0 to 5.0.'
    )

And I'm trying to get average of all order ratings related to the company and annotate that to the Advert model, when I do this:

 qs = Advert.objects.all().annotate(
        avg_rating=Subquery(
            OrderRating.objects.filter(
               reported_company=OuterRef('company')).aggregate(Avg("rating"))["rating__avg"]
            )
        )

I get back stating

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

Not sure where the problem is when I am calling the OuterRef inside a Subquery.

Solution

In my experience Subquery() is often a bit tricky and not well documented. And it tends to return the message you are receiving when there is some error in your code defining the Subquery (not a very helpful message indeed).

As far as I know aggregate does not work in Subequeries, you must use annotations instead. So this should work:

qs = Advert.objects.all().annotate(
        avg_rating=Subquery(
            OrderRating.objects.filter(
               reported_company=OuterRef('company')).values('reported_company').annotate(av=Avg('rating')).values('av')
            )
        )