[Django] [Queryset] scalar subquery 사용하기

[Queryset] scalar subquery 사용하기 | Django

2020.02.18 00:44:23 | 2024.10.26 14:26:18 | 481 | Aiden

❮ ❯

1. annotate의 RawSQL 이용 방식

(BlogVenv) C:\Work\Software\eclipse-jee-2019-12-R-win32-x86_64\workspace\Blog>python manage.py shell
>>> from menu.models import Menu
>>> from django.db.models.expressions import RawSQL

>>> qs = Menu.objects.filter(menu_isEnabled=True)
>>> qs = qs.annotate(post_cnt=RawSQL("select count(*) from blog_post where blog_post.menu_uid = blog_menu.menu_uid", ()))
>>> for m in qs:
...     print(m.menu_name, m.post_cnt)
...
[DEBUG] [2020-02-18 00:36:18,694] (0.019) QUERY = 'SELECT [blog_menu].[menu_uid]
, [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_uid]
, [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(*) 
    from blog_post 
    where blog_post.menu_uid = blog_menu.menu_uid) AS [post_cnt] 
FROM [blog_menu] 
WHERE [blog_menu].[menu_isEnabled] = %s 
ORDER BY [blog_menu].[tree_id] ASC, [blog_menu].[lft] ASC' - PARAMS = (1,); args=(True,)

Program 0
Python 2
Django 0
JAVA 0
C (C++) 0
C# 0
ASP 0
ASP.NET 0
PHP 0
UI Script 0
Polymer (EMRO Framework) 0
javascript 0
jQuery 0
Flex (EMRO Framework) 0
Database 0
DA 0
Oracle 0
Tibero 0
MSSQL 0
SQLite 0
MySQL 0
DB2 0
Informix 0
OS 0
Windows NT 0
Linux 0
Solaris (Unix) 0
WAS (+ Web Server) 0
Tomcat 0
WebLogic 0
ETC. 0
DB Script Export Package 0
보안 0
Network 0
EMWAC 0
Search 0
>>>

2. extra 이용 방식

>>> qs = Menu.objects.filter(menu_isEnabled=True).extra(select={"post_cnt":"select count(*) from blog_post where blog_post.menu_uid = blog_menu.menu_uid"},)
>>>
>>>
>>> for m in qs:
...     print(m.menu_name, m.post_cnt)
...
[DEBUG] [2020-02-18 00:43:11,935] (0.017) QUERY = 'SELECT (select count(*) from blog_post where blog_post.menu_uid = blog_menu.menu_uid) AS [post_cnt]
, [blog_menu].[menu_uid]
, [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_uid]
, [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]
FROM [blog_menu]
WHERE [blog_menu].[menu_isEnabled] = %s
ORDER BY [blog_menu].[tree_id] ASC, [blog_menu].[lft] ASC' - PARAMS = (1,); args=(True,)

Program 0
Python 2
Django 0
JAVA 0
C (C++) 0
C# 0
ASP 0
ASP.NET 0
PHP 0
UI Script 0
Polymer (EMRO Framework) 0
javascript 0
jQuery 0
Flex (EMRO Framework) 0
Database 0
DA 0
Oracle 0
Tibero 0
MSSQL 0
SQLite 0
MySQL 0
DB2 0
Informix 0
OS 0
Windows NT 0
Linux 0
Solaris (Unix) 0
WAS (+ Web Server) 0
Tomcat 0
WebLogic 0
ETC. 0
DB Script Export Package 0
보안 0
Network 0
EMWAC 0
Search 0
>>>

3. annotate 이용 방식
Menu.objects.filter(menu_isEnabled=True).annotate(post_cnt=Count('post')) <-- 이렇게 하면 안되고 Post Model의 Menu Foreign Key Relationship name을 지정해야 된다.
Django 공식 Doc.에서는
# https://docs.djangoproject.com/en/3.0/topics/db/aggregation/#joins-and-aggregates
"~ models that are related to the one you are querying can include traversing “reverse” relationships.
The lowercase name of related models and double-underscores are used here too."
이렇게 reverse Foreign Key 탐색이 된다고 되어 있고,
공식 예제에는 테이블 명만 지정되어 있는데 아마도 related_name을 지정하지 않았을 경우로 추정되고,
나처럼 명시한 경우는 related_name 속성에 지정한 명칭으로 해야 하는것 같다.

그런데 실행된 쿼리를 보면 blog_menu 와 blog_post를 join 하여 group by 하는 특이한 방식으로 되어 있다.
쿼리의 효율성을 따저보고 사용해야 한다.

>>> from menu.models import Menu
>>> from django.db.models import Count
>>> qs = Menu.objects.filter(menu_isEnabled=True).annotate(post_cnt=Count('menu_post')) <-- filter, annotate 순서는 상관없다. 똑같은 쿼리 실행된다.
>>> for m in qs:
...     print(m.menu_name, m.post_cnt)
...
[DEBUG] [2020-02-18 01:12:35,085] (0.020) QUERY = 'SELECT [blog_menu].[menu_uid]
, [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_uid]
, [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]
, COUNT_BIG([blog_post].[post_uid]) AS [post_cnt] 
FROM [blog_menu]
LEFT OUTER JOIN [blog_post] ON ([blog_menu].[menu_uid] = [blog_post].[menu_uid])
WHERE [blog_menu].[menu_isEnabled] = %s
GROUP BY [blog_menu].[menu_uid]
, [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_uid]
, [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] 
ORDER BY [blog_menu].[tree_id] ASC, [blog_menu].[lft] ASC' - PARAMS = (1,); args=(True,)

Program 0
Python 2
Django 0
JAVA 0
C (C++) 0
C# 0
ASP 0
ASP.NET 0
PHP 0
UI Script 0
Polymer (EMRO Framework) 0
javascript 0
jQuery 0
Flex (EMRO Framework) 0
Database 0
DA 0
Oracle 0
Tibero 0
MSSQL 0
SQLite 0
MySQL 0
DB2 0
Informix 0
OS 0
Windows NT 0
Linux 0
Solaris (Unix) 0
WAS (+ Web Server) 0
Tomcat 0
WebLogic 0
ETC. 0
DB Script Export Package 0
보안 0
Network 0
EMWAC 0
Search 0
>>>

4. annotate 이용 방식 (3번 방식에서 조건있는 서브쿼리)

>>> qs = Menu.objects.annotate(post_cnt=Count("menu_post", filter=Q(menu_post__post_isEnabled=True))).filter(menu_isEnabled=True)
>>> for m in qs:
...     print(m.menu_name, "----->", m.post_cnt)
...
[DEBUG] [2020-02-19 00:06:57,862] (0.016) QUERY = 'SELECT [blog_menu].[menu_uid]
, [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_uid]
, [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]
, COUNT_BIG(CASE WHEN [blog_post].[post_isEnabled] = %s THEN [blog_post].[post_uid] ELSE NULL END) AS [post_cnt]
FROM [blog_menu]
LEFT OUTER JOIN [blog_post] ON ([blog_menu].[menu_uid] = [blog_post].[menu_uid])
WHERE [blog_menu].[menu_isEnabled] = %s
GROUP BY [blog_menu].[menu_uid]
, [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_uid]
, [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]

ORDER BY [blog_menu].[tree_id] ASC, [blog_menu].[lft] ASC' - PARAMS = (1, 1); args=(True, True)

Program -----> 0
Python -----> 1
Django -----> 0
JAVA -----> 0
C (C++) -----> 0
C# -----> 0
ASP -----> 0
ASP.NET -----> 0
PHP -----> 0
UI Script -----> 0
Polymer (EMRO Framework) -----> 0
javascript -----> 0
jQuery -----> 0
Flex (EMRO Framework) -----> 0
Database -----> 0
DA -----> 0
Oracle -----> 0
Tibero -----> 0
MSSQL -----> 0
SQLite -----> 0
MySQL -----> 0
DB2 -----> 0
Informix -----> 0
OS -----> 0
Windows NT -----> 0
Linux -----> 0
Solaris (Unix) -----> 0
WAS (+ Web Server) -----> 0
Tomcat -----> 0
WebLogic -----> 0
ETC. -----> 0
DB Script Export Package -----> 0
보안 -----> 0
Network -----> 0
EMWAC -----> 0
Search -----> 0
>>>

개선된 방법 : https://blog.daonelab.com/post/12/1806/