Django – Query Expression
In the previous blog post, we have discussed F() Expression, we will now explore more query expression in Django, to name few that we will discuss in this post are
- Func() Expression
- Subquery Expression
- Aggregation () Expression
Func() Expression Func () Expression is the base of all the expressions and can be used to create your custom expression for the database level function.
# The table that we using for our query is the *Student* which keeps records of the students for the whole school.
from django.db.models import F, Func
student_obj = Student.objects.annotate(full_name=Func(F('first_name') + F('last_name'), function='UPPER')
# This will give a student object with a new field that is *full_name* of the student in upper case.
Subquery Expression Subquery are like nested condition in the query filter which helps you to make a complex query into a clean concise query. But you need to know the order of the sequence the query will be executed to use effectively. While using a Subquery you will also need to know about the OuterRef, which is like an F() Expression but points to the parent query value, let see both Subquery and OuterRef in action
# you are given a task to get the name of the student whose name starts with *S* and whose fees are due.
from django.db.models import OuterRef, Subquery
fee_objects = Fees.objects.filter(payment_due_gt=0)
student_obj = Student.objects.filter(name__startswith='S').filter(id__in=Subquery(fee_objects.values('student_id')))
# Get the lastest remarks for the students
remark = Remark.objects.filter(student_id=OuterRef('pk')).order_by('-created_at')
student_obj = Student.objects.annotate(newest_remark=Subquery(remark.values('remark_strl')[:1]))
Aggregation () Expression
Aggregation Expression is the Func Expression with GroupBy clause in the query filter.
# get the total student enrolled in the *Blind Faith* subject.
student_obj = Student.objects.filter(subject_name='blind_faith').annotate(total_count=Count('id'))
Note: All queries mentioned above in the code are not tested. So if you see any typo, a query that does not make sense, feel free to reach out to me at sandeepchoudhary1507[at]gmail[DOT]com.
Cheers!