이전 게시물의 User의 Social Account를 가져오는 queryset에 Groups 정보까지 포함해서 가져와보자. 

>>> from django.contrib.auth.models import User
>>> from django.db.models import F, FilteredRelation
>>>
>>> qs = User.objects.annotate(SocalAccount=FilteredRelation('socialaccount')).annotate(provider=F("socialaccount__provider"))
>>> for u in qs:
...     print(", ".join(group.name for group in u.groups.all()))
...
[DEBUG] [2020-06-21 01:42:25,224] (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=()

[DEBUG] [2020-06-21 01:42:25,226] (0.000) SELECT "auth_group"."id", "auth_group"."name" FROM "auth_group" INNER JOIN "auth_user_groups" ON ("auth_group"."id" = "auth_user_groups"."group_id") WHERE "auth_user_groups"."user_id" = 1; args=(1,)

administrators
[DEBUG] [2020-06-21 01:42:25,227] (0.000) SELECT "auth_group"."id", "auth_group"."name" FROM "auth_group" INNER JOIN "auth_user_groups" ON ("auth_group"."id" = "auth_user_groups"."group_id") WHERE "auth_user_groups"."user_id" = 2; args=(2,)

users
[DEBUG] [2020-06-21 01:42:25,228] (0.000) SELECT "auth_group"."id", "auth_group"."name" FROM "auth_group" INNER JOIN "auth_user_groups" ON ("auth_group"."id" = "auth_user_groups"."group_id") WHERE "auth_user_groups"."user_id" = 3; args=(3,)

users
[DEBUG] [2020-06-21 01:42:25,230] (0.000) SELECT "auth_group"."id", "auth_group"."name" FROM "auth_group" INNER JOIN "auth_user_groups" ON ("auth_group"."id" = "auth_user_groups"."group_id") WHERE "auth_user_groups"."user_id" = 4; args=(4,)

users
[DEBUG] [2020-06-21 01:42:25,231] (0.000) SELECT "auth_group"."id", "auth_group"."name" FROM "auth_group" INNER JOIN "auth_user_groups" ON ("auth_group"."id" = "auth_user_groups"."group_id") WHERE "auth_user_groups"."user_id" = 5; args=(5,)

users
[DEBUG] [2020-06-21 01:42:25,233] (0.000) SELECT "auth_group"."id", "auth_group"."name" FROM "auth_group" INNER JOIN "auth_user_groups" ON ("auth_group"."id" = "auth_user_groups"."group_id") WHERE "auth_user_groups"."user_id" = 6; args=(6,)

users

user instance의 groups를 조회하기 위해서 반복구문에서 u.groups.all()을 호출하고 group 정보를 가져오기 위한 쿼리가 User건수 만큼반복된다.
비효율적이다.

반복된 쿼리를 줄여주기위해서 prefetch_related를 이용하여 관계된 Table을 분리된 쿼리로 한번에 가져온다. 
이렇게 하면 실제 데이터를 사용할때 groups정보를 한번에 미리 조회한 다음 cache하여 python에서 "joining"을 수행한다.

https://docs.djangoproject.com/en/3.0/ref/models/querysets/#prefetch-related

>>> qs = User.objects.annotate(SocalAccount=FilteredRelation('socialaccount')).annotate(provider=F("socialaccount__provider")).prefetch_related("groups")
>>>
>>> for u in qs:
...     print(", ".join(group.name for group in u.groups.all()))
...
[DEBUG] [2020-06-21 01:42:44,482] (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=()

[DEBUG] [2020-06-21 01:42:44,485] (0.000) SELECT ("auth_user_groups"."user_id") AS "_prefetch_related_val_user_id", "auth_group"."id", "auth_group"."name" FROM "auth_group" INNER JOIN "auth_user_groups" ON ("auth_group"."id" = "auth_user_groups"."group_id") WHERE "auth_user_groups"."user_id" IN (1, 2, 3, 4, 5, 6); args=(1, 2, 3, 4, 5, 6)

administrators
users
users
users
users
users