Django annotate sum subquery

Question

How to sum a field of a subquery ( with OuterRef ) and annotate it on outer model?

note that I have a common my_special_queryset_annotator that alter queryset by adding some annotations, ... so i dont want to use direct Sum('books__sections__page')

let assume following models

class Library(models.Model):
    votes=models.IntegerField()

class Book(models.Model):
    library=models.ForiegnKey(Library)

class Section(models.Model):
    book=models.ForiegnKey(Book)
    pages=models.IntegerField()

# this works, but when want to use `my_special_queryset_annotator` 
# we could not do this simple annotation
Library.annotate(
    all_pages=Sum('books__sections__pages'),
)

# when want to sum on a Subquery, its must constructed like below but it dont work
Library.objects.annotate(
    all_pages=SUM(  # <-- problem
        Subquery(
            my_special_queryset_annotator(
                Section.objects.filter(book__libraray_id=OuterRef('id'))
            ).values('altered_pages')
        )
    )
)

So: all_pages=Subquery(qs.annotate(total_pages=Sum('pages')).values('total_pages')[:1]) — dirkgroten
– dirkgroten, Commented Sep 17, 2019 at 16:16
i have tested this before and this is not working correct, specially with OuterRef, it return only first section pages as all_pages. — wtayyeb
– wtayyeb, Commented Sep 18, 2019 at 22:06

wtayyeb · Accepted Answer · 2019-09-18 22:12:48Z

5

one try that solve the problem in ugly way is to create something like below, but i cant configure how not to pass sum_field parameter to it and just use .values(sum_field) on given queryset

class SumSubquery(Subquery):
    template = "(SELECT SUM(%(sum_field)s) FROM (%(subquery)s) _sum)"
    output_field = models.DecimalField()

    def __init__(self, queryset, output_field=None, *, sum_field, **extra):
        extra['sum_field'] = sum_field
        super(SumSubquery, self).__init__(queryset, output_field, **extra)

# and use like below

Library.objects.annotate(
    all_pages=SumSubquery(
        my_special_queryset_annotator(
            Section.objects.filter(book__libraray_id=OuterRef('id'))
        ),
        sum_field='altered_pages',
    )
)

answered Sep 18, 2019 at 22:12

wtayyeb

1,9793 gold badges18 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Django annotate sum subquery

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related