[Django] 5 ORM queries you should know!

Junyeong Choi
Nov 3 · 3 min read

I want to share very import Django ORM query in business logic which you can’t find easily in google search. English is not native language to my, so I hope you understand.

Easy part

You can get basic ORM query like Join, OrderBy, Aggregation(Sum, Count, Max) in django cookbook. So, easy part is done! yeah 😆

Difficult part

1. When you want to explicitly specify a column in Group by statement.

# sql queryselect item_no, sum(qty) as total_qty
from item
where delete=False
group by item_no

You can get same query by specifying columns in values() method and order_by() method.

# ORM queryItem.objects.filter(
deleted=False
).values(
'item_no'
).annotate(
total_qty=Coalesce(Sum('qty'), Value(0)),
).order_by(
'item_no'
)

2. When you want to get just one row in each group

you want to query only the first row by group

Suppose a student has many score data, so score model has a student primary key for foreign key.

# Modelclass Student(Model):
student_no = IntegerField()
username = CharField(unique = True)
class Score(Model):
score_no = IntegerField()
student_no = ForeignKey(Student)
date = DateTimeField()
score = IntegerField()

How can you only get the first row of a group?

# ORM queryScore.objects.order_by(
'student_no__username', '-date'
).distinct(
'student_no__username'
)

Sort by the date column of score data and query by student name.

Order is important (distinct column, order column) .

order_by(‘-date’, ‘student_no__username’) is impossible.

3. When you want to aggregate under a number of independent conditions in a single model.

# Modelfrom django.db import modelsclass ItemReview(Model):
pk = BigAutoField()
item_no = ForeignKey(Item)
point = IntegerField()
review_type = IntegerField() # 0: text, 1: photo

ItemReview model has various type. 0 is text review, 1 is photo review. How can you query the total number of reviews and the photo reviews at once?

# QuerySetfrom django.db.models import (Sum, Count, Case, When, Avg,
IntegerField, Value)
ItemReview.objects.filter(
deleted=False
).annotate(
photo_review_type=Case(
When(
review_type=1,
then=1

), default=0, output_field=IntegerField()
)
).aggregate(
photo_review_count=Coalesce(
Sum('photo_review_type'), Value(0)
),
all_review_count=Coalesce(
Count('item_review_no')
),
average_point=Coalesce(
Avg('point'), Value(0)
),
)

Annotate function can generate new columns based on existing data, so add a column to check a review is photo type or not.

Aggregate function is that sum values of a column, so photo_review_count is sum of photo reviews.

4. Exists, Not Exists

Exists statement is very in sql.

select *
from a
where
exists(
select 1
from b
where b.example_no=a.example_no
)

Using OuterRef, You can use function of exists in django.

subquery = B.objects.filter(
example_no=OuterRef('example_no')
)

Specify the column to join in the main queryset in OuterRef function.

# Main querysetA.objects.filter.annotate(
joined_example_no=Exists(subquery)
).filter(
joined_example_no=True
)

Create a new column with subquery result and filter the annotated column.

5. When you want to update a model with aggregated values.

Suppose student and score model as you seen above.

# Modelclass Student(Model):
username = CharField(max_length=200,unique = True)
total_eng_score = IntegerField()
total_math_score = IntegerField()
class Score(Model):
student = ForeignKey(Student)
date = DateTimeField()
score = IntegerField()
type = CharField() # type: eng, math

If you want to update total score to the student model.

# Aggregated subquerysetscore_aggr = Score.objects.filter(
student=OuterRef('student')
).values(
'student'
).annotate(
sum_eng_score=Coalesce(
Sum(
Case(
When(
type='eng',
then=F('score'),
),
default=0,
output_field=IntegerField()
)
),
Value(0)
),
sum_math_score=Coalesce(
Sum(
Case(
When(
type='math',
then=F('score'),
),
default=0,
output_field=IntegerField()
)
),
Value(0)
),
)

Specify student column to values() for group by and to OuterRef for subquery.

# Main querysetStudent.object.annotate(
aggr_total_eng_score=Subquery(
score_aggr.values('sum_eng_score')[:1],
output_field=IntegerField()
),
aggr_total_math_score=Subquery(
score_aggr.values('sum_math_score')[:1],
output_field=IntegerField()
),
).update(
total_eng_score=F('aggr_total_eng_score'),
total_math_score=F('aggr_total_math_score')
)

Annotate the result of subquery and declare the annotated columns to update function.

In addition, what if you wan to put conditions on the aggregated results?

Student.object.annotate(
aggr_total_eng_score=Subquery(
score_aggr.values('total_eng_score')[:1],
output_field=IntegerField()
)
).update(
total_eng_score=Case(
When(
aggr_total_eng_score__lt=0,
then=0
),
default=F('aggr_total_eng_score'),
output_field=IntegerField()
)
)

You can use aggregate value in ORM like above example.

I have shared 5 ORM know-how I have about a year’s work. I hope this post to be helpful to you. 🤗 If you like it, please click Clap button.

Junyeong Choi

Written by

blog:https://chrisjune.dev, workat:@29cm

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade