1st_orm.md

ORM (Object Relational Mapping)

• 객체와 관계형 데이터베이스간의 매핑

• 직관적이며 재사용 및 유지보수가 용이함

• DBMS에 종속적이지 않음

1. How to find the query associated with a queryset?

queryset.query

>>> queryset = Event.objects.all()
>>> str(queryset.query)
'SELECT "events_event"."id", "events_event"."epic_id", "events_event"."details", "events_event"."years_ago" FROM "events_event"'
>>> print(queryset.query)
SELECT "events_event"."id", "events_event"."epic_id", "events_event"."details", "events_event"."years_ago" FROM "events_event"

>>> queryset = Event.objects.filter(years_ago__gt=5)
>>> str(queryset.query)
'SELECT "events_event"."id", "events_event"."epic_id", "events_event"."details", "events_event"."years_ago" FROM "events_event" WHERE "events_event"."years_ago" > 5'

django/db/models/sql/query.py

>>> queryset.query
<django.db.models.sql.query.Query object at 0x10fa76748>

queryset.query.sql_with_params()
('SELECT "events_event"."id", "events_event"."epic_id", "events_event"."details", "events_event"."years_ago" FROM "events_event" WHERE "events_event"."years_ago" > %s', (5,))

2. How to do OR queries in Django ORM?

queryset_1 | queryset_2

queryset = User.objects.filter(
    first_name__startswith='R'
) | User.objects.filter(
    last_name__startswith='D'
)

filter(Q(<condition_1>)|Q(<condition_2>))

from django.db.models import Q
queryset = User.objects.filter(Q(first_name__startswith='R')|Q(last_name__startswith='D'))

Q objects in Django Docs

django/db/models/query_utils.py

Q object 를 사용하는게 더 간편해보이는데 queryset_1 | queryset_2 는 그럼 언제 쓰는거지?

Q object로 한번에 쓰지 않는 상황이 있겠지?

def get_speakers(need_more=False):
    speakers = User.objects.filter(first_name__startswith='N')

    if need_more:
        speakers = speakers | User.objects.filter(first_name__startswith='S')

    return speakers

3. How to do AND queries in Django ORM?

filter(<condition_1>, <condition_2>)

queryset = User.objects.filter(
    first_name__startswith='R',
    last_name__startswith='D'
)

queryset_1 & queryset_2

queryset = User.objects.filter(
    first_name__startswith='R'
) & User.objects.filter(
    last_name__startswith='D'
)

filter(Q(<condition_1>) & Q(condition_2))

queryset = User.objects.filter(
    Q(first_name__startswith='R') &
    Q(last_name__startswith='D')
)

4. How to do a NOT query in Django queryset?

exclude(<condition>)

queryset = User.objects.exclude(id__lt=5)

SELECT "auth_user"."id", "auth_user"."password", "auth_user"."last_login", "auth_user"."is_superuser", "auth_user"."username", "auth_user"."first_name", "auth_user"."last_name", "auth_user"."email", "auth_user"."is_staff", "auth_user"."is_active", "auth_user"."date_joined" 
FROM "auth_user" 
WHERE NOT ("auth_user"."id" < 5)

filter(~Q(<condition>))

queryset = User.objects.filter(~Q(id__lt=5))

SELECT "auth_user"."id", "auth_user"."password", "auth_user"."last_login", "auth_user"."is_superuser", "auth_user"."username", "auth_user"."first_name", "auth_user"."last_name", "auth_user"."email", "auth_user"."is_staff", "auth_user"."is_active", "auth_user"."date_joined" 
FROM "auth_user" 
WHERE NOT ("auth_user"."id" < 5)

5. How to do union of two querysets from same or different models?

.union()

• 둘 이상의 쿼리셋 결과를 결합한다. (JOIN 과는 다름)
• 동일한 필드와 데이터타입을 갖는 queryset 에서만 사용할 수 있다.

동일 모델

qs1 = Event.objects.filter(years_ago=5)
qs2 = Event.objects.filter(epic_id__gt=50)

qs1.union(qs2)

SELECT "events_event"."id", "events_event"."epic_id", "events_event"."details", "events_event"."years_ago" 
FROM "events_event" 
WHERE "events_event"."years_ago" = 5 
UNION 
SELECT "events_event"."id", "events_event"."epic_id", "events_event"."details", "events_event"."years_ago" 
FROM "events_event" 
WHERE "events_event"."epic_id" > 50

다른 모델

qs1 = Hero.objects.values_list('name', 'category')
qs2 = Villain.objects.values_list('name', 'category')

qs1.union(qs2)

SELECT "entities_hero"."name", "entities_hero"."category_id" 
FROM "entities_hero" 
UNION 
SELECT "entities_villain"."name", "entities_villain"."category_id" 
FROM "entities_villain"

union() in Django Docs

• The UNION operator selects only distinct values by default. To allow duplicate values, use the all=True argument.
• In addition, only LIMIT, OFFSET, COUNT(*), ORDER BY, and specifying columns (i.e. slicing, count(), order_by(), andvalues()/values_list() )are allowed on the resulting QuerySet. 

6. How to select some fields only in a queryset?

values / values_list

User.objects.filter(
    first_name__startswith='R'
).values('first_name', 'last_name')

User.objects.filter(
    first_name__startswith='R'
).values_list('first_name', 'last_name')

.only()

queryset = User.objects.filter(
    first_name__startswith='R'
).only("first_name", "last_name")

	values	values_list	only
쿼리셋 내용	dict	tuple	model instance

SQL을 비교해보면 only는 id 를 fetch 한다는 차이가 있음.

.defer() 와 .only()

defer()

모델 필드가 너무 많아 python 객체로 만드는 것이 비싼 경우, 로드하지 않을 필드를 설정할 수 있다.

queryset 결과 사용시 데이터베이스에서 특정 필드를 검색하지 않는다.

Entry.objects.defer("headline", "body")

defer()에 정의한 지연 필드에 접근할 때 마다 데이터베이스에서 조회한다.

only()

지연되면 안되는 필드를 설정한다. only()가 호출될 때 즉시 로드된다.

defere()에 대부분의 필드를 써야하는 상황이라면 only()를 사용하여 단순한 코드를 작성할 수 있다.

Person.objects.only("name")

All of the cautions in the note for the defer() documentation apply toonly() as well. Use it cautiously and only after exhausting your other options.

7. How to do a subquery expression in Django?

from django.db.models import Subquery
users = User.objects.all()
UserParent.objects.filter(user_id__in=Subquery(users.values('id')))

좀 더 복잡한것..

각 카테고리 별로, 자비심 많은 hero 구하기.

class Category(models.Model):
    name = models.CharField(max_length=100)


class Hero(models.Model):
    # ...
    name = models.CharField(max_length=100)
    category = models.ForeignKey(Category, on_delete=models.CASCADE)

    benevolence_factor = models.PositiveSmallIntegerField(
        help_text="How benevolent this hero is?",
        default=50
    )

hero_qs = Hero.objects.filter(
    category=OuterRef("pk")
).order_by("-benevolence_factor")

Category.objects.all().annotate(
    most_benevolent_hero=Subquery(
        hero_qs.values('name')[:1]
    )
)

• Subquery에서 외부 필드를 참조해야하는 경우 OuterRef를 사용한다. F expression과 유사

• OuterRef를 사용한 쿼리를 단독으로 사용할 수는 없음.

• >>> hero_qs
  ValueError: This queryset contains a reference to an outer query and may only be used in a subquery.