django 쿼리에 조건적 annotate 붙이기

문제

모델 코드는 제일 아래에 적어두었습니다.

특정 창고에서 받은 발송장과 보낸 발송장을 참고하여, 물건의 남은 양을 파악하고 싶었다. 다시 말하면, 조건에 맞춰서 annotate를 만들 수 있을까?

개인적으로는 가독성과 유지보수성을 위해 SQL보다는 django ORM을 선호하는 편이다. 그래서 복잡한 쿼리 만들 때마다 이런 고생을 하는지도 annotate랑 F 표현식을 어떻게 조합하면 될 것도 같은데...하며 골머리를 싸매다가, 공식 문서에서 conditional expressions라는 항목을 발견했다.

Case == if .. elif .. else

조건적인 annotate에 사용하는 클래스는 Case와 Value, When.

Case는 if elif else랑 비슷하다고 볼 수 있다. 아래는 발송장(Invoice)의 수신지(dest)가 특정 창고(inventory)인 경우(When), 물품 목록(stock_entry)에 포함된 물품의 개수(quantity)를 가리킨다(=발송 개수). 해당되는 쿼리셋이 없다면 0을 반환한다(default=0).

from django.db.models import Case, When

Case(
    When(
        invoice__dest=inventory,
        then='stock_entry__quantity'
    ),
    default=0
)

특정 창고에서 보낸 개수 파악하기

이 Case 문을 실제 모델(Stock) 객체에 붙여보자. 위의 Case 문에 Sum 객체를 덮은 후, annotate를 사용하여 Stock 객체에 붙였다.

stock_list = Stock.objects.annotate(
    received_stocks=Sum(
        Case(
            When(
                stock_entry__invoice__dest=inventory,
                then='stock_entry__quantity'
            ),
            default=Value(0)
        )
    ),
)

이렇게 하면, stock_list[0].received_stocks를 통해 각 물품의 보낸 개수를 확인할 수 있다.

특정 창고에서 받은 개수 확인하기

아래의 sent_stocks 부분이 받은 개수를 연산하는 부분이다. (dest가 source로 바뀐 점 빼고는 received와 똑같다.)

stock_list = Stock.objects.annotate(
    received_stocks=Sum(
        Case(
            When(
                stock_entry__invoice__dest=inventory,
                then='stock_entry__quantity'
            ),
            default=0
        )
    ),
    sent_stocks=Sum(
        Case(
            When(
                stock_entry__invoice__source=inventory,
                then='stock_entry__quantity'
            ),
            default=0
        )
    )
)

stock_list[0].sent_stocks를 통해 각 물품의 보낸 개수를 확인할 수 있다.

특정 창고에서 물품별 재고 파악하기

이제 각 객체에서 received_stocks 값과 sent_stocks 값을 연산하면 재고를 파악할 수 있다. (밑에서 세 번째 줄의 annotate 구문)

stock_list = Stock.objects.annotate(
    received_stocks=Sum(
        Case(
            When(
                stock_entry__invoice__dest=inventory,
                then='stock_entry__quantity'
            ),
            default=0
        )
    ),
    sent_stocks=Sum(
        Case(
            When(
                stock_entry__invoice__source=inventory,
                then='stock_entry__quantity'
            ),
            default=0
        )
    )
).annotate(
    remains=F('received_stocks') - F('sent_stocks')
)

더 자세한 내용이 궁금하다면, 공식 문서의 F 표현식, Case 객체, conditional aggregation 등을 참고하길 바란다.

참고용 소스

모델들이 어떻게 연결되어 있는지 궁금해 할 분도 있을 것 같아, 남겨본다.

class Inventory(models.Model):

    class Meta:
        verbose_name = "창고"

    title = models.CharField('이름', max_length=150)


class Invoice(TimeStampedModel):

    class Meta:
        verbose_name = "발송장"

    source = models.ForeignKey('Inventory', related_name='sent', null=True, blank=True)
    dest = models.ForeignKey('Inventory', related_name='received')


class StockEntry(models.Model):

    class Meta:
        verbose_name = "적재 내용"

    invoice = models.ForeignKey('Invoice', related_name='stock_entry', null=True)
    stock = models.ForeignKey('Stock', related_name='stock_entry')
    quantity = models.IntegerField('수량')


class Stock(models.Model):

    class Meta:
        verbose_name = "물품"

    title = models.CharField('이름', max_length=150, unique=True)