pythondjangodjango-queryset

Django queryset annotate sum of related objects of related objects


I have

class Book(models.Model):
    title = models.CharField(max_length=32)

class Table(models.Model):
    book = models.ForeignKey(Book, related_name='tables')

class TableEntry(models.Model):
    table = models.ForeignKey(Table, related_name='entries')
    value = models.FloatField()

and want a queryset of books that has annotated for each book, the sum of all table entries of this book.

I have tried

Book.objects.all().annotate(sum=Sum('tables__entries'))

but this does not seem to work. [Edit: Seems to work, after all, as expected.]

Note that the idea works when the sum of all entries of each table should be annotated:

Table.objects.all().annotate(sum=Sum('entries'))

Solution

  • You can annotate the prefetched Tables, like:

    from django.db.models import Prefetch, Sum
    
    Book.objects.prefetch_related(
        Prefetch('tables', Table.objects.annotate(sum=Sum('entries__value')))
    )
    

    If you then access a Book instance (named book for example), the book.tables.all() is a QuerySet of Tables with each an extra sum attribute that is the sum of value of the entries of the Table.