CODEWITHSUNDEEP
mysqlsqldjango
Praful Bagai
Praful Bagai

Reputation: 17382

Optimizing the Django-SQL Query

I've 3 models.

class ShipmentPPTLMapping(models.Model):
    pptl_id = models.CharField(max_length = 255)
    shipment_id = models.ForeignKey('Shipment', related_name = 'pptls')

class ShipmentBagSealMapping(models.Model):
    bag_seal = models.CharField(max_length = 255)
    status = models.CharField(max_length = 255, default = 'open')
    shipment_id = models.ForeignKey('Shipment', related_name = 'bags')



class Shipment(models.Model):
    job_id = models.CharField(max_length = 255)

I need to write a query which filters out the status of those bagsfrom ShipmentBagSealMapping which are closed, provided the pptl_id from ShipmentPPTLMapping.

I've a pptl_id at the start. With that I filters out all the shipments related to that, since shipments_obj is related to ShipmentBagSealMapping , so using shipments_obj is the only way I can filter out the closed bags.

shipment_pptl_mapping_obj_list = ShipmentPPTLMapping.objects.filter(pptl_id = pptl_id)

Then I loop around the shipment_pptl_mapping_obj_list to find the bags with status closed.

for shipment_pptl_mapping_obj in shipment_pptl_mapping_obj_list:
    closed_bags = shipment_pptl_mapping_obj.shipment_id.bags.filter(status = 'closed')
    #and then again another loop
    for bags in closed_bags:
        #something

How can I find do everything just in 1 query?

Here's my final code

shipment_pptl_mapping_obj_list = ShipmentPPTLMapping.objects.filter(pptl_id = pptl_id)  #--> extend this
for shipment_pptl_mapping_obj in shipment_pptl_mapping_obj_list:
    closed_bags = shipment_pptl_mapping_obj.shipment_id.bags.filter(status = 'closed')
    for bags in closed_bags:
        #do something

I want something like this

closed_bags = Some Query
for bags in closed_bags:
    #do_something

Upvotes: 0

Views: 54

Answers (1)

Andrey Nelubin
Andrey Nelubin

Reputation: 3294

At first correct your models, foreign key's field names should not contains _id in end. That will cause many problems and disappointments later

class ShipmentPPTLMapping(models.Model):
    pptl_id = models.IntegerField() # if you id is Integer of course
    shipment = models.ForeignKey('Shipment', related_name = 'pptls')

class ShipmentBagSealMapping(models.Model):
    bag_seal = models.CharField(max_length = 255)
    status = models.CharField(max_length = 255, default = 'open')
    shipment = models.ForeignKey('Shipment', related_name = 'bags')



class Shipment(models.Model):
    job_id = models.IntegerField(max_length = 255)

Second

closed_bags = Shipment.objects.filter(pptls__pptl_id__exact=pptl_id, status='closed')

This should solve your question

Upvotes: 1

Related Questions