https://docs.djangoproject.com/en/3.0/ref/models/querysets/#filteredrelation-objects
auth_user : socialaccount_socialaccount → 1 : 0 or 1 관계이다.
auth_user를 left로 해서 socialaccount_socialaccount의 특정컬럼 조회하기는?
>>> from django.contrib.auth.models import User
>>> from django.db.models import F, FilteredRelation
1. "SocialAccount객체의 소문자 + __ + SocialAccount객체의 속성인 provider" 조합으로 provider라는 alias지정하면 알아서 reverse lookup 된다.
>>> qs = User.objects.annotate(provider=F("socialaccount__provider"))
>>>
>>> for u in qs:
... print("{} : {}".format(u.username, u.provider))
...
[DEBUG] [2020-06-21 02:32:42,406] (0.000) 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", "socialaccount_socialaccount"."provider" AS "provider" FROM "auth_user" LEFT OUTER JOIN "socialaccount_socialaccount" ON ("auth_user"."id" = "socialaccount_socialaccount"."user_id"); args=()
administrator : None
axxxxxx12 : kakao
axxxxxx : naver
user : google
jangxxx : google
jxpark : None
문법 : 메인 테이블명.objects.annotate(별명=F("조인할 테이블명 소문자__조인할 테이블에 있는 컬럼명"))
사실 설명은 테이블명이라 했지만 Django소스상 객체명이다.
2. FilteredRelation를 이용하여 Right쪽 테이블 SocialAccount의 특정 컬럼의 조건을 걸 수 있다.
# SocialAccount 라는 alias를 지정하여 조회
>>> qs = User.objects.annotate(SocialAccount=FilteredRelation('socialaccount')).filter(SocialAccount__provider='google')
>>> qs
[DEBUG] [2020-06-20 12:34:50,939] (0.000) 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" INNER JOIN "socialaccount_socialaccount" SocialAccount ON ("auth_user"."id" = SocialAccount."user_id") WHERE SocialAccount."provider" = 'google' LIMIT 21; args=('google',)
<QuerySet [<User: user>, <User: jangwon>]>
# alias지정없이 조회. 결과는 같다.
>>> qs = User.objects.filter(socialaccount__provider='google')
>>> qs
<QuerySet [<User: user>, <User: jangwon>]>
[DEBUG] [2021-05-01 15:33:50,607] (0.000) 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" INNER JOIN "socialaccount_socialaccount" ON ("auth_user"."id" = "socialaccount_socialaccount"."user_id") WHERE "socialaccount_socialaccount"."provider" = 'google' LIMIT 21; args=('google',)
3. 1번과 2번을 조합하면...
>>> qs = User.objects.annotate(SocialAccount=FilteredRelation('socialaccount')).annotate(provider=F("SocialAccount__provider"))
>>> for u in qs:
... print("{} : {}".format(u.username, u.provider))
...
[DEBUG] [2020-06-21 02:41:37,599] (0.000) 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", SocialAccount."provider" AS "provider" FROM "auth_user" LEFT OUTER JOIN "socialaccount_socialaccount" SocialAccount ON ("auth_user"."id" = SocialAccount."user_id"); args=()
administrator : None
axxxxxx12 : kakao
axxxxxx : naver
user : google
jangxxx : google
jxpark : None결과는 1번 쿼리와 같다.
FilteredRelation은 지정된 condition으로 조인하여 얻은 집합을 Alias로지정하고 이지정된 Alias집합으로 다시 조건으로 조회하도록하여 연속된 left outer join조건을 순차적으로 접근하여 성능을 높일수 있다.