방법 1.
As far as i know, aggregate는 subquery에서 사용 할 수 없다.
대신에 annotate를 사용해야 되는데, Count() 함수는 aggregate이므로 select절로 나오는 컬럼들이 자동으로group by에 추가된다.
그래서 values() + annotate() + values()구조로 해결해야 한다. 이렇게 하지 않으면 group by 절에 subquery table의 모든 column이 포함되어 집계가 원하는 대로 되지 않는다.
첫번째 values는 group by할 속성을 지정하는 용도이고, 두번째 values는 select할 one value를 의미한다.
from menu.models import Menu
from post.models import Post
from django.db.models import OuterRef, Subquery, Count
from django.db.models.functions import Coalesce
qsPostCnt = Post.objects.filter(menu=OuterRef('pk')).values("menu").annotate(cnt=Count("pk")).values("cnt")
qs = Menu.objects.annotate(post_cnt=Coalesce(Subquery(qsPostCnt), 0))
print(qs.query)
SELECT "blog_menu"."menu_id"
, "blog_menu"."menu_name"
, "blog_menu"."menu_desc"
, "blog_menu"."menu_url"
, "blog_menu"."menu_sort_order"
, "blog_menu"."menu_isEnabled"
, "blog_menu"."menu_up_id"
, "blog_menu"."site_id"
, "blog_menu"."menu_crte_user_id"
, "blog_menu"."menu_crte_dt"
, "blog_menu"."menu_mdfy_user_id"
, "blog_menu"."menu_mdfy_dt"
, "blog_menu"."lft"
, "blog_menu"."rght"
, "blog_menu"."tree_id"
, "blog_menu"."level"
, COALESCE((SELECT COUNT(U0."post_id") AS "cnt"
FROM "blog_post" U0
WHERE U0."menu_id" = "blog_menu"."menu_id"
GROUP BY U0."menu_id"), 0) AS "post_cnt"
FROM "blog_menu"
ORDER BY "blog_menu"."tree_id" ASC, "blog_menu"."lft" ASC
for m in qs:
print(m.menu_name, m.post_cnt)
Program 0
Python 51
Django 6
JAVA 1
C (C++) 0
C# 4
ASP 1
ASP.NET 2
PHP 0
UI Script 0
Polymer (EMRO Framework) 2
javascript 0
jQuery 0
Flex (EMRO Framework) 0
Database 0
DA 2
...이 쿼리는 subquery의 group by 절 때문에 해당건이 없으면 0이 아니라 null값이 나오기 때문에 추가적으로 Coalesce로 null을 0으로 치환하는 처리가 필요하다.
마음에 안든다.
방법 2.
그래서 다시 찾은 Func를 이용한 아래 방법으로 내가 원하는 결과를 얻었다. Func은 대부분의 데이터베이스에서 지원하는 함수를 기본적으로 제공하고 만약 없다면 사용자 정의로 만들어 사용할 수 있다.
Func : https://docs.djangoproject.com/en/3.0/ref/models/expressions/#func-expressions
from menu.models import Menu
from post.models import Post
from django.db.models import OuterRef, Subquery, Func
# Django 4.1에선 Post Model에서 지정한 정렬기준로 subquery에 자동으로 order by 절이 추가 되어 오류발생한다. 그래서 빈 order by절 추가하여 order by저리 생략되도록한다.
qsPostCnt = Post.objects.filter(menu=OuterRef("pk")).annotate(cnt=Func("pk", function="Count")).values("cnt").order_by()
qs = Menu.objects.annotate(post_cnt=Subquery(qsPostCnt))
print(qs.query)
SELECT "blog_menu"."menu_id"
, "blog_menu"."menu_name"
, "blog_menu"."menu_desc"
, "blog_menu"."menu_url"
, "blog_menu"."menu_sort_order"
, "blog_menu"."menu_isEnabled"
, "blog_menu"."menu_up_id"
, "blog_menu"."site_id"
, "blog_menu"."menu_crte_user_id"
, "blog_menu"."menu_crte_dt"
, "blog_menu"."menu_mdfy_user_id"
, "blog_menu"."menu_mdfy_dt"
, "blog_menu"."lft"
, "blog_menu"."rght"
, "blog_menu"."tree_id"
, "blog_menu"."level"
, (SELECT Count(U0."post_id") AS "cnt"
FROM "blog_post" U0
WHERE U0."menu_id" = "blog_menu"."menu_id") AS "post_cnt"
FROM "blog_menu"
ORDER BY "blog_menu"."tree_id" ASC, "blog_menu"."lft" ASC
for m in qs:
print(m.menu_name, m.post_cnt)
Program 0
Python 51
Django 6
JAVA 1
C (C++) 0
C# 4
ASP 1
ASP.NET 2
PHP 0
UI Script 0
Polymer (EMRO Framework) 2
javascript 0
jQuery 0
Flex (EMRO Framework) 0
Database 0
DA 2
...이제 내가 원하는 쿼리가 되었다. 하마터면 무식하게 사용 할 뻔했다. group by가 없으니 Coalesce도 필요없다.
그리고 두 방법 모두 Subquery 키워드 쓰나 안쓰나 결과는 같더라.
RawSQL을 이용한 방법 : https://blog.daonelab.com/post/12/1611/