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
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 theUser
model [Django-doc] directly. For more information you can see the referencing theUser
model section of the documentation.