ORM 관계형 쿼리, 조인 쿼리, 집계 쿼리, 그룹화 쿼리, F/Q 쿼리
관계형 쿼리는 크게 두 가지 유형으로 구분됩니다: 객체 기반 쿼리(서브쿼리)와 더블 언더스코어 기반 쿼리(조인 쿼리)
1. 객체 기반 관계형 쿼리(SQL: 서브쿼리)
서브쿼리: 하나의 쿼리 결과를 다른 쿼리의 조건으로 사용하는 방식
1.1 일대다 관계
"""
정방향 쿼리: 다에서 일을 찾음, 필드를 통해
역방향 쿼리: 일에서 다를 찾음, 테이블명소문자_set 형식으로, set은 집합을 의미
"""
# 정방향 쿼리(필드 기준)
# 서적 '드래곤볼'의 출판사 이름과 이메일 조회
book_instance = Book.objects.get(title='드래곤볼')
print(book_instance.publisher.name)
print(book_instance.publisher.email)
실제로 변환되는 SQL 쿼리:
(0.000) SELECT "library_book"."id", "library_book"."title", "library_book"."pub_date", "library_book"."price", "library_book"."publisher_id" FROM "library_book" WHERE "library_book"."title" = '드래곤볼' LIMIT 21; args=('드래곤볼',)
(0.000) SELECT "library_publisher"."id", "library_publisher"."name", "library_publisher"."city", "library_publisher"."email" FROM "library_publisher" WHERE "library_publisher"."id" = 2 LIMIT 21; args=(2,)
# 역방향 쿼리(테이블명: book_set, 테이블명소문자_set 형식, set은 집합 의미, QuerySet 반환)
# '한빛미디어' 출판사의 모든 서적 조회
publisher_obj = Publisher.objects.get(name='한빛미디어')
print(publisher_obj.book_set.all()) # 해당 출판사와 연관된 모든 서적
# <QuerySet [<Book: 드래곤볼>, <Book: 파이썬 마스터>]>
print(publisher_obj.book_set.values('title', 'price'))
# <QuerySet [{'title': '드래곤볼', 'price': Decimal('250.00')}, {'title': '파이썬 마스터', 'price': Decimal('180.00')}]>
1.2 다대다 관계
"""
정방향 쿼리: 필드를 통해
역방향 쿼리: 테이블명소문자_set 형식, set은 집합 의미
"""
# 정방향 쿼리(필드 기준)
# '드래곤볼'의 모든 저자 이름 조회
book_instance = Book.objects.get(title='드래곤볼')
result = book_instance.writers.all().values('name')
print(result) # <QuerySet [{'name': '토리야마'}, {'name': '에디터팀'}]>
# 역방향 쿼리(테이블명: book_set, 테이블명소문자_set 형식)
# '토리야마'가 쓴 모든 서적 이름 조회
author_instance = Author.objects.get(name='토리야마')
result = author_instance.book_set.all()
print(result) # <QuerySet [<Book: 드래곤볼>, <Book: Dr.슬럼프>]>
1.3 일대일 관계
"""
정방향 쿼리: 필드를 통해, model 객체 반환, 속성값 접근
역방향 쿼리: 테이블명소문자 형식, _set 없음; 일대일 쿼리는 단일 객체만 반환
"""
# 정방향 쿼리(필드 기준)
# '토리야마'의 전화번호 조회
author_instance = Author.objects.get(name='토리야마')
result = author_instance.contact.phone
print(result) # 010-1234-5678
# 역방향 쿼리(_set 없이 테이블명소문자 사용, 객체 속성값 접근)
# 전화번호가 '010-1234-5678'인 저자 이름 조회
contact_obj = AuthorContact.objects.get(phone='010-1234-5678')
result = contact_obj.author.name
print(result) # 토리야마
1.4 related_name으로 FOO_set 재정의
# ForeignKey()와 ManyToManyField 정의에서 related_name 값을 설정하여 FOO_set 이름을 재정의할 수 있습니다.
# 예: Book 모델에서 다음과 같이 변경
publisher = ForeignKey(Book, related_name='published_books')
# 이후 다음과 같이 사용
# '한빛미디어' 출판사의 모든 서적 조회
publisher_obj=Publisher.objects.get(name="한빛미디어")
book_collection=publisher_obj.published_books.all() # 한빛미디어와 연관된 모든 서적 객체 집합
2. 더블 언더스코어 기반 관계형 쿼리(SQL: JOIN 문)
JOIN 쿼리: 두 테이블을 특정 필드 기준으로 결합하여 큰 테이블 생성, 일정 수준에서 쿼리 효율에 영향
2.1 일대다 관계
"""
정방향 관계 쿼리(관계 필드가 있는 테이블에서 연관 테이블 조회): 관계필드__조회필드
역방향 관계 쿼리: 테이블명소문자__조회필드
기준 테이블은 중요하지 않음
"""
# 정방향 쿼리, 필드 기준; 형식: 외래키필드__연관테이블필드; 집합 리스트 반환 [{},{}]
result = Book.objects.filter(title='드래곤볼').values('publisher__name', 'publisher__email')
print(result) # <QuerySet [{'publisher__name': '한빛미디어', 'publisher__email': 'info@hanbit.co.kr'}]>
result = Book.objects.filter(publisher__name='한빛미디어').values('title')
print(result) # <QuerySet [{'title': '드래곤볼'}, {'title': '파이썬 마스터'}]>
# 역방향 쿼리, 테이블명 기준; 형식: 소문자테이블명__연관테이블필드; 집합 리스트 반환 [{},{}]
result = Publisher.objects.filter(book__title='드래곤볼').values('name', 'email')
print(result) # <QuerySet [{'name': '한빛미디어', 'email': 'info@hanbit.co.kr'}]>
result = Publisher.objects.filter(name='한빛미디어').values('book__title')
print(result) # <QuerySet [{'book__title': '드래곤볼'}, {'book__title': '파이썬 마스터'}]>
2.2 다대다 관계
다대다 JOIN의 경우 SQL에서는 세 테이블(Book, Author, book_authors 연결 테이블)이 결합된 큰 테이블이 생성됩니다.
"""
정방향 관계 쿼리(관계 필드가 있는 테이블에서 연관 테이블 조회): 관계필드__조회필드
역방향 관계 쿼리: 테이블명소문자__조회필드
기준 테이블은 중요하지 않음
"""
# 정방향 쿼리
# '드래곤볼'의 모든 저자 이름 조회
result = Book.objects.filter(title='드래곤볼').values('writers__name')
print(result) # <QuerySet [{'writers__name': '토리야마'}, {'writers__name': '에디터팀'}]>
result = Author.objects.filter(book__title='드래곤볼').values('name')
print(result) # <QuerySet [{'name': '토리야마'}, {'name': '에디터팀'}]>
# 역방향 쿼리
# '토리야마'가 쓴 모든 서적 이름 조회
result = Author.objects.filter(name='토리야마').values('book__title')
print(result) # <QuerySet [{'book__title': '드래곤볼'}, {'book__title': 'Dr.슬럼프'}]>
result = Book.objects.filter(writers__name='토리야마').values('title')
print(result) # <QuerySet [{'title': '드래곤볼'}, {'title': 'Dr.슬럼프'}]>
2.3 일대일 관계
"""
정방향 관계 쿼리(관계 필드가 있는 테이블에서 연관 테이블 조회): 관계필드__조회필드
역방향 관계 쿼리: 테이블명소문자__조회필드
기준 테이블은 중요하지 않음
"""
# 정방향 쿼리
# '토리야마'의 전화번호 조회
result = Author.objects.filter(name='토리야마').values('contact__phone')
print(result) # <QuerySet [{'contact__phone': '010-1234-5678'}]>
result = AuthorContact.objects.filter(phone='010-1234-5678').values('author__name')
print(result) # <QuerySet [{'author__name': '토리야마'}]>
# 역방향 쿼리
# 전화번호가 '010-1234-5678'인 저자 이름 조회
result = AuthorContact.objects.filter(author__name='토리야마').values('phone')
print(result) # <QuerySet [{'phone': '010-1234-5678'}]>
result = Author.objects.filter(contact__phone='010-1234-5678').values('name')
print(result) # <QuerySet [{'name': '토리야마'}]>
2.4 related_name으로 FOO_set 재정의
# 역방향 쿼리 시 related_name이 정의된 경우, 테이블명 대신 related_name을 사용
# 예: 한빛미디어 출판사의 모든 서적 이름과 가격 조회(일대다)
# 역방향 쿼리 - 테이블명: book 대신 related_name: published_books 사용
query_result=Publisher.objects
.filter(name="한빛미디어")
.values_list("published_books__title","published_books__price")
3. 관계형 쿼리 심화
3.1 다중 관계 중첩
# 정방향 관계 쿼리
# 한빛미디어 출판사의 모든 서적 이름과 저자 이름 조회
result = Book.objects.filter(publisher__name='한빛미디어').values('title', 'writers__name')
print(result)
# <QuerySet [{'title': '드래곤볼', 'writers__name': '토리야마'}, {'title': '드래곤볼', 'writers__name': '에디터팀'}, {'title': '파이썬 마스터', 'writers__name': '김파이'}, {'title': '파이썬 마스터', 'writers__name': '이썬'}]>
# 역방향 관계 쿼리
# 한빛미디어 출판사의 모든 서적 이름과 저자 이름 조회
result = Publisher.objects.filter(name='한빛미디어').values('book__title', 'book__writers__name')
print(result)
# <QuerySet [{'book__title': '드래곤볼', 'book__writers__name': '토리야마'}, {'book__title': '드래곤볼', 'book__writers__name': '에디터팀'}, {'book__title': '파이썬 마스터', 'book__writers__name': '김파이'}, {'book__title': '파이썬 마스터', 'book__writers__name': '이썬'}]>
3.2 연속 관계 쿼리
# 전화번호가 010으로 시작하는 저자가 쓴 모든 서적 이름과 출판사 이름 조회
# 방법 1:
query_result = Book.objects.filter(writers__contact__phone__startswith='010').\
values_list('title', 'publisher__name')
print(query_result) # <QuerySet [('드래곤볼', '한빛미디어'), ('Dr.슬럼프', '대원씨아이')]>
# 방법 2:
result = Author.objects\
.filter(contact__phone__startswith='010')\
.values('book__title', 'book__publisher__name')
print(result)
# <QuerySet [{'book__title': '드래곤볼', 'book__publisher__name': '한빛미디어'}, {'book__title': 'Dr.슬럼프', 'book__publisher__name': '대원씨아이'}]>
4. F 쿼리와 Q 쿼리
4.1 F 쿼리
동일한 모델 인스턴스 내 두 개의 다른 필드 값을 비교하려면 어떻게 해야 할까요? Django는 이런 비교를 위해 F()를 제공합니다. F() 인스턴스는 쿼리에서 필드를 참조하는 데 사용됩니다.
from django.db.models import F
# 댓글 수가 좋아요 수보다 많은 서적 조회
books = Book.objects.filter(comments_count__gt=F('likes_count'))
print(books) # <QuerySet [<Book: 파이썬 마스터>]>
# Django는 F() 객체 간 및 F() 객체와 상수 간의 가감승제와 모듈로 연산을 지원합니다
# 댓글 수가 좋아요 수의 2배보다 많은 서적 조회
Book.objects.filter(comments_count__gt=F('likes_count')*2)
# 수정 작업에도 F 함수 사용 가능, 예: 모든 서적 가격을 5000원 인상
Book.objects.all().update(price=F("price")+5000)
4.2 Q 쿼리
filter() 등의 메소드에서 키워드 인자 쿼리는 모두 "AND"로 결합됩니다. 더 복잡한 쿼리(예: OR 문)를 실행해야 하는 경우 Q 객체를 사용할 수 있습니다.
AND: &
OR: |
NOT: ~
from django.db.models import Q
Q(title__startswith='Python')
# Q 객체는 &와 | 연산자로 결합할 수 있습니다. 연산자가 두 Q 객체에 사용되면 새로운 Q 객체가 생성됩니다.
# 가격이 20000원 이상이거나 댓글 수가 1000개 이상인 서적 조회
books = Book.objects.filter(Q(price__gt=20000)|Q(comments_count__gt=1000))
print(books.query)
print(books) # <QuerySet [<Book: 드래곤볼>, <Book: 파이썬 마스터>, <Book: Dr.슬럼프>]>
# 다음 SQL WHERE 절과 동일:
# WHERE price > 20000 OR comments_count = 1000
# &와 | 연산자를 조합하고 괄호를 사용하여 그룹화하여 임의의 복잡한 Q 객체를 작성할 수 있습니다.
# 또한 Q 객체는 ~ 연산자를 사용하여 부정(NOT)할 수 있으며, 이는 일반 쿼리와 부정 쿼리의 조합을 허용합니다
book_list=Book.objects.filter(Q(writers__name="토리야마") & ~Q(pub_date__year=2020)).values_list("title")
# 쿼리 함수는 Q 객체와 키워드 인자를 혼합하여 사용할 수 있습니다.
# 쿼리 함수에 제공된 모든 인자(키워드 인자 또는 Q 객체)는 "AND"로 결합됩니다.
# 그러나 Q 객체가 나타나는 경우 모든 키워드 인자 앞에 위치해야 합니다.
book_list=Book.objects.filter(Q(pub_date__year=2020) | Q(pub_date__year=2021),
title__icontains="python"
)
5. 집계와 그룹화 쿼리
5.1 집계 쿼리
aggregate(*args, **kwargs)
# 모든 서적의 평균 가격 계산
from django.db.models import Avg
Book.objects.all().aggregate(Avg('price'))
# {'price__avg': 34500.0}
# aggregate()는 QuerySet의 종결 절로, 일부 키-값 쌍을 포함하는 딕셔너리를 반환합니다.
# 키의 이름은 집계 값의 식별자이며 값은 계산된 집계 값입니다.
# 키 이름은 필드와 집계 함수 이름에 따라 자동으로 생성됩니다.
# 집계 값에 이름을 지정하려면 집계 절에 제공할 수 있습니다.
Book.objects.aggregate(average_price=Avg('price'))
# {'average_price': 34500.0}
# 둘 이상의 집계를 생성하려면 aggregate() 절에 다른 매개변수를 추가할 수 있습니다.
# 따라서 모든 서적 가격의 최대값과 최소값도 알고 싶다면 다음과 같이 쿼리할 수 있습니다:
from django.db.models import Avg, Max, Min
Book.objects.aggregate(Avg('price'), Max('price'), Min('price'))
# {'price__avg': 34500.0, 'price__max': Decimal('81000.00'), 'price__min': Decimal('15000.00')}
5.2 그룹화 쿼리
annotate()는 호출된 QuerySet의 각 객체에 대해 독립적인 통계값을 생성합니다(통계 방법은 집계 함수 사용).
요약: 관계형 그룹화 쿼리의 본질은 연관된 테이블을 JOIN하여 하나의 테이블로 만든 후, 단일 테이블 그룹화 쿼리 방식으로 진행하는 것입니다.
5.2.3 쿼리 연습
(1) 각 출판사의 가장 저렴한 서적 통계
publisher_list=Publisher.objects.annotate(MinPrice=Min("book__price"))
for publisher_obj in publisher_list:
print(publisher_obj.name, publisher_obj.MinPrice)
# 객체를 순회하지 않으려면 values_list 사용:
query_result= Publisher.objects
.annotate(MinPrice=Min("book__price"))
.values_list("name","MinPrice")
print(query_result)
(2) 연습: 각 서적의 저자 수 통계
result=Book.objects.annotate(author_count=Count('writers__name'))
(3) 'Python'으로 시작하는 서적의 저자 수 통계
query_result=Book.objects
.filter(title__startswith="Python")
.annotate(author_count=Count('writers'))
(4) 저자가 1명 이상인 서적 통계
query_result=Book.objects
.annotate(author_count=Count('writers'))
.filter(author_count__gt=1)
(5) 서적의 저자 수에 따라 QuerySet 정렬
Book.objects.annotate(author_count=Count('writers')).order_by('author_count')
(6) 각 저자가 쓴 서적의 총 가격 조회
query_result=Author.objects
.annotate(TotalPrice=Sum("book__price"))
.values_list("name","TotalPrice")
print(query_result)