django orm - annotate / aggregation (avg) in subquery

Question

I have this model:

class UserMovieRel(models.Model):
    user = models.ForeignKey("register.User", on_delete=models.CASCADE)
    movie = models.ForeignKey("Movie", on_delete=models.CASCADE, related_name="users")
    rating = models.PositiveIntegerField(
        validators=[MinValueValidator(1), MaxValueValidator(10)], null=True, blank=True
    )
    advice = models.CharField(max_length=500, null=True, blank=True)
    objects = UserMovieRelManager()

    def __str__(self) -> str:
        return f"{self.user} - {self.movie} (rating: {self.rating or 'n/a'})"

    class Meta:
        constraints = [
            models.UniqueConstraint(fields=["user", "movie"], name="user_movie_unique"),
        ]

I'm trying to get the avg rating for each movie in this way:

avg_ratings = UserMovieRel.objects.filter(movie_id=OuterRef("movie_id")).exclude(rating__isnull=True).annotate(avg_rating=Avg("rating"))
UserMovieRel.objects.annotate(avg_rating=Subquery(avg_ratings[0]))

but it fails:

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

I've tried also with aggregation:

UserMovieRel.objects.annotate(
    avg_rating=Subquery(
        UserMovieRel.objects.filter(
            movie_id=OuterRef("movie_id")
        ).aggregate(
            avg_rating=Avg("rating")
        )["avg_rating"]
    )
)

but I've got the same error.

any help on this? Thanks

score 1 · Accepted Answer · answered May 08 '23 at 15:30

You can filter with:

Movie.objects.annotate(avg_rating=Avg('users__rating'))

the reason we use users is because that is the value for the related_name='users', but does not make much sense.

You can rename it to:

from django.conf import settings

from django import models


class Review(models.Model):
    user = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.CASCADE)
    movie = models.ForeignKey(
        'Movie', on_delete=models.CASCADE, related_name='reviews'
    )
    rating = models.PositiveIntegerField(
        validators=[MinValueValidator(1), MaxValueValidator(10)],
        null=True,
        blank=True,
    )
    advice = models.CharField(max_length=500, null=True, blank=True)
    objects = UserMovieRelManager()

and thus query with:

Movie.objects.annotate(avg_rating=Avg('reviews__rating'))

Note: Models normally have no Rel suffix. A model is not a relation or table, it is stored in a relational database as a table, but even then it has extra logic like validators, managers, etc.

Note: It is normally better to make use of the settings.AUTH_USER_MODEL ^[Django-doc] to refer to the user model, than to use the User model ^[Django-doc] directly. For more information you can see the referencing the User model section of the documentation.

django orm - annotate / aggregation (avg) in subquery

1 Answers1