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

```python
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.

```python
‘’’
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.

```python
#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.
```python
for investment in Investment.objects.all():
if investment.title == “UBA stock”:
```
Do This
```
Investment.objects.filter(title=”UBA stock”).exist()
```

To count objects use
```python
Investment.objects.count()
```

To Select to first/last few elements use slicing
```python
Investment.objects.all()[:5]
```

To use Conditions in getting objects, try filtering by such condition,
```python
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
```python
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
```python
Investment.objects.reverse()
```

To get queries that have distinct values
```python
Investment.objects.order_by(‘created_at’).distinct(‘created_at’)
```

To query on values
```python
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
```python
>>> 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
```python
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
```python
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
```python
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

```python
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
```python

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

```python
UserInvest.objects.defer(“package”)
```

If you want to defer all other fields except some, use only.
```python
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.
```python
Investment.objects.using(‘backup’)
```

To first or last query
```python
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
```python
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!!!