SQL AND THE DJANGO ORM

A quick introduction about me. I am Mubaarock, a 2nd year software engineering student at the Federal University of Technology, Akure, Nigeria. I am currently learning backend development using python and the Django framework. It is worth stating that I am currently enrolled in the HNG11 internship, which I am super excited about.

This blog post is centered around how I intuitively think (or make a mental mapping) of the operations in the Django ORM into the actual SQL statements being executed. While there exists an exhaustive list of operations in the Django ORM, I will discuss, in the following paragraphs, the ones I find must appealing to me when I did discover them. Let's dive in.

Sample Models

I will define two simplistic models here which for illustration sake through out the rest of the blog.

class Author(AbstractUser):
    name = models.Charfield(max_length=100)
    description = models.Textfield(default="")

class Post(models.Model):
    title = models.Charfield(max_length = 100)
    author = models.ForeignKey(Author, on_delete = models.CASCADE, 
                           related_name="posts")
    content = models.Textfield()

These models represent the two database tables we have in our virtual database. Every Django model has an objects property which is the default model manager, it is the actual interface the Django orm offers developers to encapsulate SQL operations.

first(),last(), slicing operation on queryset.

I have grouped these operations together because they make use of the the SQL LIMIT keyword in the corresponding SQL statement they represent. However the slicing operation additionally includes the OFFSET keyword.

##Django
   Post.objects.first()
##sql
   SELECT * FROM post_table 
   ORDER BY id
   LIMIT 1;
##Django
   Post.objects.last()
##sql
   SELECT * FROM post_table 
   ORDER BY id DESC
   LIMIT 1;
##Django
   Post.objects.all()[3:7]
##sql
   SELECT * FROM post_table 
   OFFSET 3 LIMIT 4;

SQL Joins

Django ORM operations translate to SQL join statements when lookups are made on relations. In our case, this might mean retrieving data from the post table giving certain criteria about the author who made it or vice versa.

Another case where SQL joins are executed by the Django ORM is when using the select_related method of the model manager, which is a typical solution to the N+1 problem when querying databases.

#Django
    print(Post.objects.filter(author__name="Mubaarock"))
#sql
    SELECT post_table.name,post_table.content,post_table.author_id
    FROM post_table
    INNER JOIN author_table ON post_table.author_id = author_table.id
    WHERE author_table.name = "Mubaarock";


#Django
    print(Post.objects.select_related("author"))
#sql
    SELECT *
    FROM post_table
    INNER JOIN author_table ON post_table.author_id = author_table.id;

Aggregations

The Django ORM allows for aggregation operations on database tables by providing the aggregate method of model managers and aggregation functions such as COUNT, SUM, AVG, MIN etc., just to mention a few.

The aggregation method can be passed multiple aggregation functions. When the aggregation functions are passed as keyword arguments, they are used by the ORM to create aliases in the SQL statement, if not Django has uses its naming convention.

It is also worth mentioning that every aggregation function has a filter keyword argument, this translates into the SQL FILTER keyword. The examples below should clarify things.

#Django
    print(Post.objects.aggregate(Count("id")))
#SQL
    SELECT Count("id") FROM post_table

#Django
print(Post.objects.aggregate(muby_num_post=Count("id",filter = 
                             Q(author__name = "Muby"))
                              ))
#SQL
    SELECT COUNT(post_table.id) 
        FILTER(WHERE author_table.name = "Muby")
        AS muby_num_post
    FROM post_table
    INNER JOIN author_table ON post_table.author_id = author_table.id;

values() and values_list()

All Django queryset methods that return rows of data from the database table include all its fields/columns. The two exceptions to this case are the values() and values_list() methods which allow for specifiying which columns to return from the tables.

#Django
    Post.objects.values("title","content")
    Post.objects.values_list("title","content")
#SQL
SELECT title, content
FROM post_table;

This concludes the list of the queryset methods in Django I will be discussing.

Conclusion

All thanks to The Almighty God, this concludes the end of my very first programming-related blog post. I have to give credit to BugByte on youtube, I have learned most of this stuff using his playlist on databases and the django orm (which I am currently still watching).
I definitely have to make mention of the HNG11 internship here as well, the very first task assigned to me has pushed me to write this blog post. The stage 1 of the intership starts on Monday, July 1st, I am definitely looking forward to take up the upcoming challenges.