Django left outer join with filter

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.

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.

If you wanted Django to fetch all the User objects and all the Foo objects that are related to a user object, then you'd use select_related():

User.objects.all().select_related('foo')

but here you don't want all the Foo objects that are related to a user object, you just want the subset of them that satisfy your criteria. I don't know of a way to tell Django to do that in a single query set. But what you can do is to do both selects separately and do the join in Python:

# Map from user id to corresponding Foo satisfying <criteria>, if any.foos = {foo.user_id: foo for foo in         Foo.objects.filter(user__isnull = False, <criteria>)}for user in User.objects.all():    foo = foos.get(user.id)    # ...

(This doesn't do any more database work or transfer any more data than your LEFT OUTER JOIN would, so I think it's a reasonable approach.)