As a web developer, using databases may be a pain, you must ensure that all your queries are pretty well structured, and they have to work as expected, so when you choose an ORM you are in a good way to achieve it, you can scale easier and your code will be more organized.

The Django web framework includes a default object-relational mapping layer (ORM) that can be used to interact with application data from various relational databases such as SQLite, PostgreSQL and MySQL.

In this post we are going to see the most common queries that can be used with Django ORM. If you are concerned about the speed of your query, cause you have huge amount of data or you want to go deeper, you can take a look at these posts:

• https://swapps.com//blog/speed-it-up-recommendations-to-make-your-django-app-faster/
• https://swapps.com//blog/squash-your-response-time-in-django-querysets/

To run our examples, let’s create two models:

from django.db import models
 
 
class Category(models.Model):
    name = models.CharField(max_length=500)
    def __str__(self):
        return self.name
        
class Book(models.Model):
    name = models.CharField(max_length=500)
    author = models.CharField(max_length=200)
    available = models.BooleanField(default=True)
    category = models.ForeignKey(Category, on_delete=models.CASCADE)
    def __str__(self):
        return self.name

1. Insert data

Create the migrations and then migrate:

>>> python manage.py makemigrations
>>> python manage.py migrate

After migrate, using the shell you may create several categories and books:

The First approach using Django ORM:

>>> Category.objects.create(name="education")
>>> Book.objects.create(name="education", author="author test", category=Category.objects.get(name='education'))

The second approach is using the model reference:

>>> category = Category(name="science")
>>> category.save()
>>> book = Book(name="Science book", author="Sciencie Author", category=category)
>>> book.save()

Both approaches are equivalent

Notice that we don’t set the available attribute, because it will save as the default value “True”. also in the category attribute we set the value with the object category,  that has already been saved to the database.

The category foreign key requires to have an object that exists on the database; if you initialize the Category() object but do not save it, creating the book will raise a ValueError due to unsaved related object.

Django also provides a way to create multiple objects at once:

>>> Category.objects.bulk_create(
... [Category(name="adventure"),
... Category(name="science"),
... Category(name="education")])
[<Category: adventure>, <Category: science>, <Category: education>]

Bulk_create receive a list of unsaved objects

2. List all objects

To get the records:

>>> qs= Category.objects.all()
>>> qs

The response will be something like this:

<QuerySet [<Category: science>, <Category: adventure>, <Category: education>, <Category: adventure>]>

Sometimes we need to know how the query looks. To do that we use the query attribute and str function:

>>> str(query.query)

'SELECT "app1_category"."id", "app1_category"."name" FROM "app1_category"'<br>

3. Update data

With the category list, we are going to select the first one and Capitalize the name:

category = Category.objects.first()
>>> category
<Category: science>
>>> category.name = "Science"
>>> category.save()
>>> category.refresh_from_db()
>>> category
<Category: Science>

Sometimes you need to call refresh_from_db in order to update values from database

You also may do:

>>> Category.objects.filter(name="science").update(name="Science")

4. Select specific Object

If you have set the name attribute as unique, you can use .get function:

>>> Category.objects.get(name="education")
<Category: education>

Otherwise, if there are more than one object with that name, will raise the “MultipleObjectsReturned” error

In case there is no object with that name a DoesNotExist will be raised

You will need to use the filter function, which will return the list of elements matching the query.

>>> Category.objects.filter(name="education")
<QuerySet [<Category: education>]>

5. Field Lookups

Field lookups are how you specify the meat of an SQL WHERE clause. They’re specified as keyword arguments to the QuerySet methods filter(), exclude() and get().

Iexact: The next query will look up the exact name as case insensitive:

>>> c = Category.objects.filter(name__iexact='science')
>>> str(c.query)
>>> 'SELECT "app1_category"."id", "app1_category"."name" FROM "app1_category" WHERE UPPER("app1_category"."name"::text) = UPPER(science)'

Icontains: will do a Where query of LIKE Type:

>>> c = Category.objects.filter(name__icontains='education')

>>> str(c.query)
>>> 'SELECT "app1_category"."id", "app1_category"."name" FROM "app1_category" WHERE UPPER("app1_category"."name"::text) LIKE UPPER(%education%)'

Many of this field lookup you may find in the official site.

6. Or Queries

Let’s retrieve categories that starts with ‘d’ OR starts with ‘s’, we will use the field lookup startswith, that will do a query of type LIKE d%.

The common way is to create two different querysets and joining with ‘|’:

>>> Category.objects.filter(name__startswith='a') | Category.objects.filter(name__startswith='s')
<QuerySet [<Category: adventure>, <Category: science>]>

The other Way is to use Q objects, Django definition say “A Q object (django.db.models.Q) is an object used to encapsulate a collection of keyword arguments. These keyword arguments are specified as in ‘Field lookups’”.

>>> Category.objects.filter(Q(name__startswith='a') | Q(name__startswith='s'))<br>

For both options the generated sql will be the same

7. And Queries

We are going to get categories that starts with ‘e’ and ends with ‘n’, then we have 3 options:

• Create each condition with filter and joining it with &:

>>> Category.objects.filter(name__startswith='e') & Category.objects.filter(name__endswith='n')
<QuerySet [<Category: education>]>

• Use Q Objects:

>>> Category.objects.filter(Q(name__startswith='e') & Q(name__endswith='n'))<br>

• Add the conditions inside filter function:

>>> Category.objects.filter(name__startswith='e', name__endswith='n')<br>

For the 3 options we have the same query:

'SELECT "app1_category"."id", "app1_category"."name" FROM "app1_category" WHERE ("app1_category"."name"::text LIKE %n AND "app1_category"."name"::text LIKE e%)'<br>

8. Not Query

Let’s get all Categories which IDs are greater than 2

• Exclude method will return the query that does not match the condition:

>>> Category.objects.exclude(id__lt=2)

• The other method is to use Q objects with character tilde (~):

>>> Category.objects.filter(~Q(id__lt=2))

9. Union

Union is used to join the result of differents querysets, when the union is from different models the fields and their datatypes should match.

>>> q1 = Book.objects.filter(available=True)
>>> q1
>>> <QuerySet [<Book: Science book>]>
>>>  q2 = Book.objects.exclude(available=True)
>>> q2
>>> <QuerySet [<Book: education book>]>
>>> q1.union(q2)
>>> <QuerySet [<Book: education book>, <Book: Science book>]>

10. Join Queries

Join can be done with select_related method: Django defines this function as Returns a QuerySet that will “follow” foreign-key relationships, selecting additional related-object data when it executes its query.

>>> Book.objects.select_related('category')<br>

The Query looks like this:

>>> 'SELECT "app1_book"."id", "app1_book"."name", "app1_book"."author", "app1_book"."available", "app1_book"."category_id", "app1_category"."id", "app1_category"."name" FROM "app1_book" INNER JOIN "app1_category" ON ("app1_book"."category_id" = "app1_category"."id")'<br>

Another way to make join is with filter, as follows: the name of the model relation, followed by a double underscore (__), followed by the name of the field: 

Book.objects.filter(category__name='science')<br>

11. Aggregations

Sometimes we need to add, count or get data from a group of objects. That can be done with aggregations:

import the methods:

 >>> from django.db.models import Avg, Max, Min, Sum, Count<br>

Let’s see some of them:

Avg: Returns the mean value of the given expression

>>> Category.objects.all().aggregate(Avg('id'))
>>> {'id__avg': Decimal('2.0000000000000000')}

Count: Returns the number of objects that are related through the provided expression.

>>> Category.objects.count()
>>> 3

Use this function instead of len(query), it will be more efficient for your logic as Django suggests. “If you only need to determine the number of records in the set (and don’t need the actual objects), it’s much more efficient to handle a count at the database level using SQL’s SELECT COUNT(*).“

Django ORM supply a powerful Abstraction of Database, which mean a full pack of advantage, you will be able to scale easier, you will not worry about  SQL injection and easy to start and create all your CRUD.

These are some of the useful methods That Django ORM provides us. Take a look at the official site here, the previous examples will help you to get started with your first Django app, in order to do the queries that we always need to get started