djangodjango-modelsdjango-ormdjango-aggregationdjango-database-functions

django orm - annotate / aggregation (avg) in subquery


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


Solution

  • 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.