Django Advance ORM Query

Here we will learn anadvanced conceptof Django ORM query:

1. Query Expression
2. When
3. Case
4. Cast
5. Trunc
6. OuterRef and SubQuery

A Brief Description of the Above Points

Q. What is Query Expression in Django?

Ans. Query Expression is used for the computation of value that can be used in creating, updating, filtering, and annotatingfor more knowledge refer to this-https://docs.djangoproject.com/en/4.1/ref/models/expressions/#query-expressions

Q. How to use When() and when in Django ORM?

Ans. When() is a conditional expression it provides the functionality of if-elif-else logic use you can use. When() while filtering, annotate, update and aggregate. For more knowledge, refer to this-https://docs.djangoproject.com/en/4.1/ref/models/conditional-expressions/#when

Q. What is Case() In Django ORM?

Ans. Case() is an expression of if-elif-else statement in Django. Case() is always used with when. For more info refer to this-https://docs.djangoproject.com/en/4.1/ref/models/conditional-expressions/#case

Q. How and when to use Trunc?

Ans.We will understand Trunc with the example:

Suppose you have a shop and you want to know what was the sale of the previous day, week, month, quarter, or year you can achieve this by using Trunc. Basically, it provides the functionality of group by date. For more info refer to this - https://docs.djangoproject.com/en/4.1/ref/models/database-functions/#trunc

Q. What is SubQuery and OuterRef ?
Ans. We use SubQuery when we need to add a field into a query set and OuterRef uses where you need to add a field from SubQuery and that refers to the field outer query. For more info refer to this-https://docs.djangoproject.com/en/4.1/ref/models/expressions/#subquery-expressions,https://docs.djangoproject.com/en/4.1/ref/models/expressions/#django.db.models.OuterRef

We will understand with the example:

Explanation - Consider there is the model of service which includes the service name, service start date, service end date, service charge, and distance,now we will use this model to use the above concept.

class Service(models.Model):

id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)

service_name = models.CharField(
null=True,
blank=True,
max_length=25)

start_date = models.DateTimeField(
null=True,
blank=True)

end_date = models.DateTimeField(
null=True,
blank=True)

service_charge = models.DecimalField(
max_digits=25,
decimal_places=2,
null=True,
blank=True)

distance= models.DecimalField(
verbose_name="Destination from Service center",
max_digits=25,
decimal_places=2,
null=True,
blank=True)

class ServiceProvider(models.Model):

id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)

service= models.ForeignKey(
'Service',
on_delete=models.SET_NULL,
null=True,
blank=True)

service_provider_name = models.CharField(
null=True,
blank=True,
max_length=25)

Pragmatic Example of Query Expression:

Suppose you want to extract the time duration of service, so how you can subtract the start date from the end date

from django.db.models.functions import ExtractMinute, ExtractHour

from django.db.models import F, ExpressionWrapper,IntegerField,Sum, When, Case, OuterRef, Subquery

def calculateDurationExpression():
durationExpression = F('end_date') - F('start_date')
duration_wrapper = ExpressionWrapper(durationExpression, output_field=DurationField())
durationMin = ExtractMinute(duration_wrapper)
durationHrs = ExtractHour(duration_wrapper)
return durationHrs, durationMin

durationmin, durationhrs = calculateDurationExpression()

serviceObj = Service.objects.filter(start_date__gte="2001-01-01", end_date__lte="2001-02-31").annotate(
durationMin=durationmin, durationHrs=durationhrs)

After this execution, you can access durationMin, and durationHrs: .durationMin and .durationHrs

Pragmatic Example of Trunc(),Case and When():

Suppose you want service data that shows the quarterly growth of service and visualize the data where service charges greater than 5,000 and less than 10000

service_data= Service.objects.filter(start_date__gte="2001-01-01", end_date__lte="2001-12-31").annotate(
durationMin=durationmin, durationHrs=durationhrs,customer=Case(when(service_charge__gte=5000, service_charge__lte=10000, then=1), output_field=IntegerField()).annotate(service_date=Trunc('start_date', 'quarter', output_field=DateTimeField())).values('service_date').annotate(CustomerStrength=Sum(F('customer')),gainServiceCharges=SumF('service_charge')

Note: You can also useyear,quarter,month,week,day, daily

From the above query execution, you will get the growth record of the service.

Pragmatic Example of OuterRef and SubQuery:

Suppose you want service data with the service provider name with each row with SubQuery and OuterRef you can achieve from this

from django.db.models import OuterRef, Subquery

serviceObj = Service.objects.filter(start_date__gte="2001-01-01", end_date__lte="2001-02-31").annotate(serviceProvider=SubQuery(ServiceProvider.objects.filter(id=OuterRef('id')).values('service_provider_name')))

After execution of the above query, you can access the service provider name ex- .serviceProvider

I hope this is good to understand the use of the Django Advance ORM concept.

We, at Oodles, provide 360-degree enterprise web app development services to help enterprises strengthen their digital presence and seamlessly run their operations through an intuitive application UI. Our team specializes inPython-based programming usingframeworks like Django and Flask to build custom enterprise web solutions from the ground up. Contact us at [email protected] to learn more about our ERP application development services.