Joining a parent table with two child tables using Django ORM and performing calculations

Using Django
1

I have a parent table Order whose primary key is the foreign key of the two child tables Item and Payment

Item

 class Item(models.Model):
     item_name = models.CharField(max_length=200, null=True)
     order = models.ForeignKey(Order, on_delete=models.CASCADE)
     weight = models.DecimalField(null=True, max_digits=10, decimal_places=3)
     rate_per_gram = models.DecimalField(null=True, max_digits=10, decimal_places=2)
     charge_per_gram = models.DecimalField(null=True, max_digits=10, decimal_places=2)
     making_charge = models.DecimalField(null=True, max_digits=10, decimal_places=2)
     total = models.DecimalField(null=True, max_digits=10, decimal_places=2)
 
     def __str__(self):
        return self.item_name

and

Payment

class Payment(models.Model):
    order = models.ForeignKey(Order, on_delete=models.CASCADE)
    created_at = models.DateField(auto_now_add=True)
    updated_at = models.DateField(auto_now=True)
    amount = models.DecimalField(null=True, max_digits=10, decimal_places=2)
    remarks = models.CharField(max_length=200, null=True)

    def __str__(self):
        return self.amount

What I want to get is a list of all Orders along with the Sum(total) from Item table and Sum(amount) from Payment table
I am unable to figure out how to write the code in django ORM:
I have written something like this but the summation results are wrong:

order_list = Order.objects.values('id').annotate(
        amount_paid=Sum('payment__amount'),
        order_value=Sum('item__total')
        ).order_by('-id')

MySql query is as follows:

 select app_order.id,
 	(select sum(app_payment.amount) from app_payment
 		where app_order.id=app_payment.order_id) as total_amount_paid,
 	(select sum(app_item.total) from app_item
 		where app_order.id=app_item.order_id) as total_order_amount
 from app_order;
2

The feature you’re looking for is Aggregation. In particular, see the examples in the sections on Joins and aggregates.

4

I checked the docs and this is what i tried:

order_list = Order.objects.values('id').annotate(
        amount_paid=Sum('payment__amount'),
        order_value=Sum('item__total')
        ).order_by('-id')

The summation result are incorrect. Kindly help me figure out where I am doing it wrong.

The item table has three rows with same order_id and the sum('item__total')result I’m getting is Decimal(‘55633.60’)

+----+-------------+--------+---------------+-----------------+---------------+----------+----------+-------------+
| id | description | weight | rate_per_gram | charge_per_gram | making_charge | total    | order_id | item_name   |
+----+-------------+--------+---------------+-----------------+---------------+----------+----------+-------------+
|  1 | 918 BS      | 10.980 |       1290.00 |          245.00 |       2690.10 | 16854.30 |       47 | Gents Ring  |
|  2 | 924 BIS     |  2.890 |       1290.00 |          544.00 |       1572.16 |  5300.26 |       47 | Ladies Ring |
|  3 | 924 BIS     |  3.440 |       1290.00 |          356.00 |       1224.64 |  5662.24 |       47 | Gents Ring  |
+----+-------------+--------+---------------+-----------------+---------------+----------+----------+-------------+

The payment table has two rows with same order_id and the sum('payment__amount')result I’m getting is Decimal(‘45000.00’)

+----+----------+-----------+----------+------------+------------+
| id | amount   | remarks   | order_id | created_at | updated_at |
+----+----------+-----------+----------+------------+------------+
|  1 | 10000.00 | UNCLEARED |       47 | 2021-05-16 | 2021-05-16 |
|  2 |  5000.00 | Cash      |       47 | 2021-05-16 | 2021-05-16 |
+----+----------+-----------+----------+------------+------------+
5

Those totals you’re seeing, 55633.60 for Item and 45000 for Payment, are they the complete sums for those tables? (Implying that the summation is not filtering by id.)

I would initially suggest removing the values clause.

7

No, This is the only data that I have in those tables. What I’m able to figure out is that the Sum(total) of item is multiplied by the row count of the payment table i.e. 2 and the Sum(amount) of payment table is multiplied by the row count of the item table i.e. 3 having same order_id.

I tried removing the values clause

order_list = Order.objects.annotate(
        amount_paid=Sum('payment__amount'),
        order_value=Sum('item__total')
        ).order_by('-id')
    for order in order_list:
        amount_paid=order.amount_paid
        order_value=order.order_value
        print(amount_paid, order_value)

but the results are same:
45000.00 55633.60

8

See the section Combining multiple aggregations, and if you want to see a lot of background information on this issue, see Aggregation | Django documentation | Django

So with the ability to code Subqueries, you could code this as annotating your query with the sum of a subquery.

Kinda doing this off the cuff, I’d give something like this a try (I’d say the chance that this works is about 50-50):

total_query = Subquery(Item.objects.filter(order=OuterRef('id')).values('total'))
amount_query = Subquery(Payment.objects.filter(order=OuterRef('id')).values('amount'))
order_list = Order.objects.annotate(amount_paid=Sum(amount_query), order_value=Sum(total_query))