逆引きDjangoORM: LEFT OUTER JOIN

要件

SQL & ORM 検討

アプローチ

NULL OR NOT NULL フィルタ

FilteredRelationによるLEFT OUTER JOIN

1. NULL OR NOT NULL フィルタ

code:orm.py

queryset = Person.objects.filter(

Q(grade__isnull=True)|Q(grade__isnull=False)

)

code:sql

SELECT "person"."id", "grade"."seiseki"

FROM "person"

LEFT OUTER JOIN "grade" ON ("person"."id" = "grade"."person_id")

WHERE (

"grade"."id" IS NULL

OR "grade"."id" IS NOT NULL

);

2. FilteredRelationによるLEFT OUTER JOIN

code:orm.py

queryset = Person.objects.annotate(

grade=FilteredRelation('grade') # ここでgrade.yearの絞り込みもできる

).select_related('grade')

code:sql

SELECT "person"."id",

...

grade."id",

grade."year",

grade."person_id",

grade."seiseki"

FROM "person"

LEFT OUTER JOIN "grade" grade ON ("person"."id" = grade."person_id");

タグ