Django aggregate and grouping : code cleanliness problem ...

Question

Hello !

Here is a small problem I am having.

---

3 very simple models :

>>> class Instrument(models.Model):
...     name = models.CharField(max_length=100)
... 
>>> class Musician(models.Model):
...     instrument = models.ForeignKey(Instrument)
... 
>>> class Song(models.Model):
...     author = models.ForeignKey(Musician)

I would like to count the number of songs, grouped by instrument name and by author

---

I have solutions to it, but I would like to know what is the best way to write it in pure django-orm, such as the code would be clean, concise and reusable (I mean something that you can easily re-use to group by different attributes). What I am actually trying to see is if some code I have written to solve this problem generically is really useful, or if I just missed something big ...

Here's the first solution I think of :

results = []
for instrument_name in Instrument.objects.values_list('instrument', flat=True):
    for musician in Musician.objects.filter(instrument__name=instrument_name):
        results.append((
            instrument_name,
            musician,
            Song.objects.filter(author=musician).count())
        )

Thank you for your help !!!

Answer 1

from Django docs:

from django.db.models import Count
Song.objects.values('author','author__instrument').annotate(Count("id"))

I'm not 100% sure it will work (it's 2:20 AM), but I hope so.