Django Queries Simplified [Part - 1]
This blog post aims to make django querying simple for the developers who are new in the django world. Keep reading :)
What is a django query?
A django query is an expression which can be used to write/retrieve/update/delete data from the database
Why are we talking about django query?
Django is a well-structured web framework and it uses ORM (Object-Relational Mapping), and because of that it has its own way of writing queries. Thus it is important to know how to write the queries.
What are we looking for today?
We’ll start from the basics and then climb up the ladder to the advanced levels of queries, so stay tuned to this blog post. We are looking at query filters in django today.
Please consider the following model (i.e. SQL Table) definition while referring the queries written on this post:
Let's see what all queries we can do on this table.
Basic filters on data:
Now let’s do some filtering on the char field.
LIKE operator operations:
ORM=> User.objects.filter(first_name__contains=”kira”) SQL=> SELECT • FROM "user" WHERE "first_name” LIKE “%kira%”
This query is case sensitive.
ORM=> User.objects.filter(first_name__icontains=”kira”) SQL=> SELECT • FROM "user" WHERE "first_name” ILIKE “%kira%”
This query is case insensitive.
What if we want to match the exact word? Follow along:
ORM=> User.objects.filter(first_name__exact=”kira”) ORM=> User.objects.filter(first_name__iexact=”kira”)
This one is case insensitive, notice "i" before exact.
Find all the values that start with "kira"
ORM=> User.objects.filter(first_name__startswith=”kira”) SQL=> SELECT • FROM "user" WHERE "first_name” LIKE “kira%”
Finds all the values that ends with "kira"
ORM=> User.objects.filter(first_name__endswith=”kira”) SQL=> SELECT • FROM "user" WHERE "first_name” LIKE “%kira”
We also have istartswith and iendswith, I don’t think I need to explain these, examples of "i" are already given above
Let’s move to the date operations, this is going to be really lucrative.
Filter by a given date (not the time part, just DATE):
ORM=> User.objects.filter(created_date__date=datetime.date(2021, 1, 1)) SQL=> SELECT • FROM "user" WHERE "created_date”=“2021-1-1”
Since I want to get data by the dates that’s why I am using __date in the ORM query
Filter by a given month
ORM=> User.objects.filter(created_date__month=1) SQL=> SELECT • FROM "user" WHERE EXTRACT('month' FROM "created_date”) = “1”;
Since we want the records of January month so I am using __month in the ORM query
1 denotes the January month here.
Filter by a given year
ORM=> User.objects.filter(created_date__year=2021) SQL=> SELECT • FROM "user" WHERE "created_date”BETWEEN “2021-1-1” AND “2021-12-31”;
Since we want data only from the year 2021 that’s why we used __year filter here followed by the year which we want to filter.
Get the records which were created before 10th day of the month:
ORM=> User.objects.filter(created_date__day__lt=10) SQL=> SELECT • FROM "user" WHERE EXTRACT(day FROM "created_date”) < “10”;
Now we have introduced another filter after __day ie. __lt which means less than
the given day.
Get the records which were created on and after 3rd month:
ORM=> User.objects.filter(created_date__month__gte=3) SQL=> SELECT • FROM "user" WHERE EXTRACT('month' FROM "created_date”) >= “3”;
Now we have introduced another filter after __month ie. __gte which means greater
than and equal to the given month
The possibilities are endless for using the filters with respect to date and time. Like wise one can do filtering based on week, week_day, iso_week_day, quarter, time, hour, minute even seconds and also for is null
Filter based on null values:
It also has regex filter, what more do we want now?
ORM=> User.objects.filter(first_name__regex=r'^(An?|The) +')
You can also do a search which doesn’t include some data. In the following example we want to exclude id number 3
This was just the step 1 of the django querying simplified, stay tuned for the next blog and next part of this series.