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.

Rob Bednark · Accepted Answer · 2024-10-23 19:02:45Z

4

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

edited Oct 23, 2024 at 19:02

Rob Bednark

28.8k28 gold badges90 silver badges131 bronze badges

answered Feb 7, 2020 at 17:23

Chris

2,2021 gold badge8 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Just-Drue Over a year ago

this sadly always returns an empty queryset

Chris Over a year ago

Are you saying qs or avg_rating is empty?

Just-Drue Over a year ago

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.

Chris Over a year ago

Great you got it solved. Although...the queries in this post do not use any order_by?

Just-Drue Over a year ago

yea I had a default ordering in the model's meta, anyway this was the problem if you're interested to read about it: docs.djangoproject.com/en/3.0/topics/db/aggregation/…

Collectives™ on Stack Overflow

Django annotate subquery's aggregation

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related