FilteredRelation
code:python
>> qs = User.objects.annotate(
... checkin=FilteredRelation(
... 'checkin',
... condition=Q(checkin__date='2021-10-01', checkin__location__isnull=False))
... ).values('name', 'checkin__location')
>>
>> print(sqlparse.format(str(qs.query), reindent=True))
SELECT "users"."name",
checkin."location"
FROM "users"
LEFT OUTER JOIN "checkins" checkin ON ("users"."id" = checkin."user_id"
AND ((checkin."date" = 2021-10-01
AND checkin."location" IS NOT NULL)))
対象が N:1 関連の場合は、INNER JOIN になる
対象が 1:N 関連の場合は、LEFT OUTER JOIN になり行が増える
もうちょっと複雑な例。
ページ1件に、その更新履歴がN件付いてるモデルで、ページそれぞれに、最初と最後の履歴レコードを持たせて、その2つの履歴の作成者情報も持たせる。
code:python
qs = Page.objects.all()
qs_histories = PageHistory.objects.filter(page=OuterRef("pk")).values("id")
qs = qs.annotate(
oldest_history=FilteredRelation(
"histories",
condition=Q(histories=Subquery(qs_histories.order_by("ctime"):1)), ),
latest_history=FilteredRelation(
"histories",
condition=Q(histories=Subquery(qs_histories.order_by("-ctime"):1)), ),
).select_related(
"oldest_history",
"latest_history",
"oldest_history__user",
"latest_history__user",
)
出力
code:sql
SELECT
page.id, page.ctime, page.utime, page.title, page.content,
oldest_history.id, oldest_history.ctime, oldest_history.utime, oldest_history.page_id, oldest_history.title, oldest_history.content, oldest_history.user_id,
user.id, user.password, user.is_superuser, user.name, user.email, user.is_active, user.is_staff, user.is_system,
T6.id, T6.ctime, T6.utime, T6.page_id, T6.title, T6.content, T6.user_id,
T7.id, T7.password, T7.is_superuser, T7.name, T7.email, T7.is_active, T7.is_staff, T7.is_system
FROM
page
LEFT OUTER JOIN page_history oldest_history ON (
page.id = oldest_history.page_id
AND (
oldest_history.id = (
SELECT U0.id
FROM page_history U0
WHERE U0.page_id = (page.id)
ORDER BY U0.ctime ASC
LIMIT 1
)
)
)
LEFT OUTER JOIN user ON (oldest_history.user_id = user.id)
LEFT OUTER JOIN page_history T6 ON (
page.id = T6.page_id
AND (
T6.id = (
SELECT U0.id
FROM page_history U0
WHERE U0.page_id = (page.id)
ORDER BY U0.ctime DESC
LIMIT 1
)
)
)
LEFT OUTER JOIN user T7 ON (T6.user_id = T7.id);