/shimizukawa/FilteredRelation

generated at 2/19/2025, 8:02:50 AM
FilteredRelation
Django ORMの FilteredRelation は、サブクエリをJOINしたい場合に使う。
https://docs.djangoproject.com/ja/3.2/ref/models/querysets/#filteredrelation-objects

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 になり行が増える

なお sqlparseはSQLの確認に便利。

もうちょっと複雑な例。
ページ1件に、その更新履歴がN件付いてるモデルで、ページそれぞれに、最初と最後の履歴レコードを持たせて、その2つの履歴の作成者情報も持たせる。
pythonqs = 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",
)
出力
sqlSELECT
    `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`);