[Django] 5 ORM queries you should know!

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 query
select 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 query
Item.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.

# Model
class 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 query
Score.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.

# Model
from django.db import models
class 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?

# QuerySet
from 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 queryset
A.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.

# Model
class 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 subqueryset
score_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 queryset
Student.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.