2

I have these models:

class Agency(models.Model):
    pass


class User(models.Model):
    agency = models.ForeignKey(Agency)


class Feedback(models.Model):
    rating = models.DecimalField()
    user = models.ForeignKey(User)

and I want to annotate a queryset with the average of all ratings. I expected this to work:

Feedback.objects.annotate(avg_rating=Avg('rating')).values('rating', 'avg_rating')

but it just outputs this:

<QuerySet [{'rating': 0.8, 'avg_rating': 0.8}, {'rating': 0.2, 'avg_rating': 0.2}, {'rating': 0.6, 'avg_rating': 0.6}, {'rating': 1.0, 'avg_rating': 1.0}, {'rating': 0.4, 'avg_rating': 0.4}]>

As you can see the average should be 3.0. Where am I going wrong?

For a bit of clarity, I'm trying to do something like this:

agencies = Agency.objects.annotate(
    avg_rating=Coalesce(Subquery(
        Feedback.objects.filter(user__agency_id=OuterRef('pk'))
        .values('rating')
        .annotate(avg_rating=Avg('rating', output_field=DecimalField()))
        .values('avg_rating')
    ), 0)
)

where the average rating is per agency. Any ideas?

Ross Lote
  • 814
  • 1
  • 8
  • 19
  • Are you trying to find out avg rating of a particular user ? Or the entire users who belong to one Agency ? Can you describe in words what is the use case ? – Sandeep Balagopal Oct 10 '17 at 11:38

2 Answers2

2
Feedback.objects.aggregate(avg_rating=Avg('rating'))
Sandeep Balagopal
  • 1,943
  • 18
  • 28
0

Maybe try this:

agencies = Agency.objects.annotate(
avg_rating=Coalesce(Subquery(
    Feedback.objects.values('user__agency')
    .annotate(avg_rating=Avg('rating', output_field=DecimalField()))
    .values('avg_rating')
), 0)))
rajkris
  • 1,775
  • 1
  • 9
  • 16