[Django] 반드시 알아야 할 5가지ORM 쿼리

10 min readOct 27, 2019

비지니스 로직이 복잡한 상황에서 어떻게 ORM쿼리를 해야하는지 알아봅니다. 구글링으로 쉽게 찾기 어려운 ORM꿀팁을 가득 담았습니다!

기초편

기초적인 Join, Orderby, Aggregation(Average, Count, Max)는 django cookbook을 읽어보시면 도움이 많이 됩니다. 따라서 기초편의 글은 작성하지 않습니다 :)
https://django-orm-cookbook-ko.readthedocs.io/en/latest/index.html

심화편

1. Group by의 컬럼을 명시적으로 지정하고 싶을 때

sql쿼리에서는 select 절과 group by 절에 명시적으로 지정해 주면 group by 할 컬럼을 정해줄 수 있습니다.

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

ORM에서는 이와 같이 values() 메소드와 order_by() 메소드에 같이 지정해주면 동일한 쿼리를 할 수 있습니다.

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

Item 모델에서 item_no컬럼을 기준으로 group by를 하는 쿼리입니다.

2. Group by 후 특정 기준으로 그룹별로 하나의 row만 쿼리하고 싶을 때

학생과 점수 모델이 존재하고, 점수는 학생을 외래키로 참조할때, 학생별로 가장 최신 치른 시험의 점수를 쿼리하고 싶은 상황입니다

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

점수데이터를 학생명과 날짜별로 정렬하고, 학생명별로 distinct() 메소드로 설정하여 하나의 row만 쿼리하게 됩니다.

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

아래 쿼리에서 눈여겨볼 점은, 동일한 점수가 존재할 수 있기 때문에 date라는 컬럼을 정렬조건으로 추가한 것입니다.

순서가 중요합니다.
order_by(‘-date’, ‘student_no__username’)는 불가능합니다

3. 하나의 모델에서 독립적인 여러 조건으로 Aggregation 하고 싶을 때

상품의 리뷰데이터 모델을 가정합니다. 리뷰는 review_type이라는 컬럼으로 타입을 구분하고 있습니다.

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

한번의 쿼리로 모든 리뷰의 개수와, review_type=1인 리뷰의 개수는 어떻게 구할 수 있을 까요?

# QuerySetfrom django.db.models import (Sum, Count, Case, When, Avg,
                              IntegerField, Value)ItemReview.objects.filter(
    deleted=False
).annotate(
    product_review_type=Case(
        When(
            review_type=1,
            then=1
        ), default=0, output_field=IntegerField()
    )
).aggregate(
    product_review_count=Coalesce(
        Sum('product_review_type'), Value(0)
    ),
    all_review_count=Coalesce(
        Count('item_review_no')
    ),
    average_point=Coalesce(
        Avg('point'), Value(0)
    ),
)

annotate()는 주석이라는 뜻으로, 기존 컬럼값을 manipulate하여 새로운 컬럼의 값으로 생성하는 역할을 합니다. annotate를 활용하여 review_type이 특정값인 리뷰를 1, 아닌 경우 0인 값이 되는 product_review_type컬럼을 하나 추가합니다.

aggregate함수는, 모든 컬럼을 합쳐주는 기능을 합니다. annotate()에서 생성한 product_review_type컬럼의 값을 모두 더하면 특정 review_type인 총 리뷰의 개수를 구할 수 있습니다.

all_review_count는 모든 리뷰의 수, average_point는 모든 리뷰의 평점을 계산하는 예시입니다.

4. Exists, Not Exists

쿼리에서는 Exists 함수를 쉽게 사용합니다.

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

장고에서는 OuterRef함수를 사용하여 서브쿼리를 작성해야합니다. 서브쿼리로 들어갈 쿼리셋을 미리 작성할 때, 메인쿼리와 조인할 컬럼을 OuterRef메소드를 지정해줍니다.

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

B에 존재하는 A의 row를 식별하기 위하여 annotate로 새로운 컬럼을 생성해주고 filter에서 True로 존재하는 row만 제한하여 쿼리해줍니다. SQL에서 NotExists는 ~Exists()로 쿼리할 수 있습니다.

qs = A.objects.filter.annotate(
    joined_example_no=Exists(subquery)
).filter(
    joined_example_no=True
)

5. 일대다 (1:Many) 모델에서 many의 값을 aggregate하여 1 모델에 업데이트하고 싶을 때

학생과 점수 모델이 있습니다

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

학생의 수학, 영어 총 시험점수를 합하여 학생의 모델에 갱신해주고 싶을 때

# 업데이트할 점수를 aggregate하는 서브쿼리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)
    ),
)

위의 쿼리를 보면 앞 전 주제에서 보았던 values()메소드로 명시적으로 group by 할 컬럼을 지정해주었고, OuterRef()메소드에 메인메소드로 조인할 컬럼을 지정하여 주었습니다.

# 위의 서브쿼리를 Student모델에 업데이트하는 메인쿼리셋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')
)

조금더 간결한 방법은 아래와 같습니다.

Student.object.update(
    total_eng_score=Subquery(
        score_aggr.values('total_eng_score')[:1]
    ),
    total_maht_score=Subquery(
        score_aggr.values('total_math_score')[:1]
    )
)

추가적으로, aggregate한 값들에 조건을 주고 싶은 경우에는 위의 방식으로 활용해야 합니다. 총합한 점수가 0보다 작은 경우에는 0으로 바꿔서 업데이트를 하는 경우입니다.

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()
    )
)

약 1년 반동안 django를 활용하여 비지니스 모델을 개발하면서 복잡했던 ORM에 대하여 정리하여 공유드렸습니다. 부디 도움이 많이 되시길 바랍니다.