Django -Join 3 model in query

user_13967452 0
Viewed 60
class Item(models.Model):
    CATEGORY = (
        ('Gudang Kering', 'Gudang Kering'),
        ('Gudang Basah','Gudang Basah'),
        )
    name = models.CharField(max_length=200,null= True)
    stock = models.IntegerField(default='0', blank=False, null=True)
    category = models.CharField(max_length=200,null= True,choices=CATEGORY)
    description = models.CharField(max_length=200,null= True, blank= True)
    date_created = models.DateTimeField(auto_now_add= True)
    tags = models.ManyToManyField(Tag)

class Issue(models.Model):
    STATUS = (
        ('Pending', 'Pending'),
        ('Granted','Granted'),
        ('Denied','Denied'),
        )
    customer = models.ForeignKey(Customer, null=True, on_delete= models.SET_NULL)
    item = models.ForeignKey(Item, null=True, on_delete= models.SET_NULL)
    quantity = models.IntegerField(default='0', blank=False, null=True)
    date_created = models.DateTimeField(auto_now_add=True, auto_now=False)
    status = models.CharField(max_length=200,null= True, choices=STATUS)


class Receive(models.Model):
    STATUS = (
        ('Pending', 'Pending'),
        ('Granted','Granted'),
        ('Denied','Denied'),
        )
    customer = models.ForeignKey(Customer, null=True, on_delete= models.SET_NULL)
    item = models.ForeignKey(Item, null=True, on_delete= models.SET_NULL)
    quantity = models.IntegerField(default='0', blank=False, null=True)
    date_created = models.DateTimeField(auto_now_add=True, auto_now=False)
    status = models.CharField(max_length=200,null= True, choices=STATUS)

"Hello,sorry im trying to learn django and want to ask,how to write a query to calculate objects where if the 2 have the same item and status granted ,it will calculate stock - quantity(issue) + quantity(receive), i want to save the value to be new value of stock in database"

django-queryset django
2 Answers
  • Get the object of Item() and Customer() first as they are set as Foreign Keys in Issue()
  • Create an object of Issue() and assign corresponding values to fields
  • To update the Item.stock use the Issue.quantity and Item.stockvalues which already we have
  • Save the instances
# c_id and i_id should be fetched from post or any other way
customer_obj = Customer.objects.get(id=c_id)
item_obj = Item.objects.get(id=i_id)

# create an object of Issue and assign values
issue_obj = Issue()
issue_obj.customer = customer_obj 
issue_obj.item = item_obj
# like so assign values to other fields also and save Issue object
issue_obj.save()

# calculate stock and save the Item object
item_obj.stock = item_obj.stock - issue_obj.quantity
item_obj.save()

When Foreign Key is used, the object of the model class should be assigned to that field. eg: customer = models.ForeignKey(Customer, null=True, on_delete= models.SET_NULL) is a field in Issue(). Inorder to save the instance of Issue() we need to assign the object of Customer() to customer field of Issue object.

user_133178580

There is no need to store same information twice. It will make this solution more confusing. You can add these values in runtime. For example:

Solution One: You can just annotate the value of quantity in the queryset directly. No need to store that additional information in the stock attribute of Item model. For example:

from django.db.models import Q, F, Sum

items = Item.objects.annotate(issue_q=Sum('issue__quantity', filter=Q(issue__status="GRANTED")).annotate(total=F('stock')-F('issue_q'))

for item in items:
   print(item.total)

Solution Two: add a property method to display total value:

class Item(models.Model):
   ...

   @property
   def total(self):
      return self.stock - self.item_set.filter(status="GRANTED").aggregate(quantity_s=Sum('quantity'))['quantity_s']


for item in Item.objects.all():
   print(item.total)

BTW, this solutions is less efficient then first, as it makes multiple queries to DB.

user_26961650
Related