Django ORM Query or raw SQL Query

Question

I need to make a query, where I can display the total amount of clothes ordered by clients, the result should be like this:

models.py

class Cloth(models.Model):
    description = models.CharField(max_length=30)
    type = models.CharField(max_length=2)

class Order(models.Model):
    client = models.ForeignKey(Client, on_delete=models.CASCADE)
    date = models.DateTimeField(auto_now_add=True)

class Order_Detail(models.Model):
    order = models.ForeignKey(Order, on_delete=models.CASCADE)
    cloth = models.ForeignKey(Cloth, on_delete=models.CASCADE)
    weight = models.FloatField()

The closest that I got was:

Order_Detail.objects.values('order__client__name','cloth__type').annotate(Sum('weight'))

The problem with this is that it will retrieve an object for each cloth type:

Client_1 | Cloth_Type_X | SUM_X
Client_1 | Cloth_Type_Y | SUM_Y
Client_1 | Cloth_Type_Z | SUM_Z
Client_2 | Cloth_Type_X | SUM_X
Client_2 | Cloth_Type_Y | SUM_Y
Client_2 | Cloth_Type_Z | SUM_Z
Client_3 | Cloth_Type_X | SUM_X
Client_3 | Cloth_Type_Y | SUM_Y
Client_3 | Cloth_Type_Z | SUM_Z

Isn't there a better approach?

Yes, but not only the total weight, but the SUM of the weights for each cloth type. — victim
– victim, Commented Dec 1, 2017 at 6:24

Satendra · Accepted Answer · 2017-12-01 07:13:23Z

1

You can use Conditional Expressions for that purpose

Order_Detail.objects.values('order__client').annotate(
   cloth_type_x_sum = Sum(Case(When(cloth__type=X, then='weight'))), 
   cloth_type_y_sum = Sum(Case(When(cloth__type=Y, then='weight'))),
   cloth_type_z_sum = Sum(Case(When(cloth__type=Z, then='weight'))),
).order_by('order__client')

answered Dec 1, 2017 at 7:13

Satendra

6,8554 gold badges29 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

victim Over a year ago

Excellent, this is exactly what I needed! Thanks bro!

Sandeep Balagopal · Accepted Answer · 2017-12-01 06:49:21Z

0

from django.db.models import Sum
Order_Detail.objects.values('cloth').annotate(cloth_weight=Sum('weight'))

This will give you grouped by cloth type.

Order_Detail.objects.values('order__client').annotate(cloth_weight=Sum('weight'))

This will give yougrouped by client.

edited Dec 1, 2017 at 6:49

answered Dec 1, 2017 at 6:32

Sandeep Balagopal

2,00320 silver badges28 bronze badges

1 Comment

victim Over a year ago

Yes, but in different object values, I need to have it in the same object result. The result should be like this: client_1 | cloth_type_x_sum | cloth_type_Y_sum | cloth_type_z_sum | sum_total

Collectives™ on Stack Overflow

Django ORM Query or raw SQL Query

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related