Django aggregate sum on child model field

user_16633726 0
Viewed 137

Consider the following models:

from django.db import models
from django.db.models import Sum
from decimal import *

class Supply(models.Model):
    """Addition of new batches to stock"""
    bottles_number = models.PositiveSmallIntegerField(
    bottles_remaining = models.DecimalField(max_digits=4, decimal_places=1, default=0.0)

    def remain(self, *args, **kwargs):
        used = Pick.objects.filter(supply=self).aggregate(
                                   total=Sum(Pick.n_bottles))[bottles_used__sum]
        left = self.bottles_number - used
        return left

    def save(self, *args, **kwargs):
        self.bottles_remaining = self.remain()
        super(Supply, self).save(*args, **kwargs)

class Pick(models.Model):
    """ Removals from specific stock batch """
    supply = models.ForeignKey(Supply, on_delete = models.CASCADE)
    n_bottles = models.DecimalField(max_digits=4, decimal_places=1)

Every time an item (bottles in this case) is used, I need to update the "bottles_remaining" field to show the current number in stock. I do know that best practice is normally to avoid storing in the database values that can be calculated on the fly, but I need to do so in order to have the data available for use outside of Django.

This is part of a stock management system originally built in PHP through Xataface. Not being a trained programmer, I managed to get most of it done by googling, but now I am totally stuck on this key feature. The remain() function is probably a total mess. Any pointers as to how to perform that calculation and extract the value would be greatly appreciated.

aggregate django python
1 Answers

Not sure what you actually want to solve and what is exact problem.

Possible solutions are

  1. Use SQL View
CREATE VIEW bottles_extended AS
SELECT id, total_number, used, (total_number - used) as remaining
FROM bottles;

After that you may simply get data as select total_number, used, remaining from bottles_extended on PHP side

  1. Add (total_number - used) as remaining column directly in PHP SQL query

  2. Current Django solution to update field automatically on save looks not perfect but also should be working solution (in addition you may add serializers and set serializer's remaining field as read only. This will prevent to change value by user manually)

user_139462040
Related