Django aggregate through table related by ForeignField

Question

I want to get total price for specified order as total_price(self) of Order model so it sums order items amount multiplied by their price. I cant relate through pr_id field to product table to get product price.

Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "D:\python\projects\pipingapi\restapi\models.py", line 75, in total_price
    order_price = self.items_in_order.all().aggregate(price=Sum(F('amount') * F('pr_id.pr_price')))
  File "C:\Users\Adam\AppData\Local\Programs\Python\Python36-32\lib\site-packages\django\db\models\query.py", line 376, in aggregate
    query.add_annotation(aggregate_expr, alias, is_summary=True)
  File "C:\Users\Adam\AppData\Local\Programs\Python\Python36-32\lib\site-packages\django\db\models\sql\query.py", line 1000, in add_annotation
    summarize=is_summary)
  File "C:\Users\Adam\AppData\Local\Programs\Python\Python36-32\lib\site-packages\django\db\models\aggregates.py", line 47, in resolve_expression
    c = super().resolve_expression(query, allow_joins, reuse, summarize)
  File "C:\Users\Adam\AppData\Local\Programs\Python\Python36-32\lib\site-packages\django\db\models\expressions.py", line 600, in resolve_expression
    c.source_expressions[pos] = arg.resolve_expression(query, allow_joins, reuse, summarize, for_save)
  File "C:\Users\Adam\AppData\Local\Programs\Python\Python36-32\lib\site-packages\django\db\models\expressions.py", line 447, in resolve_expression
    c.rhs = c.rhs.resolve_expression(query, allow_joins, reuse, summarize, for_save)
  File "C:\Users\Adam\AppData\Local\Programs\Python\Python36-32\lib\site-packages\django\db\models\expressions.py", line 511, in resolve_expression
    return query.resolve_ref(self.name, allow_joins, reuse, summarize, simple_col)
  File "C:\Users\Adam\AppData\Local\Programs\Python\Python36-32\lib\site-packages\django\db\models\sql\query.py", line 1601, in resolve_ref
    join_info = self.setup_joins(field_list, self.get_meta(), self.get_initial_alias(), can_reuse=reuse)
  File "C:\Users\Adam\AppData\Local\Programs\Python\Python36-32\lib\site-packages\django\db\models\sql\query.py", line 1504, in setup_joins
    names[:pivot], opts, allow_many, fail_on_missing=True,
  File "C:\Users\Adam\AppData\Local\Programs\Python\Python36-32\lib\site-packages\django\db\models\sql\query.py", line 1420, in names_to_path
    "Choices are: %s" % (name, ", ".join(available)))
django.core.exceptions.FieldError: Cannot resolve keyword 'pr_id.pr_price' into field. Choices are: amount, id, or_id, or_id_id, pr_id, pr_id_id

There was meta parameter unique together on pr_id and or_id but I noticed Django does not support combined primary keys so I deleted meta.

I tried also to use group_by somehow, but I couldnt manage to do it.

Models:

from django.db import models
from django.contrib.auth.models import AbstractUser
from django.contrib.auth import get_user_model
from django.conf import settings
from django.db.models import Sum, F

def get_sentinel_user():
    return get_user_model().objects.get_or_create(username='deleted')[0]

class Order(models.Model):
    or_id = models.AutoField(primary_key=True)
    or_start_date = models.DateTimeField(auto_now_add=True)
    or_is_finished = models.BooleanField(default=False)
    or_finish_date = models.DateTimeField(null=True)
    or_is_sent = models.BooleanField(default=False)
    or_sent_date = models.DateTimeField(null=True)
    or_username = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.SET(get_sentinel_user))

    def __str__(self):
        return 'Order No. {}, started {}'.format(self.or_id, self.or_start_date)

        @property
    def total_price(self):
        order_price = self.items_in_order.all().aggregate(price=Sum(F('amount') * F('pr_id.pr_price')))
        return order_price

class ProductsInOrders(models.Model):

    or_id = models.ForeignKey(Order, on_delete=models.PROTECT, related_name='items_in_order', db_column='or_id')
    pr_id = models.ForeignKey(Product, on_delete=models.PROTECT, related_name='chosen_products', db_column='pr_id')
    amount = models.IntegerField()

    def __str__(self):
        return 'Order No. {}, Product: {}, Amount: {}'.format(self.or_id.or_id, self.pr_id.pr_name, self.amount)

class Product(models.Model):
    PIPES = 'PI'
    FITTINGS = 'FI'
    VALVES = 'VA'
    WELD_AND_THREAD = 'WT'
    CATEGORY_CHOICES = [(PIPES, 'Pipes'),
                        (FITTINGS, 'Fittings'),
                        (VALVES, 'Valves'),
                        (WELD_AND_THREAD, 'Welds and threads')]

    pr_id = models.AutoField(primary_key=True)
    pr_name = models.CharField(max_length=50)
    pr_cat = models.CharField(max_length=2, choices=CATEGORY_CHOICES)
    pr_price = models.DecimalField(max_digits=8, decimal_places=2)
    pr_sup = models.ForeignKey(Supplier, on_delete=models.PROTECT)

    def __str__(self):
        return self.pr_name

Willem Van Onsem Willem Van Onsem · Accepted Answer · 2020-05-12T10:47:59

One uses double underscores (__) to look through a relation, so:

order_price = self.items_in_order.aggregate(
    price=Sum(
        ExpressionWrapper(F('amount'), output_field=DecimalField(max_digits=10, decimal_places=2)) *
        F('pr_id__pr_price')
    )
)

Django aggregate through table related by ForeignField

1 Answers