Count & Sum of Order Values for each customer (through iteration) in Django

user_16232521 0
Viewed 480

I have Customer & Order models as below:

class Customer(models.Model):
    name = models.CharField(max_length = 100)
    city = models.CharField(max_length = 100)

class Order(models.Model):
    value = models.FloatField()
    customer = models.ForeignKey(Customer, on_delete=models.CASCADE)

Now I would like to generate a table of (distinct) customers along with a count of number of orders placed by each of them and the sum of values of those orders. I tried this in the views.py:

def customers(request):
    customer_orders = Order.objects.distinct().annotate(Sum('value'))

Then in my html template, I tried the following:

<ul>
{% for customer in customer_orders %}
    <li>{{customer.customer}} - {{customer.value__sum}}<li>
{% endfor %}
</ul>

After all this, instead of getting unique customers (and respective order records), I'm getting a list of all orders and customers are getting repeated (as shown below). Not sure what I'm missing here.

Bosco-Ward - 16,700.0 Ernser PLC - 51,200.0 Murphy Ltd - 21,400.0 Kohler-Veum - 29,200.0 Schmidt-Legros - 96,800.0 Brown-Weissnat - 8,200.0 Bosco-Ward - 36,400.0 Ernser PLC - 66,600.0 Murphy Ltd - 84,200.0

Also wanted to know if there's a possibility to generate a table of city names with order count and total value of orders received from that city (note that my order model doesn't have city field).

for-loop django sql python
3 Answers

Since you want a queryset of Customer instances make your query on the Customer model itself instead of on Order, next I believe you will not need to use distinct here since the customer instances should be considered unique. Hence, you can make a query like:

from django.db.models import Count, Sum


customers = Customer.objects.annotate(order_count=Count('order'), order_value_sum=Sum('order__value'))


for customer in customers:
    print(customer.name, customer.order_count, customer.order_value_sum)
user_149918640

Kindly note im typing the solution from my phone without testing it but this is what i think:

  1. Give a related name to customer in Order model:

customer = models.ForeignKey(Customer, on_delete=models.CASCADE, related_name=“orders”)

  1. Get orders count:

Customer.objects.get(id=1).orders.count()

Or access this attribute in template something like:

{{ customer.orders.count }}

  1. Get orders count values by city

From django.db.models import Count, Sum

Customer.objects.values(“city”).annotate(order_count=Count(“orders”)).annotate(totals=Sum(“value”))

user_133436950

Add realted_name to Order customer field:

class Customer(models.Model):
    name = models.CharField(max_length = 100)
    city = models.CharField(max_length = 100)

class Order(models.Model):
    value = models.FloatField()
    customer = models.ForeignKey(Customer, on_delete=models.CASCADE, related_name='orders')

query:

Customer.objects.values(“city”).annotate(order_count=Count(“orders”)).annotate(totals=Sum(“orders__value”))
user_112820770
Related