How to build a Django QuerySet to check conditions on two manyToMany fields

user_5877122 0
Viewed 29

I have the following models (simplified):

class Resource(models.Model):
    name = models.CharField(max_length=64, unique=True)  

class ResourceFlow(models.Model):
    resource = models.ForeignKey(Resource, related_name="flow")
    amount = models.IntegerField()

class Workflow(models.Model):    
    inputs = models.ManyToManyField(ResourceFlow, related_name="workflow")

class Stock(models):
    resource = models.ForeignKey(Resource, related_name="stock")
    amount = models.IntegerField()    

class Producer(models.Model):   
    workflow = models.ForeignKey(Workflow, related_name="location")
    stocks = models.ManyToManyField(Stock, related_name="location")

I would like to test with computation done by the the DB engine if I can start a production.

A production can start if I have enough stock: for my Producer's workflow, all inputs ResourcesFlow amount have to be present in the Producer'stocks

So the queryset might be one those result:

  • for a given producer return all stocked resources that do not fulfill Workflow inputs amounts conditions
  • for a given producer return inputs resources needed for the workflow that are not in sufficient quantity in its stocks

It is possible to do that in Django? And if yes how to do it?

django-queryset django python
1 Answers

Not sure if you've found the answer but anyways, hope I understand your question correctly.

Let's assume we have the following resources:

head = Resource.objects.create(name="head")
neck = Resource.objects.create(name="neck")
body = Resource.objects.create(name="body")
arm = Resource.objects.create(name="arm")
leg = Resource.objects.create(name="leg")

And we have a build_a_robot workflow:

build_a_robot = Workflow.objects.create()
build_a_robot.inputs.add(ResourceFlow.objects.create(resource=head, amount=1))
build_a_robot.inputs.add(ResourceFlow.objects.create(resource=neck, amount=1))
build_a_robot.inputs.add(ResourceFlow.objects.create(resource=body, amount=1))
build_a_robot.inputs.add(ResourceFlow.objects.create(resource=arm, amount=2))
build_a_robot.inputs.add(ResourceFlow.objects.create(resource=leg, amount=2))

And finally, we have a producer:

producer = Producer.objects.create(workflow=build_a_robot)
producer.stocks.add(Stock.objects.create(resource=head, amount=0))
producer.stocks.add(Stock.objects.create(resource=neck, amount=3))
producer.stocks.add(Stock.objects.create(resource=body, amount=1))
producer.stocks.add(Stock.objects.create(resource=arm, amount=10))
producer.stocks.add(Stock.objects.create(resource=leg, amount=1))

We want to find the list of resources that we have run out of to build a robot given producer.

I think here's one way to do it:

from django.db.models import OuterRef, Subquery

required_resources = ResourceFlow.objects.filter(pk__in=producer.workflow.inputs.values("pk")).values("resource")
required_amount = producer.workflow.inputs.filter(resource=OuterRef("resource")).values("amount")[:1]
missing_stocks = Stock.objects.filter(resource_id__in=required_resources).filter(amount__lt=required_amount)

In this example, missing_stocks will be equal to:

<QuerySet [<Stock: Stock [Resource [head], 0]>, <Stock: Stock [Resource [leg], 1]>]>

So, we need more head and leg to build a robot.

user_196144700
Related