CODEWITHSUNDEEP
pythondjangodjango-modelsormdjango-queryset
Ya'el
Ya'el

Reputation: 301

Django get most recent value AND aggregate values

I have a model which I want to get both the most recent values out of, meaning the values in the most recently added item, and an aggregated value over a period of time. I can get the answers in separate QuerySets and then unite them in Python but I feel like there should be a better ORM approach to this. Anybody know how it can be done?

Simplified example:

Class Rating(models.Model):
     movie = models.ForeignKey(Movie, related_name="movieRatings")
     rating = models.IntegerField(blank=True, null=True)
     timestamp = models.DateTimeField(auto_now_add=True)

I wish to get the avg rating in the past month and the most recent rating per movie.

Current approach:

recent_rating = Rating.objects.order_by('movie_id','-timestamp').distinct('movie')
monthly_ratings = Rating.objects.filter(timestamp__gte=datetime.datetime.now() - datetime.timedelta(days=30)).values('movie').annotate(month_rating=Avg('rating'))

And then I need to somehow join them on the movie id.

Thank you!

Upvotes: 0

Views: 643

Answers (2)

Ines Tlili
Ines Tlili

Reputation: 810

I suggest you add a property method (monthly_rating) to your rating model using the @property decorator instead of calculating it in your views.py :

@property
def monthly_rating(self):
    return 'calculate your avg rating here'

Upvotes: 1

Roman Yakubovich
Roman Yakubovich

Reputation: 893

Try this solution based on Subquery expressions:

from django.db.models import OuterRef, Subquery, Avg, DecimalField

    month_rating_subquery = Rating.objects.filter(
        movie=OuterRef('movie'),
        timestamp__gte=datetime.datetime.now() - datetime.timedelta(days=30)
    ).values('movie').annotate(monthly_avg=Avg('rating'))

    result = Rating.objects.order_by('movie', '-timestamp').distinct('movie').values(
        'movie', 'rating'
    ).annotate(
        monthly_rating=Subquery(month_rating_subquery.values('monthly_avg'), output_field=DecimalField())
    )

Upvotes: 1

Related Questions