Django ORM 2

Django ORM에 대해 좀 더 알아보자

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

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

sql query

select item_no, sum(qty) as total_qty
 from item
where delete=False
group by item_no
ORM에서는 이와 같이 values() 메소드와 order_by() 메소드에 같이 지정해주면 동일한 쿼리를 할 수 있습니다.

ORM query

Item.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만 쿼리하고 싶을 때

group 별로 첫번째 row만 쿼리하려는 상황

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

Model

class 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 query

Score.objects.order_by(
    'student_no__username', '-date'
).distinct(
    'student_no__username'
)

아래 쿼리에서 눈여겨볼 점은, 동일한 점수가 존재할 수 있기 때문에 date라는 컬럼을 정렬조건으로 추가한 것입니다.
순서가 중요합니다.
order_by(‘-date’, ‘student_no__username’)는 불가능합니다

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

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

Model

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

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

QuerySet

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