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.

Solution 1

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

Solution 2

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.

Solution 3

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.

Comments

[Gareth Rees]

Remember that I don't actually know how your models are related, so you might have to adjust my proposed solution a bit to meet your needs.

[Phobophobia]

Your solution runs this query: SELECT ••• FROM "auth_user" WHERE "auth_user"."id" = <id> for every user that has a Foo fitting the criteria. As far as I can tell, this has to do with "{foo.user_id: foo"; replacing either part with something else causes the queries to not be run. The only relation between the two models is that Foo has a ForeignKey pointing to User.

[Gareth Rees]

When you make a ForeignKey field, that usually causes two properties to be added to instances. For example, if the model Foo has a field user = ForeignKey(auth_user) I would expect Foo instances to have two properties: user (which issues a select query to fetch the related object, as you say) and user_id (which does not). At least, that's what I observe when running my own tests. I'm afraid you'll have to do some of your own debugging here.

[Phobophobia]

I found the problem, I'd previously added user.username to Foo's __unicode__(self). Entirely my fault, your solution works perfectly. Thanks again!

[clime]

except with this solution you need to iterate through all the user which is not always desirable. E.g. when you paginate in template by using django-endless-pagination.

[Chris Withers]

Doing the join in python is never the right answer :-(

[Gareth Rees]

@ChrisWithers: If it's never the right answer, then what exactly do you recommend in this case? How would you solve the OP's problem?

[Chris Withers]

@GarethRees - honestly? Another ORM, it's pretty disappointing that Django's ORM doesn't natively support outer joins in any kind of sensible fashion. If you have to stick with Django, like I did, look at objects.raw() and write the query properly.

[Gareth Rees]

@ChrisWithers: I look forward to seeing your answer.

[Chris Withers]

@GarethRees - see below

[Kevin Bedell]

Thanks for adding this.