Django: Nested Sum() annotations across three Model Relations

Question

I am using Django to sort out a storefront environment to handle orders and am struggling on one of the annotations I am attempting to write.

The salient data models are as such

class Order(ClusterableModel):
    "various model fields about the status, owner, etc of the order"

class OrderLine(Model):
    order = ParentalKey("Order", related_name="lines")
    product = ForeignKey("Product")
    quantity = PositiveIntegerField(default=1)
    base_price = DecimalField(max_digits=10, decimal_places=2) 

class OrderLineOptionValue(Model):
    order_line = ForeignKey("OrderLine", related_name="option_values")
    option = ForeignKey("ProductOption")
    value = TextField(blank=True, null=True)
    price_adjustment = DecimalField(max_digits=10, decimal_places=2, default=0)

The OrderLine represents one or more of a specific product being bought at a specific base price and quantity. This base price has been copied from the product model in order to preserve the price of the product at the time the order was created.

The Order therefore is just a collection of multiple OrderLines

The complexity comes in the OrderLineOptionValue model which represents a modification to the base price based on the choices made by the user, and each order line may have multiple adjustments if the product has multiple options. Color, size, weight, etc may each have variable price impacts.

When querying the OrderLine model I have been able to successfully annotate each result with that line's appropriate line total of (base+sum(price_adjustments))*quantity using the following query:

annotation = {
    "line_total": ExpressionWrapper((F("base_price")+Coalesce(Sum("option_values__price_adjustment", output_field=DecimalField(max_digits=10, decimal_places=2)), Value(0)))*F("quantity"), output_field=DecimalField(max_digits=10, decimal_places=2)),
}
OrderLine.objects.all().annotate(**annotation)

This annotation appears to work correctly for all of the tests I've tried throwing at it. Of note, OrderLines may have no price_adjustments, thus the Coalesce.

My problems start when trying to annotate each order with it's grand total summing all of it's respective line items together. My initial attempt resulted in the exception Cannot compute Sum('line_total'): 'line_total' is an aggregate which I can only assume is indeed an illegal SQL request, as my practical knowledge of SQL is a bit rusty.

lineItemSubquery=OrderLine.objects.filter(order=OuterRef('pk')).order_by()
#the same annotation as above
lineItemSubquery=lineItemSubquery.annotate(**annotation).values("order")

Order.objets.all().annotate(annotated_total=Coalesce(Subquery(lineItemSubquery.annotate(sum_total=Sum("line_total")).values('sum_total')), 0.0))

After stumbling across this question I attempted to restructure it a bit, and while I was able to get it to return a number, it does so incorrectly, appearing to return only the first line_total for each order.

lineItemSubquery=OrderLine.objects.filter(Q(order=OuterRef("pk"))).annotate(**annotation).values("line_total")
Order.objects.all().annotate(annotated_total=Coalesce(Subquery(lineItemSubquery), 0.0))

By slicing the lineItemSubquery [1:2] the annotation also works but then calculates out to the second line item's amount ignoring any other line items. I assume this is a byproduct of the referenced question and how they're requesting the maximum (first result as ordered) of a set of values instead of a Sum of the entire dataset.

My instinct says that I need to find a way to Sum() the Subquery, or that due to the multi-level aspect, I need some sort of additional OuterRef to bridge the relationships between all three models? I'm seriously considering simply caching each OrderLine's calculated total directly in a model field for the purpose in order to avoid the problem entirely.

score 0 · Answer 1 · answered Jul 09 '22 at 16:49

I've handled this exact situation in an online store platform. The basics of our models are (with many more fields than these, of course):

class Product(models.Model):
    base_price = models.DecimalField(...)
    options = models.ManyToManyField('ProductOption', ...)

class ProductOption(models.Model):
    [price - see below]
    ...

class Order(models.Model):
    cost = models.DecimalField(...)

class OrderItem(models.Model):
    order = models.ForeignKey('Order', ...)
    cost = models.DecimalField(...)

class OrderItemOption(models.Model):
    order_item = models.ForeignKey('OrderItem', null=True, blank=True, on_delete=models.CASCADE)
    product_option = models.ForeignKey('ProductOption', null=True, blank=True, on_delete=models.PROTECT)
    value = models.CharField(max_length=255, default='', blank=True)

A price attribute for a product could either be a field on the Product model (very simplistic) or could be a calculated property based upon, perhaps, a pricing table or any number of other factors. To me, the latter approach is preferable. When employing the latter approach, you could either use the @property decorator on the def price(self): declaration for a Product or else define a function: def calculate_price(self):.

With that background and to address your question, I override save() on both Order and OrderItem in order to calculate the cost and then store it in the cost field of each of these models. Of course you also have to trigger the calculation of Order.cost from the OrderItem.save() method so that the Order cost is always up-to-date. Calculation of costs can get very complex. I would never attempt to calculate costs in SQL. Also, I always prefer overriding save() rather than using a post_save() signal.

You'll have to be careful in triggering calculations on one model from another model so that you don't encounter a recursion error.

The above-described code (and successive iterations) has been running in production for six years.

Django: Nested Sum() annotations across three Model Relations

1 Answers1