时间戳

首页 > 解决方案 > Django 注释 sum 子查询

python - Django 注释 sum 子查询

问题描述

如何对子查询的字段（使用 OuterRef ）求和并在外部模型上对其进行注释？

请注意，我有一个共同点my_special_queryset_annotator，通过添加一些注释来改变查询集，......所以我不想直接使用Sum('books__sections__page')

假设以下模型

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')
        )
    )
)

标签： pythondjango

解决方案

以丑陋的方式解决问题的一种尝试是创建如下所示的内容，但我无法配置如何不将sum_field参数传递给它并仅.values(sum_field)在给定的查询集上使用

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',
    )
)

推荐阅读