Django ORM Queries

Pre-requisites

Before moving on to retrieving and filtering objects in Django ORM we must first meet the following prerequisites

• The Latest version of Django.
• Creation of a project in Django, along with the creation of an app.
• Database application SQL.

Introduction to Django ORM Queries

Django lets us interact with its database models using an API called ORM. The primary goal of ORM is to send data between a database and application models. It represents a relationship between a database and a model. The main advantage of using Django ORM queries is that it speeds up and eliminates errors throughout the development process.

What is a QuerySet?

A Query Set is a collection of data from a database. Query set allows us to get data easily, by allowing us to filter, create, order, etc. Let us take an example of a database table named Students.

In views.py, we have a view called testing where we will test different queries. In the source code below, we use all() to get all the records and fields of the model Students. The object is placed in a variable known as mydata, it is sent to the template through the context object as mystudents:

Model Students contain 5 records, they are listed inside the Query Set as 5 objects.

Below is the source code oftemplate.html, where we will be using mystudents object to generate content:

The output of the template.html is:

• Django Shell

So to enter into the Django shell, the following command should be entered into the command prompt in the virtual environment:

This will lead us to an interactive console.

• All objects

There are some methods to get data from a model into a queryset: 1. all() It returns each object as a Python dictionary with names and values as key and value pairs respectively. The source code for the same is given below:

The output is:

2. values_list()

The values_list() returns only the column that is specified. The source code for the same is given below:

The output is:

• Create Objects

To create an object, at first we need to import Students

Thus for creating an object, use the following code

• Filter Objects

The filter() returns a filtered search. The source code for the same is given below:

The output is:

Django also allows fetching filtered data based on multiple conditions, like AND and OR operations, and also be performed.

1. AND

We can get filtered data satisfying both of the queries mentioned. Refer to the example for demonstration.

The output it returns:

2. OR

We can also get filtered data that matches either of the query mentioned. An example is mentioned below.

The output it returns:

3. Field Lookups

Field lookups are keywords that represent specific SQL keywords.

The output it returns:

Some of the field lookup keywords are mentioned below:

• Ordering objects

Django provides a feature to sort the query sets, using order_by().

1. To Sort the Result Alphabetically by Name

It returns the following:

2. To Sort in Descending Order

The results are by default sorted in ascending order. To sort in descending order, we insert a '-' minus sign in front of the field name. Thus for sorting in reverse alphabetical order we add '-' in front of 'name'.

The above code returns:

3. Multiple Order By

To order more than one field, kindly consider the code below.

The above code returns:

• Complex queries through method-chaining

A query set can be combined with another query set by chaining them together,

The above code would return

Explanation of Each ORM Query and Associated SQL Queries

• Create a sample database using the Django model

In the above code, we created two models Album and Song. Whenever an instance of a model is created in Django, it will display the object as Modelname Object(1) in the admin interface. Hence to change the display name we use the function def __str__(self). The Str function in a Django model returns a string that is rendered as the display name of instances for that model. In our case, it will display the name of the title for the Model Album and the name of the song for the Model Song. In the Model Song, we are linking the second field, the album with the Model Album.

After creating the models, we need to use the following command:

In the above commands,makemigrationsis responsible for packing up the changes into individual migration files, and migrate is responsible for applying those to our database.

Now we need to access Django ORM, it can be accessed by using the following command inside our project directory:

This leads us to an interactive Python console. Next, we are supposed to import our models using the following command:

• Get all records from a table(Model)

Let us add some records for ease of explanation.

To retrieve all the objects of a model,all()is used:

The corresponding SQL query is

• Add a record to the table(Model)

We write the following code to add and save a record in the table(Model) Album:

We write the following code to create and save a record in the table(Model) Song:

Well to insert data in the model (Song) whose field (album) is related to another model (Album), at first we have to take a variable (a) and store the corresponding data on the first model (Album) then we enter the data in the model (Song) whose field (album) is related and enter the name of the variable (a) in the field (album) that is related. The corresponding SQL query is

• Retrieving Single Objects from QuerySets

The get() is used to retrieve a single object matching the conditions we give in the command. When the query returns multiple objects, it throws an error.

The corresponding SQL query is

• Filtering the Records

The filter() returns a filtered search. The source code for the same is given below:

The corresponding SQL query is

Django also allows fetching filtered data based on multiple conditions, like AND and OR operations, and also be performed.

1. AND(&)

We can get filtered data satisfying both of the queries mentioned. Refer to the example for demonstration.

The corresponding SQL query is

2. OR(|)

We can also get filtered data that matches either of the query mentioned.

The corresponding SQL query is

• Ordering objects

Django provides a feature to sort the query sets, using order_by().

1. To sort the result alphabetically by name

The corresponding SQL query is

2. To sort in descending order

The results are by default sorted in ascending order. To sort in descending order, we insert a '-' minus sign in front of the field name. Thus for sorting in reverse alphabetical order we add '-' in front of 'title'.

The corresponding SQL query is

In the SQL query, we add DESC for sorting in descending order and ASC for ascending order along with the field name.

3. Multiple Order by

To order based on more than one field, kindly consider the code below.

Since the name of the artist was the same for the second and third records the ordering is done based on the genre in descending order. The corresponding SQL query is

• Creating Multiple Objects in One Shot

Django allows us to enter multiple records without writing multiple queries every time, usingbulk_create.

After entering the above, the interactive console prompts the following

The corresponding SQL query is

• Limiting QuerySets

This allows us to display a certain number of records based on our requirements. The code below displays the first 4 records.

The corresponding SQL query is

The code below returns two records starting from the second one from index number 1. The first record is of index 0.

The corresponding SQL query is

To return a single record, we write

The corresponding SQL query is

• Important Field Lookups

Field lookups are keywords that represent specific SQL keywords.

The corresponding SQL query is

The following code is used to return the exact result.

The corresponding SQL query is

The following is equivalent to 'contain'.

The corresponding SQL query is

• Perform join operations in Django

The SQL join unites data or rows from two or more tables that share a common field. First, let us add some data to another model, Song we created.

Therefore it returns all the records of the model Song with a common album in the model Album. The corresponding SQL query is

• Group record in Django ORM

We will consider a new model and a set of records for ease of explanation.

After creating the model and registering it in the admin.py, we make migrations and migrate in the command prompt and then go to the interactive shell, importing our model and creating records. There are operations like max, min, avg, sum, and count that can be performed for a particular field. At first, we import the following operations.

The corresponding SQL query is

• Perform a truncate-like operation using Django ORM

To delete the records from the model(table) we write the following:

The corresponding SQL query is

• Complex queries through method-chaining

A query set can be combined with another query set by chaining them together,

The corresponding SQL query is

Difference Between Null=True and Blank=True

Null informs the database whether the database column for the field can be left empty, resulting in the database setting the column to NULL or NOT NULL. If the database encounters an empty NOT NULL column, an IntegrityError will be raised. If null=True, Django will store empty values in the database as NULL.

If blank=True, the field model validation allows users to enter an empty value such as "". If blank=False, the validation will prevent the entry of empty values.