How to add a second annotation to an already annotated queryset with django models

user_11835455 0
Viewed 208

I want to create a queryset with following columns

movie.id | movie.title | movie.description | movie.maximum_rating | movie.maximum_rating_user

Below are my models and the code I have tried.

models.py

from django.contrib.auth.models import AbstractUser
from django.db import models

class User(AbstractUser):
    pass
    
class Rating(models.Model):
    movie = models.ForeignKey(Movie, on_delete=models.CASCADE, related_name="ratings")
    user = models.ForeignKey(User, on_delete=models.CASCADE, related_name="ratings")
    score = models.DecimalField(decimal_places=2, max_digits=9)
    
class Movie(models.Model):
    title = models.CharField(max_length=64)
    description = models.TextField(max_length=4096)

When i run below code the following is printed in console: (basically this shows all data in the experimental database)

views.py

def index(request, movie_id):
    movies = Movie.objects.all().annotate(
            maximum_rating=Max('ratings__score'), 
            maximum_rating_user=F('ratings__user')
            )
    for movie in movies:
        print(movie.id, movie.title, movie.description, movie.maximum_rating, movie.maximum_rating_user)
    return HttpResponse("")
id title description rating user
1 Jumping Cats Running wild 2 1
1 Jumping Cats Running wild 1 2
1 Jumping Cats Running wild 5 3
1 Jumping Cats Running wild 4 4
2 Terminator I'll be back! 8 1
2 Terminator I'll be back! 6 2
2 Terminator I'll be back! 4 3
2 Terminator I'll be back! 10 4
3 Dumbo Walt Disney 6 1
3 Dumbo Walt Disney 8 2
3 Dumbo Walt Disney 9 3
3 Dumbo Walt Disney 2 4
4 Thin Red Line Wartimes 9 1
4 Thin Red Line Wartimes 2 2
4 Thin Red Line Wartimes 5 3
4 Thin Red Line Wartimes 8 4

Now i would like to modify the query so it only returns the row for each movie where the rating has the maximum value.

When i remove the second annotate the query returns a single row for each movie with the maximum rating but then the user is not annotated.

views.py

def index(request, movie_id):
    movies = Movie.objects.all().annotate(
            maximum_rating=Max('ratings__score'))
    for movie in movies:
        print(movie.id, movie.title, movie.description, movie.maximum_rating)
    return HttpResponse("")
id title description rating
1 Jumping Cats Running wild 5
2 Terminator I'll be back! 10
3 Dumbo Walt Disney 9
4 Thin Red Line Wartimes 9

How should i modify the query so it returns the abave queryset but annotes the user who posted the highest rating?

django python
1 Answers

You can work with a Subquery expression [Django-odc] to determine the user with the highest review:

from django.db.models import OuterRef, Subquery

def index(request, movie_id):
    movies = Movie.objects.all().annotate(
        maximum_rating=Max('ratings__score'),
        max_user=Subquery(
            Rating.objects.filter(
                movie=OuterRef('pk')
            ).order_by('-score').values('user')[:1]
        )
    )
    for movie in movies:
        print(movie.id, movie.title, movie.description, movie.maximum_rating)
    return HttpResponse("")
user_675790
Related