[FIXED] How to group by in ManyToMany relationships in Django?

Issue

I am creating a web application with Django and I have some problems using its ORM to make queries. I have these models:

class Country(models.Model):
name = models.CharField(max_length=25)

class Song(models.Model):
title = models.CharField(max_length = 25)
country = models.ForeignKey(Country, on_delete=models.CASCADE, related_name="songs")
ratings = models.ManyToManyField(Country, through='Rating')

class Rating(models.Model):
rating = models.PositiveSmallIntegerField()
country_id = models.ForeignKey(Country, on_delete=models.SET_NULL, null=True)
song_id = models.ForeignKey(Song, on_delete=models.SET_NULL, null=True)

A Country rates many Songs (with ratings 1-10), and a Song can be rated by many Countries, so there's a ManyToMany relationship between these models through the Rating table. I am making a query to get the number of 10 points a Song has received, but I don't know how. To do that, I tried this query:

result = Rating.objects.filter(rating=10).values('song_id').annotate(ten_points = Count('rating'))

I read Django documentation and I understood that values() method is like group by clause in SQL, but it doesn't work because this query returns a queryset of dictionaries like this:

<QuerySet [{'song_id': 1, 'ten_points': 1}, {'song_id': 2, 'ten_points': 1}, {'song_id': 2, 'ten_points': 1}, {'song_id': 3, 'ten_points': 1}, {'song_id': 3, 'ten_points': 1} 

Why am I getting different dictionaries with the same key and value 1, instead of a dictionary with the key and the value the total number of ten points?

Solution

You need to use .order_by(…) to force grouping, so:

result = Rating.objects.filter(rating=10).values(
    'song_id'
).annotate(ten_points=Count('rating')).order_by('song_id')

But in this specific case, it might make more sense to annotate the Songs:

Song.objects.filter(
    rating__rating=10
).annotate(
    ten_points=Count('rating')
)

This will only include Songs that have at least one Rating record with rating=10. If you want to annotate all Songs, you can use:

from django.db.models import Q

Song.objects.annotate(
    ten_points=Count('rating', filter=Q(rating__rating=10))
)

Answered By - Willem Van Onsem