Django ORM에서 Subquery 사용하기

사용자의 다양한 요청에 대한 응답을 하기 위해서는 DB Level에서 다양한 연산이 필요합니다.

Django ORM을 사용하게되면 직접 Query를 작성할 수도 있지만 이보다는 ORM에서 제공하는 메서드들을 활용하는 것이 더 나을 수 있습니다. 

 

왜냐하면 Raw Query로는 Django REST Framework에서 제공하는 다양한 기능(e.g Filtering, Ordering)을 사용하는데 제한이 있을 수 있기 때문입니다.

아래의 예시를 통해 Sub Query를 사용하는 방법을 알아보도록 합니다.

# models.py
class Video(models.Model):
    id = models.AutoField(primary_key=True)
    title = models.TextField(max_length=500)
    video_url = models.URLField(max_length=800, null=True)
    transcoding_status = models.CharField(max_length=20, default='processing', choices=TRANSCODE_STATUS)
    is_posted = models.BooleanField(default=False)
    
class VideoView(models.Model):
    id = models.AutoField(primary_key=True)
    user = models.ForeignKey('baseuser.BaseUser', on_delete=models.CASCADE)
    video = models.ForeignKey('video.Video', on_delete=models.CASCADE)

위의 모델에 대한 설명은 아래와 같습니다.

• Video → 영상 정보를 담고 있는 Model
• VideoView → 사용자가 어떤 영상을 조회했는지 저장하는 Model

이제 Django REST Framework에서 제공하는 Ordering을 활용하여 아래와 같이 정렬을 하려고 합니다.

• 영상의 조회수가 많은 순서대로 정렬

# views.py
class VideoViewSet(viewsets.ModelViewSet):
    queryset = Video.objects.filter(transcoding_status='complete', is_posted=True)
    serializer_class = VideoPublicSerializer
    permission_classes = (IsAuthenticated,)
    filter_backends = (OrderingFilter,)
    ordering_fields = ('view_count',)

django-filter 패키지를 설치하면 OrderingFilter라는 강력한 정렬 필터를 사용할 수 있습니다.

위의 예시에 나온 것처럼 간단한 설정을 통해 데이터 목록을 가져올 때 정렬을 수행하도록 할 수 있습니다.

하지만 문제는 Video 모델이 view_count라는 필드가 없다는 것입니다.

위의 Ordering Filter는 Model Instance에 정렬하려는 attribute가 존재해야 정상적으로 동작합니다.

즉, ViewVideo 모델에서 특정 Video Model Instance가 몇개나 있는지 갯수를 파악한 뒤 Video 모델에 결과를 포함시켜줘야 한다는 얘기가 됩니다.

이와 같은 연산을 위해서는 Sub Query가 반드시 필요합니다.

필요한 데이터는 아래와 같습니다.

1. Video에서 is_posted가 True이고 transcoding_status가 'complete'인 Video 목록
2. 위의 목록에 각각의 Video Model Instance에 대해서 VideoView Model로부터 영상 조회수를 구해서 view_count라는 항목으로 추가

먼저 항목 1에 대한 쿼리를 ORM으로 표현하면 아래와 같습니다.

Video.objects.filter(is_posted=True, transcoding_status="complete")

여기에 view_count라는 항목을 추가하기 위해 annotate 메서드를 사용합니다.

Video.objects.filter(is_posted=True, transcoding_status="complete").annotate(view_count=서브쿼리)

이제 annotate 안에 VideoView로부터 조회수를 구하는 Sub Query가 들어가면 됩니다.


가장 먼저, VideoView를 통해 각 영상의 조회수를 파악하기 위해서는 video_id값이 필요합니다.

즉, 앞에서 is_posted값이 참이고 transcoding_status가 complete인 video들에 대해서 조회수를 파악해야 한다는 얘기입니다.

그렇기 때문에 Sub Query는 위에서 필터링한 video값들의 id를 받아와야 합니다.

외부의 인자값을 받아오기 위해 Sub Query에서는 OuterRef를 활용합니다.

videoview = VideoView.objects.filter(video=OuterRef('pk'))
Video.objects.filter(is_posted=True, transcoding_status="complete").annotate(view_count=videoview.count())

위처럼 수행하면 원하는 결과값이 나올 것 같지만 에러가 발생합니다.

바로 count() 메서드 때문입니다.

count() 메서드는 해당 query를 수행하여 queryset을 갖춘 다음 그 개수를 리턴하는 함수입니다.

하지만 Sub Query의 경우 외부의 값(OuterRef를 사용한 부분)때문에 Sub Query만으로는 쿼리를 수행할 수 없습니다.

따라서 Sub Query를 사용하는 경우 count() 메서드를 사용할 수 없습니다.

이 대안으로 Count() aggregate 함수를 생각할 수 있습니다.

Video.objects.filter(is_posted=True, transcoding_status="complete").annotate(view_count=Count(VideoView.objects.filter(video=OuterRef('pk'))))

하지만 Count 함수는 Sub Query가 아닌 Aggregate 함수입니다. 따라서 위와 같이 수행할 수는 없습니다.

그렇기 때문에 Sub Query로 수행한 결과물에 Count 함수를 활용해야 합니다.

Video.objects.filter(is_posted=True, transcoding_status="complete").annotate(view_count=Count(Subquery(VideoView.objects.filter(video=OuterRef('pk')))))

하지만 다음과 같은 에러가 발생합니다.

Sub Query must return only one column

이는 Sub Query에서 리턴한 Row가 여러개이기 때문에 발생합니다.

Count를 하기 위해서는 primary key만 존재하면 되기 때문에 아래와 같이 수정합니다.

Video.objects.filter(is_posted=True, transcoding_status="complete").annotate(view_count=Count(Subquery(VideoView.objects.filter(video=OuterRef('pk')).only('pk'))))

이제 아래와 같은 에러가 발생합니다.

sub query must return one row
Sub Query는 단 하나의 row만 리턴해야 합니다

이는 Count()가 Aggregate 함수이고, 이 함수가 Sub Query내에서 사용되면 Group By 구문이 뜻하지 않는 곳에 붙게되어 위와 같은 에러가 발생하는 것입니다.

그렇기 때문에 Count()와 같은 aggregate 함수를 Sub Query안에서 사용할 때는 filter(), values(), annotate() 이 세가지 메서드를 조합하여 사용해야 합니다.

위의 쿼리를 Django ORM에 맞게 표현하면 아래와 같습니다

queryset = Video.objects.filter(transcoding_status='complete', is_posted=True). \
        annotate(view_count=Subquery(
        VideoView.objects.filter(video=OuterRef('pk')).values('video').annotate(count=Count('pk')).values('count')))

VideoView.objects.filter(video=OuterRef('pk')).values('video')

Sub Query에서 VideoView를 filtering한 뒤에 .values('video')하는 부분을 주목해야 합니다.

annotate를 사용하게 되면 실제 쿼리문에는 Group By가 들어가게 됩니다.

values는 어떤 값을 기준으로 Group By를 할 것인지 결정하게 됩니다. 

이를 명시하지 않으면 디폴트로 pk값을 기준으로 Group By를 진행하게 되는데 이렇게 할 경우 정상적으로 그루핑이 되지 않기 때문에 에러가 발생하게 됩니다.

VideoView.objects.filter(video=OuterRef('pk')).values('video').annotate(count=Count('pk'))

이제 Video별로 조회수를 구할 차례입니다. 각 영상이 VideoView에서 몇개의 row를 가지고 있는지 Count한 뒤 이를 Video별로 그루핑합니다.

이제 마지막으로 Video에 각 영상별로 조회수를 기존 Model에 추가해줍니다.

이때 Sub Query가 하나의 값만 리턴해야 annotate를 통해 Video Model에 값을 추가할 수 있으므로 다시 한번 .values()를 사용합니다.

VideoView.objects.filter(video=OuterRef('pk')).values('video').annotate(count=Count('pk')).values('count')

이제 조회수가 포함된 Video 모델이 만들어졌습니다.
하지만 여전히 한가지 문제가 더 존재합니다.

바로 VideoView에 특정 영상을 조회한 정보가 없을 때, 즉 조회수가 0일때 문제가 생깁니다.

각 Video별로 조회수를 그루핑하는 과정에서 NULL값이 발생합니다.

이것은 Group By를 진행하는 과정에서 값이 없으면 NULL을 리턴하기 때문에 발생합니다.

이 문제를 해결하기 위해서 Coalesce 함수를 사용해줍니다.
Coalesce 함수는 아래와 같이 사용합니다.

Coalsece(a,b,c,d,0)

a에서 b,c,d까지 순서대로 인자값을 검사하여 유효한 값을 리턴하는 함수입니다.

따라서 아래와 같이 쿼리를 작성하면 Sub Query의 값이 NULL이 되었을 때 다른 값으로 대체할 수 있습니다.

Coalsece(Subquery(...), 0)  # NULL대신 0을 리턴

최종 완성된 Query는 아래와 같습니다.

  queryset = Video.objects.filter(transcoding_status='complete', is_posted=True). \
        annotate(view_count=Coalesce(Subquery(
        VideoView.objects.filter(video=OuterRef('pk')).values('video').annotate(count=Count('pk')).values('count')), 0))