Django Orm Queries Tutorial

Since Django makes it easier to develop nicely designed web applications easily, by understanding certain methodologies we can make our project development simple and easier.

In this tutorial, I’ll explain how to write efficient django queries and to optimise our codebase in django way!

Examples are defined on the basis of follwing models (I’ve omitted certain fields and parameters to keep the tutorial short and precise):

class Product(models.Model):
    name = models.CharField(max_length=64)
    description = models.CharField(max_length=1024)
    quatity = models.IntegerField()
    seller = models.CharField()

class Item(models.Model):
    product = models.ManyToManyField(Product, related_name='item_product')
    quantity = models.IntegerField()

class Cart(models.Model):
    item = models.ManyToManyField(Item, related_name='cart_item')
    user = models.ForeignKey(User)

While querying, to fetch values from a foreign key table, .values() or .values_list() can be used. For example, to fetch quantity values of Item whose name is null, while querying from Cart, following can be used:

Cart.objects.values('item__quantity').filter( item__product__name__isnull = True  )

The results returned by filter() can be more optimised by using methods like values_list(), values(), defer(), only() & exclude(). For slicing a queryset following method can be used:

Product.objects.all()[5:]

This slicing is django’s internal method which is different from python’s slicing method and works on database level. But, slicing doesn’t works while we use -ve indexing like [-5:]. In that case we can use reverse() operator in following way:

Product.objects.all().reverse()[5:]

If we want to check if entries are present in a queryset, using exists() is more efficient than using count()>0.

Product.objects.filter(name='test product').exists()

To iterate all the items in the cart, we can use .iterator(), which get query results one-by-one unlike a queryset which caches everything in the memory:

for item in Cart.objects.prefetch_related('item').iterator():

In this case, prefetch_related() fetches all the fields from item and stores it in the cache, and hence reduces the lookup to the database for every foreign key in every iteration.

A similar method select_related() can also be used in case non Many to Many relationships. More detailed explaination for both the methods can be found here and here.

Bulk Operations

For updating multiple rows, Query for bulk updating the fields can be used:

Product.objects.filter( name__isnull=True ).update( name='unknown' )

Similarly, bulk_create() can be used in following way:

Product.objects.bulk_create([
    Product( name='test_product1', description='test description', quantity=5 ),
    Product( name='test_product2', description='test description', quantity=5 ),
])

The F() operator

This operator is used to access fields from models(similar to getattr()) and some manipulation of that field itself is required.

Item.objects.filter( quantity__gte=5 ).update( quantity=F('quantity')*2 )

This query doubles the quantity of products with quantities greater than or equals to 5.

Aggregate queries

When we want retrieve details from multiple rows simultaneously, aggregate() can be used:

Product.objects.aggregate( products_count=Sum('quantity') )

This query will return Sum of all the quantity of products.

Hammer of the Thor: Django Annotate Query

annotate() is one of the most powerful query in django and have several versatile use cases. In general, annotate() works as a GROUP BY operator in sql. To query Number of Products sold by every seller, we can query like:

Products.objects.values('seller').annotate( products_sold_by_seller=Count('seller') )

For cases in which we may require WHEN conditions or IF conditions, we can use annotate() in the following way:

Products.objects.annotate(
    quantity_sum_square_of_quantity_greater_than_five = Sum(Case(
        When( quantity__gt=5,
            then = ExpressionWrapper(
                new_quantity = F('quantity')*F('quantity'),
                output_field = IntegerField()
            )),
        default = 0,
        output_field=IntegerField()
    ))
).values_list('quantity_sum_square_of_quantity_greater_than_five', flat=True)

This returns the square of quantity sum of products with quantity greater than five.

Products.objects.values('quantity', 'name').annotate( sum_of_quantities_of_products_with_same_name=Count(F('quantity')) )

The simple query above performs very powerful operation. It matches all the products of same name and return sum of quantities.

Some Caveats while using annotate(): While Querying on multiple models(more than 2) with Many to Many relationship, annotate queries using parameters from both the tables, annotate return row for each of the relationship, and the returned number of rows gets increased for each relationsip, which may lead to unexpected behaviour. distinct() method cannot be used along with annotate().