Let's Explore Django Queryset API indepth.
Making efficient database queries is one of the most important skill of any backend operation. Queries can either make your application or destroy it. Optimising your application from the backend lies on writing efficient algorithms. Django developers had taken enough time writing these complex algorithms for other developers to reuse. If you are curious like me to find out what is happening. You should explore the django project on Github.
Today, We will be focusing on Django based application and how we can leverage the built-in queryset API to speed up not just the development time but your application response time.
Our Models
from django.db import models
from django.conf import settings
class BaseModel(models.Model):
created_at = models.DateTimeField(auto_now_add=True)
modified_at = models.DateTimeField(auto_now=True)
user = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.CASCADE)
class Meta:
abstract = True
class Investment(BaseModel):
title = models.CharField(max_length=200)
interest = models.DecimalField(decimal_places=2, max_digits=4)
min_invest = models.DecimalField(decimal_places=2, max_digits=8)
max_invest = models.DecimalField(decimal_places=2, max_digits=8)
duration = models.DurationField()
active = models.BooleanField(default=False)
def __str__(self) -> str:
return self.title
class Meta:
verbose_name_plural = "Investment"
ordering = "-created_at"
class UserInvest(BaseModel):
amount = models.DecimalField(max_digits=4, decimal_places=2)
package = models.ForeignKey(Investment, on_delete=models.PROTECT)
started = models.BooleanField(default=False)
def __str__(self) -> str:
return f"{self.user.username} invested {self.amount} in {self.package.title) package"
''' other methods for comes in.
I love writing fat models because it makes my work easy
and makes the application code to have one source of truth
Note: You should always use django.utils.translation for translation.
'''
Our Views
This is where the fun begins. we will touch every part of queryset api showing you more complex methods to query your database.
Get all queries in reverse order.
'''
We will make database queries through our models.
Note: we assume that we have enough data in our database to work with.
'''
from django.views.generic import ListView
from django.db.models.query import Q
from myapp.investment.models import Investment, UserInvest
class InvestmentListView(ListView):
template_name = "investment/investment_list.html"
model = Investment
context_object_name = "investment"
def get_queryet(self, **kwargs):
return super().get_queryset(self, kwargs).order_by("-created_at")
The query above list out all the investment package that the company have starting from the latest. Django have provided an efficient way that is optimised. instead of using the built in python methods, django provide a good api to optimise such query.
Note: Don't use python built in methods if django have provided an alternative. instead of reverse(queries) use
queries.order_by() function or pass in the ordering keywords with the model Meta Class.
#You can pass in the ordering keywords with the model to re-order your data. like
class Meta:
ordering = "-created_at"
# OR use
def get_querset(self, **kwargs):
return super().get_queryset(self, kwargs).order_by("-created_at")
Alternatives to common queries
instead of using this to check if objects exists.
for investment in Investment.objects.all():
if investment.title == "UBA stock":\
print(f"investment.title exists")
Do This
Investment.objects.filter(title="UBA stock").exist()
To count objects use
Investment.objects.count()
To Select to first/last few elements use slicing
Investment.objects.all()[:5]
To use Conditions in getting objects, try filtering by such condition,
from django.db.model.query import Q
Investment.objects.filter(active=True, created_at__month=datetime.datetime.now().month)
## complex queries use Q objects
invest = Q(Investment.objects.filter(active=True) | Investment.objects.filter(completed=True))
To Exclude some objects with certain conditions, try this
Investment.objects.exclude(created_at__gt=datetime.date(2022, 6, 2), title='Morgan Stock')
NB: You can chain these individual queries to perform a more complex queries
To reverse queries
Investment.objects.reverse()
To get queries that have distinct values
Investment.objects.order_by('created_at').distinct('created_at')
To query on values
Investment.objects.filter(title__istartswith="UBA")
Investment.objects.filter(title__iendswith="stock")
Investment.objects.filter(title__icontains="stock")
## These are case sensitive operations, so i prefer using i to prefix the startswith or endswith
What of dates
>>> Investment.objects.dates('created_at', 'year')
[datetime.date(2022, 1, 1)]
>>> Investment.objects.dates('created_at', 'month')
[datetime.date(2022, 2, 1), datetime.date(2022, 3, 1)]
>>> Investment.objects.dates('created_at', 'week')
[datetime.date(2022, 2, 14), datetime.date(2022, 3, 14)]
>>> Investment.objects.dates('created_at', 'day')
[datetime.date(2022, 2, 20), datetime.date(2022, 3, 20)]
>>> Investment.objects.dates('created_at', 'day', order='DESC')
[datetime.date(2022, 3, 20), datetime.date(2022, 2, 20)]
>>> Investment.objects.filter(title__contains='UBA').dates('pub_date', 'day')
[datetime.date(2022, 3, 20)]
If you want to combine multiple distinct queries use union
invest1 = Investment.objects.filter(active=True)
invest2 = Investment.objects.filter(completed=True)
invest3 = Investment.objects.filter(active=False)
invest = invest1.union(invest2)
invest4 = invest1.union(invest2, invest3)
To get the intersection of 2 queries use
invest1 = Investment.objects.filter(active=True)
invest2 = Investment.objects.filter(completed=True)
invest3 = Investment.objects.filter(active=False)
invest = invest1.intersection(invest2)
To get the difference of 2 queries use
invest1 = Investment.objects.filter(active=True)
invest2 = Investment.objects.filter(completed=True)
invest3 = Investment.objects.filter(active=False)
invest = invest1.difference(invest2)
If you have objects that has relationships and you don't want multiple database queries to get the relationships, use select_related or prefetch_related function
UserInvest.objects.select_related("package").get(id="7")
UserInvest.objects.filter(amount__gte=200).prefetch_related("package")
UserInvest.objects.filter(amount__gte=200).select_related("package")
There are lots of things going on while using select_related or prefetch related. I suggest you look at django docs and understand deeply how they both perform their queries.
Instead of writing raw queries using cursor objects, use django select and extra function
Investment.objects.extra(select={'is_recent': "created_at > '2022-01-01'"})
UserInvest.objects.extra(
select={
'entry_count': 'SELECT COUNT(*) FROM userinvest_entry WHERE userinvest_entry.userinvest_id = userinvest_userinvest.id'
},
)
## This query might not make much sense, but it shows what is possible.
In some complex data-modeling situations, your models might contain a lot of fields, some of which could contain a lot of data (for example, text fields), or require expensive processing to convert them to Python objects. If you are using the results of a queryset in some situation where you don’t know if you need those particular fields when you initially fetch the data, you can tell Django not to retrieve them from the database. use defer in this case
UserInvest.objects.defer("package")
If you want to defer all other fields except some, use only.
Investment.objects.only("title", "duration").only("user")
If you are connected to multiple databases and wanted to use a specific database, Use using to specify the database.
Investment.objects.using('backup')
To first or last query
p = Investment.objects.order_by('title', 'created_at').first()
q = Investment.objects.order_by('title', 'created_at').last()
To count or sum a field, use aggregate and Sum function
from django.db.models.aggregates import Sum, Count
q = Investment.objects.aggregate(Count('title'))
total_amount = UserInvest.objects.aggregate(Sum("amount"))
Django provides lots of api to make highly optimised queries. All you need to do is read the doc
Happy Coding!!!
Top comments (1)
good tips..