Django left outer join with filter
Django 2.0 introduced FilteredRelation
objects, which can produce pretty much exactly the LEFT OUTER JOIN
query you mentioned with code similar to:
User.objects.annotate(
filtered_foo=FilteredRelation('foo', condition=Q(foo_<criteria>))
).values(...) # e.g. 'user__id', 'filtered_foo__id'
However, it looks like you need to explicitly ask for the fields of filtered_foo
that you want to use, either by specifying in values
or with additional annotations. Alternatively, you can also aggregate over fields of filtered_foo
grouped by the User.
To get a LEFT OUTER JOIN
you can go:
User.objects.select_related('foo').filter(Q(foo__isnull=True) | Q(<other criteria here>))
Django uses the foo__isnull=True
to direct it to generate a LEFT OUTER JOIN
. Giving foo__isnull=False
to generates an INNER JOIN
as it would without the filter parameter.