How to write a Django ORM pre-request
To keep things simple, let's say I have StockIOLog models.
class StockIOLog(models.Model):
pid = models.IntegerField()
name= models.CharField(max_length=50)
type= models.IntegerField()
stock = models.IntegerField()
It contains the following data:
pid | name | batch | type | quantity
------------------------------------------------
1 | Napa | AB | 0 | 100
------------------------------------------------
1 | Napa | AA | 0 | 100
------------------------------------------------
2 | Amod | AA | 0 | 100
------------------------------------------------
2 | Amod | CA | 0 | 100
------------------------------------------------
1 | Napa | AB | 1 | 10
------------------------------------------------
1 | Napa | AB | 1 | 5
------------------------------------------------
1 | Napa | AA | 1 | 20
------------------------------------------------
2 | Amod | AA | 1 | 10
------------------------------------------------
2 | Amod | AA | 1 | 50
------------------------------------------------
2 | Amod | CA | 1 | 5
------------------------------------------------
2 | Amod | CA | 1 | 15
type 0 means the product was purchased, type 1 means the product was consumed. Now I want to calculate the total stock of each product in order.
By running the following SQL query
SELECT pid, name, batch, SUM(in) - SUM(out) as stock FROM (
SELECT pid, name, type SUM(quantity) as in, 0 as out from `qset` WHERE type=0 GROUP BY pid,type,batch as a
UNION
SELECT pid, name, type 0 as in, SUM(quantity) as out from `qset` WHERE type=1 GROUP BY pid,type,batch as b
) ac table_a
I am getting the following request
pid | name | batch | stock
-----------------------------------
1 | Napa | AB | 85
-----------------------------------
1 | Napa | AA | 80
-----------------------------------
2 | Amod | AA | 40
-----------------------------------
2 | Amod | CA | 80
How to do similar things in django ORM?
+3
source to share
1 answer
This may not be the best answer, but you can get purchased
it consumed
in the dictionary as well by doing the following query
from django.db.models import When, F, IntegerField, Sum, Count
from .models import StockIOLog
values = (StockIOLog.objects.all().values("pid", "name", "batch").annotate(
purchased=Sum(Case(When(type=1, then=F("stock")),
output_filed=IntegerField())),
consumed=Sum(Case(When(type=0, then=F("stock")),
output_field=IntegerField())))
)
0
source to share