Django left outer join with filter

Question

I'm using Django's built-in user model and have a custom Foo object with a ForeignKey to User. I'm looking to select all User objects and all of the Foo objects that fit certain constraints, like so:

SELECT * from auth_user LEFT OUTER JOIN "foo" ON
(auth_user.id = foo.id AND <other criteria here>)

How should I accomplish this in Django? So far I've tried:

User.objects.filter(foo__<criteria>)

but that generates SQL similar to this:

SELECT * from auth_user LEFT OUTER JOIN "foo" ON
(auth_user.id = foo.id) WHERE <other criteria here>

and only returns User objects that have Foo objects that fit the criteria. Alternately I can select all User objects and run a query for each one, but that would be substantially less efficient.

Answer 1

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.

Answer 2

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.