Django: multply, sum and groupby using two models

52 Views Asked by At

Good day, I am trying to build a system that creates an order and allows a user to add pieces. Here are the two models:

class Order(models.Model):

    order_slug = models.SlugField(unique=True, blank=True)
    machine = models.ForeignKey(Machine, blank=True,on_delete=models.DO_NOTHING)
    production_type = models.CharField(choices=PRODUCTION_TYPE_CHOICES, max_length=30)
    production_bond = models.CharField(choices=PRODUCTION_BOND_CHOICES, max_length=50)
    profile = models.CharField(choices=PROFILE_CHOICES, max_length=50)
    order_number = models.CharField(max_length=50)
    order_colour = models.CharField(choices=ORDER_COLOUR_CHOICES, max_length=50)
    order_finish = models.CharField(choices=ORDER_FINISH_CHOICES, max_length=10)
    order_gauge  = models.IntegerField(choices=ORDER_GAUGE_CHOICES)
    order_width   = models.IntegerField()
    date_received = models.DateTimeField(default=datetime.now)

    class Meta: 
        ordering = ["date_received", "-profile"]

    def __str__(self):
        return (str(self.order_slug))


   
class Piece(models.Model):
    
    order = models.ForeignKey(Order,on_delete=models.CASCADE)
    coil = models.ForeignKey(Cut_Material ,on_delete=models.CASCADE)
    piece_length = models.DecimalField(max_digits=4, decimal_places=2)
    prime_pieces = models.IntegerField()
    reject_pieces = models.IntegerField()
    coil_constant = models.IntegerField(blank=True)

    def __str__(self):
        return (str(self.piece_length))

    class Meta:
        ordering = ["-order","piece_length"]

Here is what I want to do.

  1. Multiply the prime_pieces by the piece_length to get running_meters for each column
  2. I want to group the pieces by order_gauge, order_number, and sum the running_meters.

Proposed solution

piece = Piece.objects.all()
order = Order.objects.all()

For step 1

rm_data =  Piece.objects.annotate(running_meters=ExpressionWrapper(F('piece_length') * F('prime_pieces'), output_field=FloatField())).values()

For step 2

I am not sure

Can someone help me please?

1

There are 1 best solutions below

0
On

I have tried this and it seems to work after scratching my head:

order.values('order_number','order_gauge').annotate(
running_meters=Sum(ExpressionWrapper(F('piece__piece_length')*F('piece__prime_pieces'),output_field=FloatField())))