DEV Community

Cover image for Django Queryset API Is Enough.
Chidozie C. Okafor
Chidozie C. Okafor

Posted on

Django Queryset API Is Enough.

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.

'''

Enter fullscreen mode Exit fullscreen mode

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")
Enter fullscreen mode Exit fullscreen mode

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")

Enter fullscreen mode Exit fullscreen mode

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")
Enter fullscreen mode Exit fullscreen mode

Do This

Investment.objects.filter(title="UBA stock").exist()
Enter fullscreen mode Exit fullscreen mode

To count objects use

Investment.objects.count()
Enter fullscreen mode Exit fullscreen mode

To Select to first/last few elements use slicing

Investment.objects.all()[:5]
Enter fullscreen mode Exit fullscreen mode

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))
Enter fullscreen mode Exit fullscreen mode

To Exclude some objects with certain conditions, try this

Investment.objects.exclude(created_at__gt=datetime.date(2022, 6, 2), title='Morgan Stock')
Enter fullscreen mode Exit fullscreen mode

NB: You can chain these individual queries to perform a more complex queries

To reverse queries

Investment.objects.reverse()
Enter fullscreen mode Exit fullscreen mode

To get queries that have distinct values

Investment.objects.order_by('created_at').distinct('created_at')
Enter fullscreen mode Exit fullscreen mode

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

Enter fullscreen mode Exit fullscreen mode

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)]
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)

Enter fullscreen mode Exit fullscreen mode

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)

Enter fullscreen mode Exit fullscreen mode

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")
Enter fullscreen mode Exit fullscreen mode

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.
Enter fullscreen mode Exit fullscreen mode

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")
Enter fullscreen mode Exit fullscreen mode

If you want to defer all other fields except some, use only.

Investment.objects.only("title", "duration").only("user")
Enter fullscreen mode Exit fullscreen mode

If you are connected to multiple databases and wanted to use a specific database, Use using to specify the database.

Investment.objects.using('backup')
Enter fullscreen mode Exit fullscreen mode

To first or last query

p = Investment.objects.order_by('title', 'created_at').first()
q = Investment.objects.order_by('title', 'created_at').last()
Enter fullscreen mode Exit fullscreen mode

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"))
Enter fullscreen mode Exit fullscreen mode

Django provides lots of api to make highly optimised queries. All you need to do is read the doc

Happy Coding!!!

Top comments (1)

Collapse
 
say_whaaaaaattt profile image
Hypertext

good tips..