# Ordering a list by averages accumulated from two models

**URL:** <https://forum.djangoproject.com/t/ordering-a-list-by-averages-accumulated-from-two-models/22558>\
**Category:** Using the ORM\
**Created:** [July 25, 2023, 9:51pm UTC](https://forum.djangoproject.com/t/ordering-a-list-by-averages-accumulated-from-two-models/22558 "2023-07-25T21:51:24Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![AndrewSRea](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/andrewsrea/32/6537_2.png) [@AndrewSRea](https://forum.djangoproject.com/u/AndrewSRea)\
**Post date:** [July 25, 2023, 9:51pm UTC](https://forum.djangoproject.com/t/ordering-a-list-by-averages-accumulated-from-two-models/22558/1 "2023-07-25T21:51:24Z")

</div>

I have a “Highest Rated” list of albums I want ordered by an average of ratings from both an `Album` model and a `Review` model.

 ![Image 7-25-23 at 2.42 PM](https://us1.discourse-cdn.com/flex026/uploads/djangoproject/original/2X/0/0e0f079dbc4e0516a16fba3e15973137c68fc893.jpeg)  
I was able to accumulate the averages with an `avg_rating` custom model field I built in the `Album` model:

```auto
class Album(models.Model):
	creator = models.ForeignKey(User, on_delete=models.SET_NULL, null=True)
	artist = models.CharField(max_length=255)
	title = models.CharField(max_length=255)
	# image =
	rating = models.IntegerField(
		choices=[(i, i) for i in range(1, 11)],
		validators=[MaxValueValidator(10), MinValueValidator(1)],
		default=10,
	)
	comment = models.TextField(null=True, blank=True)
	updated = models.DateTimeField(auto_now=True)
	created = models.DateTimeField(default=timezone.now)

        # vvv HERE IS THE CUSTOM MODEL vvv
	def avg_rating(self):
		review_ratings = 0
		orig_rating = self.rating
		review_ratings_count = self.review_set.count()

		if review_ratings_count > 0:
			for review in self.review_set.all():
				review_ratings += review.rating
				ratings_sum = review_ratings + orig_rating

			ratings_count = review_ratings_count + 1
			ratings_avg = ratings_sum / ratings_count
			avg_return = round(ratings_avg, 1)

			if avg_return == 10.0:
				avg_return = 10
			else:
				avg_return = avg_return

			return avg_return

		else:
			return self.rating

...

class Review(models.Model):
	reviewer = models.ForeignKey(User, on_delete=models.SET_NULL, null=True)
	album = models.ForeignKey(Album, on_delete=models.CASCADE)
	rating = models.IntegerField(
		choices=[(i, i) for i in range(1, 11)],
		validators=[MaxValueValidator(10), MinValueValidator(1)],
		default=10,
	)
	comment = models.TextField()
	updated = models.DateTimeField(auto_now=True)
	created = models.DateTimeField(default=timezone.now)

```

But when I try to order the “Highest Rated” list in my view like so:

```auto
ratings_list = Album.objects.all().order_by('-avg_rating')[0:5]

```

…it doesn’t work.  
Do I have to create another custom model just so I can order the list by the averages?

---

<div class="post-metadata">

**Author:** ![massover](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/massover/32/1792_2.png) [@massover](https://forum.djangoproject.com/u/massover)\
**Post date:** [July 25, 2023, 10:30pm UTC](https://forum.djangoproject.com/t/ordering-a-list-by-averages-accumulated-from-two-models/22558/2 "2023-07-25T22:30:03Z")

</div>

In order to use the `order_by` clause with the “average rating” value, it must exist in the database. Something like this should work:

```python
from django.db.models import Sum, Count
from django.db.models.functions import Round

ratings_list = Album.objects.annotate(
    # This is an average rating without `Album.rating`. You'll need to update this calculation.
    db_avg_rating=Round(Sum("review_set__rating") / Count("review_set")),
).order_by("-db_avg_rating")[0:5]

```

I want to nit at a term you used:

> I was able to accumulate the averages with an `avg_rating` custom model field I built in the `Album` model:

You created a [model method](https://docs.djangoproject.com/en/4.2/topics/db/models/#model-methods). This distinction is important. The database has no idea about this method. It is not a field. The calculation as you had written it is entirely in python.

---

<div class="post-metadata">

**Author:** ![AndrewSRea](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/andrewsrea/32/6537_2.png) [@AndrewSRea](https://forum.djangoproject.com/u/AndrewSRea)\
**Post date:** [July 26, 2023, 5:31pm UTC](https://forum.djangoproject.com/t/ordering-a-list-by-averages-accumulated-from-two-models/22558/3 "2023-07-26T17:31:56Z")

</div>

Sorry, @massover, I’ve been on the road. I will try this, thank you!

---

<div class="post-metadata">

**Author:** ![AndrewSRea](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/andrewsrea/32/6537_2.png) [@AndrewSRea](https://forum.djangoproject.com/u/AndrewSRea)\
**Post date:** [July 29, 2023, 10:14pm UTC](https://forum.djangoproject.com/t/ordering-a-list-by-averages-accumulated-from-two-models/22558/4 "2023-07-29T22:14:37Z")

</div>

@massover, I get an error when I use “review\_set” as a lookup: “`django.core.exceptions.FieldError: Cannot resolve keyword 'review_set' into field. Choices are: artist, comment, created, creator, creator_id, id, rating, review, title, updated`”

I thought I was close with this query:

```auto
ratings_list = Album.objects.annotate(
     avg_rating=Round((Sum('review__rating') + Sum('rating')) / (Count('review__rating') + 1))
).order_by('-avg_rating')

```

But I think this just returns an average from only one of the ratings in `album.review__rating`.

---

<div class="post-metadata">

**Author:** ![massover](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/massover/32/1792_2.png) [@massover](https://forum.djangoproject.com/u/massover)\
**Post date:** [July 30, 2023, 1:54am UTC](https://forum.djangoproject.com/t/ordering-a-list-by-averages-accumulated-from-two-models/22558/5 "2023-07-30T01:54:17Z")

</div>

My original syntax with `review_set` was not correct. Your query looks correct as we can see in the docs for [following relationships backwards](https://docs.djangoproject.com/en/4.2/topics/db/aggregation/#following-relationships-backwards).

If you’re not sure, I’d suggest you write a test (or some code in the shell), for example something like:

```python
from django.test import TestCase

class TestAlbum(TestCase):
    def test_annotations(self):
        album = Album.objects.create()
        rating6 = Rating.objects.create(album=album, rating=6)
        rating8 = Rating.objects.create(album=album, rating=8)
        annotated_album = Album.objects.annotate(
             sum=Sum("review__rating"),
             count=Count("review"),
             avg=F("sum") / F("count"),
        ).first()
        self.assertEqual(annotated_album.sum, 14)
        self.assertEqual(annotated_album.count, 2)
        self.assertEqual(annotated_album.avg, 7)

```

---

<div class="post-metadata">

**Author:** ![AndrewSRea](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/andrewsrea/32/6537_2.png) [@AndrewSRea](https://forum.djangoproject.com/u/AndrewSRea)\
**Post date:** [July 30, 2023, 6:42pm UTC](https://forum.djangoproject.com/t/ordering-a-list-by-averages-accumulated-from-two-models/22558/6 "2023-07-30T18:42:46Z")

</div>

@massover, mind if I ask a question? Due to my query above not working, I’m trying to take this back to basics.  
So when I create this query:

```auto
ratings_list = Album.objects.annotate(
     num_of_review_ratings=Count('review__rating')
)

```

I get the correct number of ratings from `Album.review.rating`.  
I get the correct results when I create the same kind of query for `Album.rating`.  
But when I create a query to try to get the total of `Album.rating`s and `Album.review.rating`s:

```auto
ratings_list = Album.objects.annotate(
     total_count=(Count('rating', distinct=True) + Count('review__rating', distinct=True))
)

```

I get incorrect results. The total of the `Count()`s added up is not correct.

---

<div class="post-metadata">

**Author:** ![massover](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/massover/32/1792_2.png) [@massover](https://forum.djangoproject.com/u/massover)\
**Post date:** [July 30, 2023, 6:57pm UTC](https://forum.djangoproject.com/t/ordering-a-list-by-averages-accumulated-from-two-models/22558/7 "2023-07-30T18:57:52Z")

</div>

Have you tried setting up a test?

Try annotating each field separately. What do you get when you count each field? Is it what you expect? What do get after you add the distinct? Is it what you expect?

---

<div class="post-metadata">

**Author:** ![AndrewSRea](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/andrewsrea/32/6537_2.png) [@AndrewSRea](https://forum.djangoproject.com/u/AndrewSRea)\
**Post date:** [August 6, 2023, 5:06pm UTC](https://forum.djangoproject.com/t/ordering-a-list-by-averages-accumulated-from-two-models/22558/8 "2023-08-06T17:06:00Z")

</div>

@massover, thanks for getting me to check the differences in adding `distinct` to the separate fields. It helped a lot. Now I’m SO close to getting this problem solved! I put this query together and I can feel I’m close to coding this correctly but I’m still not getting the results I want:

```auto
ratings_list = Album.objects.annotate(
     ratings_avg=Round((Sum('rating', distinct=True) + Sum('review__rating')) 
                 / (Count('review__rating') + 1), precision=1)
).order_by('-ratings_avg')

```

From the image in my original post, the order and results should be:

```auto
The War on Drugs - 9.5
The Sundays - 8.3
The Black Keys - 8.0
Nine Inch Nails - 8.0

```

But the order and results I’m getting are:

```auto
The War on Drugs - 9
The Black Keys - 8
Nine Inch Nails - 8
The Sundays - 8

```

---

<div class="post-metadata">

**Author:** ![AndrewSRea](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/andrewsrea/32/6537_2.png) [@AndrewSRea](https://forum.djangoproject.com/u/AndrewSRea)\
**Post date:** [August 6, 2023, 5:11pm UTC](https://forum.djangoproject.com/t/ordering-a-list-by-averages-accumulated-from-two-models/22558/9 "2023-08-06T17:11:44Z")

</div>

@massover, BTW…

```auto
ratings_list = Album.objects.annotate(
     ratings_sum=(Sum('rating', distinct=True) + Sum('review__rating'))
).order_by('-ratings_sum')

```

…and…

```auto
ratings_list = Album.objects.annotate(
     ratings_count=(Count('review__rating') + 1)
).order_by('-ratings_count')

```

…give the correct results and ordering. I’m just having difficulty combining them into one query.

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [August 6, 2023, 5:14pm UTC](https://forum.djangoproject.com/t/ordering-a-list-by-averages-accumulated-from-two-models/22558/10 "2023-08-06T17:14:18Z")

</div>

What happens if you put the two annotate calls in the same query?  
e.g.  
`Album.objects.annotate(...).annotate(...).order_by('-ratings_sum')`

---

<div class="post-metadata">

**Author:** ![AndrewSRea](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/andrewsrea/32/6537_2.png) [@AndrewSRea](https://forum.djangoproject.com/u/AndrewSRea)\
**Post date:** [August 6, 2023, 5:28pm UTC](https://forum.djangoproject.com/t/ordering-a-list-by-averages-accumulated-from-two-models/22558/11 "2023-08-06T17:28:51Z")

</div>

@KenWhitesell, the `Album` objects are ordered by the total sum of ALL the ratings. (But not the average of all the ratings. – But I feel you’re leading me somewhere. 😀)

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [August 6, 2023, 5:32pm UTC](https://forum.djangoproject.com/t/ordering-a-list-by-averages-accumulated-from-two-models/22558/12 "2023-08-06T17:32:50Z")

</div>

Ahh, no - my mistake. I missed the difference in the order\_by calls.

Nope, since you want two different orderings, you’re going to need to do this with two separate queries.

---

<div class="post-metadata">

**Author:** ![AndrewSRea](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/andrewsrea/32/6537_2.png) [@AndrewSRea](https://forum.djangoproject.com/u/AndrewSRea)\
**Post date:** [August 6, 2023, 5:34pm UTC](https://forum.djangoproject.com/t/ordering-a-list-by-averages-accumulated-from-two-models/22558/13 "2023-08-06T17:34:26Z")

</div>

@KenWhitesell, I was already able to display the averages for each `Album` object. But now I need to find a way to order them by average in `views.py`.

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [August 6, 2023, 5:38pm UTC](https://forum.djangoproject.com/t/ordering-a-list-by-averages-accumulated-from-two-models/22558/14 "2023-08-06T17:38:54Z")

</div>

Ok, I’m kinda lost here with what you’re trying to produce.  
Your last two queries have dropped the `ratings_avg` calculation. (Which, BTW, is returning an integer because the values you’re working with are all integer values. You’d need to cast them to Float for the division to return a floating point value.)

---

<div class="post-metadata">

**Author:** ![AndrewSRea](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/andrewsrea/32/6537_2.png) [@AndrewSRea](https://forum.djangoproject.com/u/AndrewSRea)\
**Post date:** [August 6, 2023, 5:43pm UTC](https://forum.djangoproject.com/t/ordering-a-list-by-averages-accumulated-from-two-models/22558/15 "2023-08-06T17:43:39Z")

</div>

@KenWhitesell, I split apart the original query:

```auto
ratings_list = Album.objects.annotate(
     ratings_avg=Round((Sum('rating', distinct=True) + Sum('review__rating')) 
                 / (Count('review__rating') + 1), precision=1)
).order_by('-ratings_avg')

```

…just to show that the two different parts work on their own.

But this original `ratings_avg` query isn’t returning the correct results.  
Would I need to use `output_field=FloatField()` for it to return the correct results?

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [August 6, 2023, 5:49pm UTC](https://forum.djangoproject.com/t/ordering-a-list-by-averages-accumulated-from-two-models/22558/16 "2023-08-06T17:49:01Z")

</div>

> [@AndrewSRea](#):
>
> But this original `ratings_avg` query isn’t returning the correct results.

Well, it _is_, for some definition of “correct”. 😉

> [@AndrewSRea](#):
>
> Would I need to use `output_field=FloatField()` for it to return the correct results?

Not on the final result, no. The [Cast](https://docs.djangoproject.com/en/4.2/ref/models/database-functions/#cast) needs to be before the division is performed.  
I’m not entirely sure if you need to Cast the entire numerator:

> [@AndrewSRea](#):
>
> `(Sum('rating', distinct=True) + Sum('review__rating'))`

or if you need to cast both of them, but I think that either way should be sufficient.

---

<div class="post-metadata">

**Author:** ![Knowta](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/knowta/32/32998_2.png) [@Knowta](https://forum.djangoproject.com/u/Knowta)\
**Post date:** [July 31, 2026, 12:11pm UTC](https://forum.djangoproject.com/t/ordering-a-list-by-averages-accumulated-from-two-models/22558/17 "2026-07-31T12:11:22Z")

</div>

## A possible solution of this error “Cannot resolve keyword ‘review\_set’ into field. Choices are:…”

Find that you have to statefully specify the `related_name='review_set'` to use `annotate` or `prefetch_related` method.

In your case, that is `album = models.ForeignKey(Album, on_delete=models.CASCADE, related_name='review_set')`.

Encountering the similar problem in July, 2026. Hope this may help you oneday.

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [July 31, 2026, 5:13pm UTC](https://forum.djangoproject.com/t/ordering-a-list-by-averages-accumulated-from-two-models/22558/18 "2026-07-31T17:13:13Z")

</div>

That is not an accurate statement. There is no requirement to use the related\_name attribute in those situations. If you are getting an error without it, then you have something else that isn’t correct.

---

<div class="post-metadata">

**Author:** ![Knowta](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/knowta/32/32998_2.png) [@Knowta](https://forum.djangoproject.com/u/Knowta)\
**Post date:** [July 31, 2026, 5:41pm UTC](https://forum.djangoproject.com/t/ordering-a-list-by-averages-accumulated-from-two-models/22558/19 "2026-07-31T17:41:11Z")

</div>

Thanks for your reply, I am not pretty sure what’s happening though regarding learning Django for just a few weeks.

api\_views.py

```python
class BookViewSet(viewsets.ReadOnlyModelViewSet):
    queryset = Book.objects.prefetch_related('review_set').annotate(
        avg_rating=Avg('review_set__rating')
    )
    serializer_class = BookSerializer

```

models.py

```python
class Publisher(models.Model):    
    name = models.CharField(max_length=50, help_text="Name of Publisher.")
    website = models.URLField(blank=True, help_text="Website of Publisher.")
    email = models.EmailField(blank=True, help_text="Email of Publisher.")

    def __str__ (self):
        return self.name

class Book(models.Model):    
    title = models.CharField(max_length=70, help_text="Title of the Book.")
    publication_date = models.DateField(verbose_name="Publication date of the Book.")
    isbn = models.CharField(blank=True, max_length=20, verbose_name="ISBN number of the Book.")    
    publisher = models.ForeignKey(Publisher, on_delete=models.CASCADE)
    contributors = models.ManyToManyField("Contributor", through="BookContributor")
    cover = models.ImageField(upload_to="book_covers/", blank=True)
    sample = models.FileField(upload_to="book_samples/", blank=True)

    def __str__ (self):
        return self.title

class Review(models.Model):
    content = models.TextField(help_text="The Review text.")
    RATING_CHOICES = [(i, '☆' * i) for i in range(1, 6)]
    rating = models.IntegerField(choices=RATING_CHOICES, help_text="The rating the reviewer has given.")
    date_created = models.DateTimeField(auto_now_add=True, help_text="The date and time the review was created.")
    date_edited = models.DateTimeField(auto_now_add=True, help_text="The date and time the review was edited.")
    creator = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.CASCADE)
    book = models.ForeignKey(Book, on_delete=models.CASCADE, help_text="The Book that this review is for.")

    def __str__ (self):
        return self.book.title

```

It will raise error:

> django.core.exceptions.FieldError: Cannot resolve keyword ‘review\_set’ into field. Choices are: bookcontributor, contributors, cover, id, isbn, publication\_date, publisher, publisher\_id, review, reviewerprofile, sample, title

It works without error after adding the `related_name="review_set"` for some reasons as showed in the following screenshot.

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [August 1, 2026, 2:57am UTC](https://forum.djangoproject.com/t/ordering-a-list-by-averages-accumulated-from-two-models/22558/20 "2026-08-01T02:57:09Z")

</div>

Side note: Please do not post images of text data here. Copy past the text data into the body of your post, surrounded by lines of three backtick - ` characters. This means you’ll have a line of ```, then your lines of text, then another line of ```.

Your syntax for the annotate is incorrect. In that situation, it should be `avg_rating=Avg('review__rating')`

[Next page](https://forum.djangoproject.com/t/ordering-a-list-by-averages-accumulated-from-two-models/22558.md?page=2)