CODEWITHSUNDEEP
pythonmysqldjangodjango-modelsorm
vinay
vinay

Reputation: 33

Optimize Query in django

I want to optimize the looping code using django orm as it is taking 41 queries because of the loop was shown in django-debug-toolbar, I have stated my models with the django ORM and there is a loop where i need to optimize using ORM approach so that i can avoid a loop. 

class Bills(models.Model):
    library=models.ForeignKey(Library,null=True)
    customer=models.ForeignKey(Customer, null=True)
    total_price = models.FloatField()
    History = '1'
    Physics = '2'
    Maths = '3'
    Book_Category=(
        (History,'History'),
        (Physics,'Physics'),
        (Maths,'Maths')
    )
    book_category_type=models.CharField(max_length=2,choices=Book_Category)

This is the Bill model storing the all the bills of specific customer.

class LibraryOrder(models.Model):
   hotel=models.ForeignKey(Hotel,null=True)
   library = models.ForeignKey(Library,null=True)
   customer=models.ForeignKey(Customer,null=True)
   library_item = models.ForeignKey(LibraryItem,null=True)
   quantity = models.FloatField()
   total_price = models.FloatField()
   comments=models.TextField(null=True)
   bill=models.ForeignKey(Bills,null=True)
   ORDER_STATUS = (
      (NOT_PROCESSED, 'NotProcessed'),
      (PROCESSING, 'Processing'),
      (PROCESSED,'processed'),
   )
   order_status = models.CharField(max_length=3, choices=ORDER_STATUS, default=NOT_PROCESSED)

This is the Order model when some customer orders for some book in the library item.

Right now i am using this :

customers = Customer.objects.filter(library="1").exclude(customer_status='2')
bills = Bills.objects.filter(library="1", customer=customers, book_category_type="1")
for bill in bills:
    # if bill.order_type == "1":
    not_processed_order = LibraryOrder.objects.filter(bill=bill, order_status="1")
    notprocessed_lists.append(not_processed_order)
    processing_order = LibraryOrder.objects.filter(bill=bill, order_status="2")
    processing_lists.append(processing_order)
    processed_order = LibraryOrder.objects.filter(bill=bill, order_status="3")
    processed_lists.append(processed_order)

The bills which is getting looped is the loop where i am getting the array of bills by that orm, as in library order i have bill as foreign key i am using to get the order details and pushing to the array for displaying in the html.

I want to optimize the django orm approach which i have specified to two lines into single line, Those lists are separate as it is displaying in the separate tabs in html

Upvotes: 1

Views: 512

Answers (2)

Aamish Baloch
Aamish Baloch

Reputation: 1200

not_processed_order = LibraryOrder.objects.filter(bill__library="1", bill__book_category_type="1", order_status="1", bill__customer__library='1').exclude(bill__customer__status='2')
processing_order = LibraryOrder.objects.filter(bill__library="1", bill__book_category_type="1", order_status="2", bill__customer__library='1').exclude(bill__customer__status='2')
processed_order = LibraryOrder.objects.filter(bill__library="1", bill__book_category_type="1", order_status="3", bill__customer__library='1').exclude(bill__customer__status='2')

Upvotes: 4

Todor
Todor

Reputation: 16050

In this specific scenario you can use prefetch_related with custom Prefetch object/s to prefetch the necessary data and not make any queries inside the loop.

customers = Customer.objects.filter(library="1").exclude(customer_status='2')
bills = Bills.objects.filter(library="1", customer=customers, book_category_type="1").prefetch_related(
        Prefetch('libraryorder_set', queryset=LibraryOrder.objects.filter(order_status="1"), to_attr='not_processed_orders'),
        Prefetch('libraryorder_set', queryset=LibraryOrder.objects.filter(order_status="2"), to_attr='processing_orders'),
        Prefetch('libraryorder_set', queryset=LibraryOrder.objects.filter(order_status="3"), to_attr='processed_orders'),
    )

for bill in bills:
    notprocessed_lists.append(bill.not_processed_orders)
    processing_lists.append(bill.processing_orders)
    processed_lists.append(bill.processed_orders)

This way you will have 3 queries (1 query per prefetch object) instead of 40+.

You can optimize if further to 1 query, but you will have to do some more work in the python code:

customers = Customer.objects.filter(library="1").exclude(customer_status='2')
bills = Bills.objects.filter(library="1", customer=customers, book_category_type="1").prefetch_related('libraryorder_set')

for bill in bills:
    not_processed_orders = []
    processing_orders = []
    processed_orders = []
    for order in bill.libraryorder_set.all():
        if order.status == '1':
            not_processed_orders.append(order)
        elif order_status == '2':
            processing_orders.append(order)
        elif order_status == '3':
            processed_orders.append(order_status)
    notprocessed_lists.append(bill.not_processed_orders)
    processing_lists.append(bill.processing_orders)
    processed_lists.append(bill.processed_orders)

However in order to lean how to fish, my advice it to take a look at the following articles from the docs:

Upvotes: 0

Related Questions