Query a model via another model in Django

Question

I have a model named Demand in my app

class DemandFlows(models.Model):
 
    kit = models.ForeignKey(Kit, on_delete=models.CASCADE)
    
class Demand(models.Model):

    demand_flows = models.ManyToManyField(DemandFlows)
    delivery_month = models.DateTimeField(blank=True, null=True)

From this model I get the month and year and the kits used in that month like the following:

    d_o = Demand.objects.all()
    for i in d_o:
        print("month", i.delivery_month)
        m = i.delivery_month
        print("m", m.month)
        print("y", m.year)
        for k in i.demand_flows.all():
            print("k", k.kit.pk)

Now I have another model Allotment, How can I query this model such that I get the kit and the sum of alloted_quantity of the particular month I got by Demand model?

Allotment:

class AllotmentFlow(models.Model):

    kit = models.ForeignKey(Kit, on_delete=models.CASCADE)
    alloted_quantity = models.IntegerField(default=0)


class Allotment(models.Model):

 
    transaction_no = models.IntegerField(default=0)
    dispatch_date = models.DateTimeField(default=datetime.now)
    flows = models.ManyToManyField(AllotmentFlow)

For e.g.

I got the following from demand:

m 12
y 2020
k 3
k 4
k 5
k 7
k 8
k 9
k 10

So how can I know the alloted_quantity of kit 3,4,5,7,8,9,10 in December, 2020?

Kit Model:

class Kit(models.Model):
   
    owner = models.ForeignKey(User, on_delete=models.CASCADE)
    kit_name = models.CharField(max_length=500, default=0)
    kit_info = models.CharField(max_length=500, default=0)
    part_name = models.CharField(max_length=500, default=0)

user_2696165 · Answer

I think you can use GroupBy functionality here:

from django.db.models import Sum

Demand.objects.values('delivery_month__year', 'delivery_month__month').annotate(allotment_qty=Sum('demand_flows__kit__allotmentflow__ alloted_quantity')).values('delivery_month__year', 'delivery_month__month', 'allotment_qty')

More information can be found in values() documentation.

user_6562458 · Answer

Give this a try, This will give you alloted_quantity of Kits for a given date.

for k in i.demand_flows.all():
    allotment_flow = AllotmentFlow.objects.filter(
        kit=k.id,
        allotment__dispatch_date__month=m.month,
        allotment__dispatch_date__year=m.year,
    )
    print('qty',allotment_flow[0].alloted_quantity)

user_13940715 · Answer

for demand in Demand.objects.all(): # We cycle through demands

    # We print the relevant info
    print(f"Month : {demand.delivery_month}")
    print(f"M : {demand.delivery_month.month}")
    print(f"Y : {demand.delivery_month.year}")


    total_allotted_quantity = 0  # We initialize the variable that we'll use to sum the month's allotted quantities

    for demand_flow in demand.demand_flow.all():  # We cycle through the kit ids of the selected month
        partial_allotted_quantity = 0
        for allotment_flow in  AllotmentFlow.objects.filter(kit=Kit.objects.get(id=demand_flow.kit.id)): # Returns a list of AllotmentFlow elements that are associated with that specific Kit
            partial_allotted_quantity += allotment_flow.alloted_quantity
        total_allotted_quantity += partial_allotted_quantity
    print(total_allotted_quantity)

Query a model via another model in Django

3 Answers