Django ORM sum prices from 3 level nested

Question

I have these models and managers:


class ItemManager(models.Manager):
    use_for_related_fields = True

    def get_queryset(self):
        qs = super().get_queryset()
        return qs.annotate(total_price=ExpressionWrapper(
            F('gross_price') * F('qty'), output_field=models.DecimalField())
        )


class OrderManager(models.Manager):
    use_for_related_fields = True

    def get_queryset(self):
        qs = super().get_queryset()
        return qs.annotate(total_price=Sum(items__total_price))  # this doesnt work



class Order(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    name = models.CharField(max_length='50')


class Item(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    price = models.DecimalField(max_digits=14, decimal_places=2)
    qty = models.IntegerField()
    order = models.ForeignKey(Order, related_name='items', on_delete=models.CASCADE)

    objects = ItemManager()

I want to get Sum price of the order, like this:

for sale in Order.objects.all():
    print(sale.total_price)

Error:

django.core.exceptions.FieldError: Unsupported lookup 'total_price' for UUIDField or join on the field not permitted.

I can get total_price for each item, and I want to use that total_price to get Sum of all items. I need in this way because I will use that sale total_price in a model that have multiple orders as child elements.

score 2 · Accepted Answer · answered Sep 18 '19 at 12:38

2

If you query for related models, these related models do not "pass through" the .objects manager. You can override the ._base_manager, etc. But I would strongly advice against that.

You probably are better of doing the work in the OrderManager itself:

class OrderManager(models.Manager):
    use_for_related_fields = True

    def get_queryset(self):
        return super().get_queryset().annotate(
            total_price=Sum(F('items__price') * F('items__qty'))
        )

answered Sep 18 '19 at 12:38

Willem Van Onsem

443,496
30
428
555

I know this solution, but I need in this way, because I will use this to get sum of all orders in Subscription, and subscription have multiple orders. So I really need in this way. – Mirza Delic Sep 18 '19 at 12:42
@MirzaDelic: well, as aid, you can override the `_base_manager` manager of `Items`. But I personally would consider it against "*The Zen of Python*". Explicit over implicit is rule #2 :) – Willem Van Onsem Sep 18 '19 at 12:44

Django ORM sum prices from 3 level nested

1 Answers1