Subquery a Parent Model returning Error in Django-CodePudding

class Customer(models.Model):
     pass

class Order(models.Model):

    customer = models.ForeignKey(Customer, on_delete=models.CASCADE, related_name='orders')
    total_amount = models.DecimalField()

As you can see, a customer can have many orders. What I want to know, how much amount one customer has spent so far.

qs = Customer.objects.get(pk=OuterRef('customer').orders.all().aggregate(total=Sum('total_amount))
Order.objects.annotate(customer_total=Subquery(qs.values('total')[:1]))

But I am getting error

ValueError: This queryset contains a reference to an outer query and may only be used in a subquery.

My question is, can we Subquery a parent Model ? or i am doing something wrong ?

CodePudding user response：

Try this way

from django.db.models import Sum
Order.objects.filter(customer__id=1).aggregate(Sum('total_amount'))

CodePudding user response：

Your brackets aren't quite right. You need to first get the customer, then call the related orders:

customer = Customer.objects.get(pk=OuterRef('customer'))
qs = customer.orders.all().aggregate(total=Sum('total_amount))

Or all in one:

qs = Customer.objects.get(pk=OuterRef('customer')).orders.all().aggregate(total=Sum('total_amount))