django 쿼리에 조건적 annotate 붙이기

django 쿼리에 조건적 annotate 붙이기

문제

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

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

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

Case == if .. elif .. else

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

Caseif 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 부분이 받은 개수를 연산하는 부분이다. (destsource로 바뀐 점 빼고는 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)