Django annotation returns 1 for each item

I have 2 almost identical models.

class FavoriteBook(models.Model):

    class Meta:
        # Model for books added as favorites
        verbose_name = "Favorite Book"
        unique_together = ['user', 'book']

    user = models.ForeignKey(User, null=False, blank=False, on_delete=models.CASCADE, verbose_name="User", related_name="favorite_books")
    book = models.ForeignKey(Book, null=False, blank=False, on_delete=models.CASCADE, verbose_name="Book")

    def __str__(self):
        return "{user} added {book} to favorites".format(user=self.user, book=self.book)

class SpoilerVote(models.Model):

    class Meta:
        # Model for votes of book spoilers
        verbose_name = "Spoiler Vote"
        unique_together = ['spoiler', 'user']

    spoiler = models.ForeignKey(Spoiler, null=False, blank=False, on_delete=models.CASCADE, verbose_name="Spoiler")
    user = models.ForeignKey(User, null=False, blank=False, on_delete=models.CASCADE, verbose_name="User", related_name="bookspoiler_votes")
    choices = (
        ('U', 'UP'),
        ('D', 'DOWN'),
    )
    vote = models.CharField(max_length=1, choices=choices, null=False, blank=False, verbose_name="Vote")

    def __str__(self):
        return "{user} liked {book}'s spoiler".format(user=self.user, book=self.spoiler.book.book_title)

Following query works fine for FavoriteBook model.

most_popular = FavoriteBook.objects.values("book", "book__book_title", "book__year").annotate(count=Count("book")).order_by("-count")[:20]

But this one does not work for SpoilerVote model. It returns 1 for each item.

SpoilerVote.objects.values("spoiler", "user", "vote").annotate(count=Count("spoiler")).order_by("-count")

What am I missing? There is no difference whatsoever.

Solution

Issue with the Current Query

In your SpoilerVote query:

SpoilerVote.objects.values("spoiler", "user", "vote").annotate(count=Count("spoiler")).order_by("-count")

You are using .values("spoiler", "user", "vote"), which groups by spoiler, user, and vote. This means that each combination of spoiler, user, and vote will be counted, resulting in a count of 1 for each row because it's grouping at a more granular level than you want. Solution

To get the count of votes for each spoiler and order them by this count, you should group by spoiler only. Here's the corrected query:

from django.db.models import Count

# Aggregate and order by the count of votes for each spoiler
most_voted_spoilers = SpoilerVote.objects.values("spoiler", "spoiler__book__book_title").annotate(count=Count("spoiler")).order_by("-count")

Explanation

.values("spoiler", "spoiler__book__book_title"): This tells Django to group the results by the spoiler and also include the book title related to the spoiler for easier understanding of the result.

.annotate(count=Count("spoiler")): This adds a count of votes per spoiler. Since we're grouping by spoiler, this count reflects the total number of votes each spoiler has received.

.order_by("-count"): Orders the results by the count in descending order, so the most voted spoilers come first.

With this adjusted query, you should get a list of spoilers ordered by the number of votes they have received, similar to how you obtained the most popular books in the FavoriteBook model.