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.

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

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

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.

Back to Top