Django select_related and prefetch_related

 

Difference between select_related and prefetch_related

Reducing SQL queries is one of the first steps when optimizing a Django project. There are two powerful methods included in the Django ORM to help us that can boost performance by creating a single more complex QuerySet rather than multiple, smaller queries.

In this project we will understand about  select_related and prefetch_related. 

Django use these two orm method to reduce sql queries in database based on different scenario. 

select_related

Lets assume  this two model we have. 

class Author(models.Model):

name = models.CharField(max_length=200)

def __str__(self):

return self.name

class Courses(models.Model):

name = models.CharField(max_length=200)

author = models.ForeignKey(Author,on_delete=models.CASCADE,related_name='courses')

def __str__(self):

return self.name

Here we have two mode. One is Author and another is Courses model. and we have added Author as foreign key in Courses mode. 

now suppose we want to get all the courses then we have to run this query

course = Courses.objects.all()

 Database query will be like this 

SELECT id, name,author_id FROM blog_courses

and now if we want to get the author it'll run a query again.example:

we have now Courses model objects in course variable. we want 0 index values author details.

courses[0].author

now it'll execute another query to get the author details.
Database query will be like this:

SELECT id, name, author_id FROM blog_courses

SELECT id, name FROM blog_author WHERE id = 1

This 2 query will execute if we want to get the author details from Courses mode.

But if we use select_related then we can reduce this expensive query execution in database

let's see what happen we use 

course = Courses.objects.select_related()

This query will also return all the objects from Courses model in course variable. In database it'll execute this sql query

SELECT id,name, author_id, FROM blog_courses INNER JOIN blog_author ON (author_id = id)

When we will use select_related() this will automatically INNER JOIN with Author model and get the all values from Author model. 

courses[0].author

Now if we can easily get the author value without executing another Query.

prefetch_related

Now let's deep drive into prefetch_related. In this scenario we want to get the Courses model objects from Author model. 
we'll query in Author model but we want data from Courses model. Author model has no connection with Courses model.

author = models.ForeignKey(Author,on_delete=models.CASCADE,related_name='courses')

Hope you can remember this field. we have used Author model as Foreign key in Courses model and we have used related_name

Using related_name and prefetch_related we can get Courses model data from Author model.

If we django query like this

author = Author.objects.prefetch_related()

Database Query:

SELECT id,name FROM blog_author ;

This will as same as Author.objects.all() query. 

But if we use

author = Author.objects.prefetch_related("courses")

In database it'll execute this sql query

SELECT id, name, author_id FROM blog_courses WHERE author_id IN (1, 2, 3, 4); 

See here it's executing a query in Courses model with where condition of Author id. Now we can get Courses Model object easily  in a single query.