[Fixed]-Django left outer join with filter

by

0👍

✅

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

👤Gareth Rees

19👍

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.

👤Richard Zschech

8👍

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.

👤betaveros

0👍

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.

👤Chris Withers

0👍

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.

👤alper

Leave a comment