CODEWITHSUNDEEP
sqldjangodjango-modelssubquerydjango-queryset
victim
victim

Reputation: 65

Django ORM Query or raw SQL Query

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

Client_1 | Cloth_Type_X_SUM | Cloth_Type_Y_SUM | Cloth_Type_Z_SUM | SUM_ALL Client_2 | Cloth_Type_X_SUM | Cloth_Type_Y_SUM | Cloth_Type_Z_SUM | SUM_ALL Client_3 | Cloth_Type_X_SUM | Cloth_Type_Y_SUM | Cloth_Type_Z_SUM | SUM_ALL

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?

Upvotes: 0

Views: 512

Answers (2)

Satendra
Satendra

Reputation: 6865

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')

Upvotes: 1

Sandeep Balagopal
Sandeep Balagopal

Reputation: 1983

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.

Upvotes: 0

Related Questions