Django annotate subquery's aggregation

Question

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.

score 2 · Accepted Answer · answered Feb 07 '20 at 17:23

2

According to my experience Subqueries are often a bit tricky and not well documented. And they tend 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')
            )
        )

answered Feb 07 '20 at 17:23

Chris

2,162
1
6
17

this sadly always returns an empty queryset – Just-Drue Feb 09 '20 at 10:59
Are you saying ```qs``` or ```avg_rating``` is empty? – Chris Feb 09 '20 at 11:58
Actually just got it working, thanks! The problem was very interesting, under `OrderRating` meta I also had `ordering = ['created_at']` which translated the sql query not into just `ORDER BY reported_company` but to `ORDER BY reported_company, created_at`, and this was messing up everything, removing ordering fixed and now gives the correct output. – Just-Drue Feb 09 '20 at 12:11
Great you got it solved. Although...the queries in this post do not use any ```order_by```? – Chris Feb 09 '20 at 12:30
yea I had a default ordering in the model's meta, anyway this was the problem if you're interested to read about it: https://docs.djangoproject.com/en/3.0/topics/db/aggregation/#interaction-with-default-ordering-or-order-by – Just-Drue Feb 09 '20 at 12:39

Django annotate subquery's aggregation

1 Answers1