CODEWITHSUNDEEP
pythondjangodjango-queryset
Adam Lombard
Adam Lombard

Reputation: 334

Django: Average of Subqueries as Multiple Annotations on a Query Result

I have three Django models:

class Review(models.Model):
  rating = models.FloatField()
  reviewer = models.ForeignKey('Reviewer')
  movie = models.ForeignKey('Movie')

class Movie(models.Model):
  release_date = models.DateTimeField(auto_now_add=True)

class Reviewer(models.Model):
  ...

I would like to write a query that returns the following for each reviewer:

The result would be formatted:

<Queryset [{'id': 1, 'average_5': 4.7, 'average_10': 4.3, 'most_recent_bad_review': '2018-07-27'}, ...]>

I'm familiar with using .annotate(Avg(...)), but I can't figure out how to write a query that averages just a subset of the potential values. Similarly, I'm lost on how to annotate a query for the most recent <3 rating.

Upvotes: 0

Views: 317

Answers (1)

Işık Kaplan
Işık Kaplan

Reputation: 3002

All of those are basically just some if statements in python code and when statements in your database assuming it is SQL-like, so, you can just use django's built-in Case and When functions, you'd probably combine them with Avg in your case and would need a new annotation field for every when, so your queryset would look roughly like

Model.objects.annotate(
    average_5=Avg(Case(When(then=...), When(then=...)),
    average_10=Avg(Case(When(then=...), When(then=...)),
)

with appropriate conditions inside when and appropriate then values.

Upvotes: 1

Related Questions