Django ORM: Efficient Querying with Query Expressions

Django’s ORM is easy to learn, intuitive and covers many use cases. Still, there are several things it does not do well. What happens then is that, after the queryset is returned, we begin processing more and more data in Python. This is not good because every database manages and transforms data faster than Python. Instead of working data with Python, we always try to use Django’s advanced query tools to do the lifting. By doing that, we not only benefit from increased performance, but we also enjoy using more proven code than any Python-based workarounds we create.

When performing reads on a database, query expressions can be used to create values or computations during that read. If that sounds confusing, don’t feel alone, I’m confused too. For more clarity, first of all, let us see one example:

#Don't do this!
from models.customers import Customer

customers = []
for customer in Customer.objects.iterator():
  if customer.scoops_ordered > customer.store_visits:
    customers.append(customer)

We should not iterate like the above because:

It uses Python to loop through all the Customer records in the database, one by one. This is slow and memory consuming. Under any volume of use, it will generate race conditions. This occurs when we’re running the script while customers interact with the data. While probably not an issue in this simple ‘READ’ example, in real-world code combining that with an ‘UPDATE’ can lead to loss of data. For the right way, Django provides a way to make this more efficient and race-condition free. So we can:

from django.db.models import F
from models.customers import Customer

customers = Customer.objects.filter(scoops_ordered__gt=F('store_visits'))

What this does is use the database itself to perform the comparison. Under the hood, Django is running something that probably looks like this:

SELECT * from customers_customer where scoops_ordered > store_visits