[Fixed]-Django left outer join with filter


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():


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.)


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:

    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.


It’s clunky, but this should do it:

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

You can splat extra attributes on the returned User objects by adding them to the left side of the select.


Although there are good answers which may satisfy one’s needs the best practice would be

from django.db.models import Prefetch

User.objects.all().prefetch_related(Prefetch(<related_name>, queryset=Foo.objects.filter(<criteria>)))

<related_name> = ‘foo’ if:

class Foo(models.Model):
    user = models.ForeignKey(User, related_name='foo')

This returns all users and foo objects related to users with the given filter criteria.


Leave a comment