Lesser-known methods in Django ORM

We all know Django provides ORM right out of the box thus preventing us from writing SQL queries. This mechanism has a lot of potentials. Most of us will be dealing only with the filter(), get(), all(), update() and delete() methods from the Django ORM.

But Django ORM provides a lot of other options in addition to this so we don’t miss the power of the traditional SQL queries. I will be covering the following methods in this article.

1. exclude()
2. values()
3. values_list()
4. select_related()
5. order_by()
6. exists()
7. count()
8. first() and last()
9. in_bulk()
10. explain()
11. latest()
12. earliest()

I will be using the following student table to provide examples for the above methods. This Student class is from the models.py file

class Student(models.Model):
    name = models.CharField(max_length=100)
    grade = models.IntegerField()
    section = models.CharField(max_length=10)
    school = models.ForeignKey(School, on_delete=models.CASCADE)
    blood_group = models.CharField(max_length=10)
    mobile = models.CharField(max_length=20)
    address = models.TextField()
    def __str__(self):
        return self.name

The student table has a Foreign key relationship with the school table.

class School(models.Model):
    name = models.CharField(max_length=100)
    email = models.EmailField(null=True, blank=True)
    address = models.TextField()
    def __str__(self):
        return self.name

Let us get started.

1. exclude()

The first method is the exclude() method. This method will basically return us a query set excluding the value that we have given. I have 4 students in my student table. First, let me get them all using the all() method.

>>> queryset = Student.objects.all()
>>> queryset
<QuerySet [<Student: Regina Johnson>, <Student: Eva Smith>, <Student: Jessie Smith>, <Student: John David>]>

I do not want ‘Eva Smith’ to be present in the query set. So I can do something like this. I can call the exclude() method with her name on the Student model.

>>> queryset = Student.objects.exclude(name='Eva Smith')
>>> queryset
<QuerySet [<Student: Regina Johnson>, <Student: Jessie Smith>, <Student: John David>]>

As you can see now she is excluded from the query set.

2. values()

The next method is the values() method. This method returns Python dictionaries, instead of a QuerySet object.

>>> Student.objects.values()<QuerySet [{'id': 1, 'name': 'Regina Johnson', 'grade': 10, 'section': 'A', 'school_id': 1, 'blood_group': 'A+', 'mobile': '9791684645', 'address': '93 Jessica Ln, Depew, NY, 14043'}, {'id': 3, 'name': 'Eva Smith', 'grade': 12, 'section': 'A', 'school_id': 1, 'blood_group': 'A1+', 'mobile': '8907896543', 'address': '2012 Walnut Ave #J, Ceres, CA, 95307'}, {'id': 4, 'name': 'Jessie Smith', 'grade': 12, 'section': 'A', 'school_id': 1, 'blood_group': 'A1+', 'mobile': '8907896543', 'address': '503 Courtney Dr, Brusly, LA, 70719'}, {'id': 5, 'name': 'John David', 'grade': 12, 'section': 'A', 'school_id': 1, 'blood_group': 'A1+', 'mobile': '2675431231', 'address': '34 Leaman Pl, Lynbrook, NY, 11563'}]>

We can also retrieve only the fields that we require by providing the field names as arguments to the values() method. Let us say I need only the ‘id’ and ‘name’ of the students. I can do something like this.

>>> Student.objects.values('id', 'name')<QuerySet [{'id': 1, 'name': 'Regina Johnson'}, {'id': 3, 'name': 'Eva Smith'}, {'id': 4, 'name': 'Jessie Smith'}, {'id': 5, 'name': 'John David'}]>

3. values_list()

The values_list() method is similar to the values() method, but instead of returning dictionaries it returns tuples.

>>> Student.objects.values_list('id', 'name')<QuerySet [(1, 'Regina Johnson'), (3, 'Eva Smith'), (4, 'Jessie Smith'), (5, 'John David')]>

If we only need a single value like a list instead of a tuple we can pass an additional argument flat=True to the values_list method. If I want only name as a list I can do something like this.

>>> Student.objects.values_list('name', flat=True)<QuerySet ['Regina Johnson', 'Eva Smith', 'Jessie Smith', 'John David']>

Note: This will work only with a single field. If you provide more than one field you will get an error.

>>> Student.objects.values_list('id', 'name', flat=True)
TypeError: 'flat' is not valid when values_list is called with more than one field.

4. select_related()

This is one of the methods which I like very much. As I said my student table has a foreign key relationship to the school table. So in order to retrieve the school from a student, I can query like this.

>>> student = Student.objects.get(pk=1)
>>> student.school
<School: Montfort>

First, the student table is queried with the student id to get a particular student. Then again in order to get the school, we perform an additional database lookup.

Note: Each foreign key relationship requires an additional database lookup.

For our simple example, this is not a problem, but in large databases with many foreign key relationships, the load on the database can be prohibitive.

We can use select_related() to improve database performance by retrieving all related data the first time the database is hit.

>>> student = Student.objects.select_related('school').get(pk=1)
>>> student.school # school has already been retrieved. Database is not hit again.
<School: Montfort>

In this case, the school data is retrieved from the pre-fetched data while making the first query instead of querying the database again.

5. order_by()

The order_by() method changes the default ordering of the QuerySet. By default, the ordering is based on the primary key(id) field. If I want my QuerySet to be ordered based on names I can provide the names field to the order_by() method.

If I want my QuerySet to be ordered based on the names in ascending order I can do something like this.

>>> Student.objects.order_by('name')<QuerySet [<Student: Eva Smith>, <Student: Jessie Smith>, <Student: John David>, <Student: Regina Johnson>]>

If I want the names in descending order I can query the database like this.

>>> Student.objects.order_by('-name')<QuerySet [<Student: Regina Johnson>, <Student: John David>, <Student: Jessie Smith>, <Student: Eva Smith>]>

The negative of the field name will do the trick.

6. exists()

The exists() method returns True if the returned QuerySet contains one or more objects, False if the QuerySet is empty.

>>> Student.objects.filter(name='Regina Johnson').exists()
True
>>> Student.objects.filter(name='Regina David').exists()
False

My database has a student named Regina Johnson and so the exists() method returns True when called with the name as ‘Regina Johnson’ and False in other cases.

7. count()

The count() method counts the number of entries in the QuerySet. It can be used to count all objects in a database table.

>>> Student.objects.count()
4

Or used to count the number of objects returned by a query:

>>> Student.objects.filter(name='Regina Johnson').count()
1

count() is functionally equivalent to using the aggregate() function, but it has a cleaner syntax, and is likely to be faster on large datasets.

8. first() and last()

The first() method returns the first element in the QuerySet.

>>> Student.objects.all()
<QuerySet [<Student: Regina Johnson>, <Student: Eva Smith>, <Student: Jessie Smith>, <Student: John David>]>>>> Student.objects.all().first()
<Student: Regina Johnson>

The last() method will return the last element from the QuerySet.

>>> Student.objects.all()
<QuerySet [<Student: Regina Johnson>, <Student: Eva Smith>, <Student: Jessie Smith>, <Student: John David>]>>>> Student.objects.all().last()
<Student: John David>

Although the QuerySet is similar to the list you can retrieve the first element using an index like queryset[0], but you cannot retrieve the last element like queryset[-1]. This will throw an error. In this scenario, the last() method will come in handy.

>>> Student.objects.all()[0]
<Student: Regina Johnson>
>>> Student.objects.all()[-1]    "Negative indexing is not supported."
AssertionError: Negative indexing is not supported.

9. in_bulk()

in_bulk() takes a list of id values and returns a dictionary mapping each id to an instance of the object with that id. If you don’t pass a list to the in_bulk() method, all objects will be returned.

Let us say I want to retrieve only students whose id is 1 and 4, I can do something like this.

>>> students = Student.objects.in_bulk([1, 4])
>>> students[1].name
'Regina Johnson'
>>> students[4].name
'Jessie Smith'

Then I can access the object using the id as an index.

10. explain()

This method returns a string of the QuerySet’s execution plan. Used for analyzing query performance.

>>> Student.objects.filter(pk=1).explain()
'2 0 0 SEARCH TABLE student_student USING INTEGER PRIMARY KEY (rowid=?)'

11. latest()

The latest() method is similar to the last() method. This method returns the latest object in the table based on the given field(s).

For example, if you have a blog model and you wish to get the latest modified blog post you can do something like this.

>>> Blog.objects.latest('updated_at')
<Blog: Blog object (17)>

This is equivalent to,

>>> Blog.objects.all().order_by('updated_at').last()
<Blog: Blog object (17)>

The latest() method can take multiple field names as well.

>>> Blog.objects.latest('updated_at', '-created_at')
<Blog: Blog object (17)>

Note: If you have set the ordering meta in your model the results may vary.

12. earliest()

The earliest() method is similar to the first() method and the opposite to the latest() method.

This will take multiple field names as well and returns the first record from the table.

>>> Blog.objects.earliest('updated_at')
<Blog: Blog object (17)>

Conclusion

Hope this article is helpful. Follow me for more cool articles on Python and Django. Happy coding!

References

https://docs.djangoproject.com/en/3.2/ref/models/querysets/