Queryset with annotate Subquery with OuterRef and Sum

Question

I'm trying to return the sum of fields from another model inside a Subquery.

My main queryset returns all users of type company. I have to return the total of credits used by taking the data from CreditOrder and Sum the credit_used field.

I'm using ClusterableModel and ParentalKey from django-modelcluster

My CreditOrder model

class CreditOrder(ClusterableModel):
    credit = ParentalKey(
        Credit, on_delete=models.CASCADE, related_name="credit_order"
    )
    order = ParentalKey(Order, on_delete=models.CASCADE, related_name="credit_order")
    credit_used = models.DecimalField(
        max_digits=12, decimal_places=2, null=True, blank=True
    )

My User model

class User(AbstractUser, ClusterableModel):
    username = models.CharField(max_length=40, null=True, blank=True)
    user_type = models.CharField(max_length=20, choices=TIPO_UTENTE, default="dipendente")

My queryset using the class model User

    def get_queryset(self, request):
        qs = super().get_queryset(request)
        qs = qs.filter(user_type='company')
        credits_used_subquery = Subquery(CreditOrder.objects.filter(credit__font__company__id=OuterRef('id')).order_by()
                    .values('credit_used').annotate(credit_used_sum=Sum('credit_used'))
                    .values('credit_used_sum'), output_field=DecimalField())
        qs = qs.annotate(
            _credits_used_sum=credits_used_subquery
        )
        return qs

But this error is returning me:

django.db.utils.ProgrammingError: more than one row returned by a subquery used as an expression

Welcome to Stack Overflow. Please see how to write a minimal reproducible example. Specifically few things are confusing in your question what is ClusterableModel, ParentalKey?? What model class is your get_queryset method dealing with? The problem is likely as the error says your subquery returns multiple rows. You can limit it by slicing it [:1] which might help, but depending on what you want might give wrong results. — Abdul Aziz Barkat
– Abdul Aziz Barkat, Commented Jul 7, 2021 at 9:10
Hi Abdul, thanks for your feedback. I edited the post with more details. Unfortunately I can't split, all the objects and their total serve me. I also tried to perform this subquery: CreditOrder.objects.filter(credit__font__company__id =OuterRef('id')).aggregate(credit_used_sum=Sum('credit_used')), but i get this error: This queryset contains a reference to an outer query and may only be used in a subquery. — caiojhonny
– caiojhonny, Commented Jul 7, 2021 at 9:41

Brian Destura · Accepted Answer · 2021-07-07 12:06:59Z

1

If you just need to sum all the credits used by the company, you can do:

qs.annotate(_credits_used_sum=Sum('font__credit__credit_used'))

edited Jul 7, 2021 at 12:06

answered Jul 7, 2021 at 11:57

Brian Destura

12.1k3 gold badges22 silver badges39 bronze badges

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

Comments

caiojhonny · Accepted Answer · 2021-07-08 20:15:21Z

1

I solved my problem with this class:

class SubquerySum(Subquery):
    template = "(SELECT COALESCE(SUM(%(field)s), %(zero_value)s) FROM (%(subquery)s) _sum)"

    def as_sql(self, compiler, connection, template=None, **extra_context):
        if 'field' not in extra_context and 'field' not in self.extra:
            if len(self.queryset._fields) > 1:
                raise FieldError('You must provide the field name, or have a single column')
            extra_context['field'] = self.queryset._fields[0]
        if 'zero_value' not in extra_context and 'zero_value' not in self.extra:
            extra_context['zero_value'] = 0
        return super().as_sql(compiler, connection, template=template, **extra_context)

and

def get_queryset(self, request):
    credit_query=CreditOrder.objects.filter(credit__font__company__id=OuterRef('id')).order_by()
            .values('credit_used')
    qs = super().get_queryset(request)
    qs = qs.filter(user_type='company')
    qs = qs.annotate(
        _credits_used_sum=SubquerySum(credit_query, zero_value=0, field='credit_used')
        )
    return qs

answered Jul 8, 2021 at 20:15

caiojhonny

116 bronze badges

1 Comment

Diganta Protic Biswas Over a year ago

Awesome brother. this helped me. You opened a door and I will look into how to create custom classes like SubquerySum on my own. THanks

Brad Martsberger · Accepted Answer · 2021-07-07 18:15:44Z

Without seeing all the models between CreditOrder and User, it's hard to tell exactly what you've got wrong. It looks like the Credit model is linked to Font and Font might have an attribute called Company which is a foreign key to the User model?

In any case, your first values call has the wrong argument, you need to group by the the essentially the same thing you are linked to in the outer ref. So I'd suggest

.values('credit__font__company__id')

In the first call to values. And keep the annotate and second call to values the same.

Another answer suggests doing the Sum with a join instead of a Subquery, if you like the simplicity of that api, but you still want to use a Subquery, you can use the django-sql-utils package. After you pip install django-sql-utils

from sql_util.utils import SubquerySum

qs.annotate(_credits_used_sum=SubquerySum('font_credit_credit_used')

Collectives™ on Stack Overflow

Queryset with annotate Subquery with OuterRef and Sum

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related