Annotating a Django queryset with a left outer join?

user_112855 0
Viewed 27513

Say I have a model:

class Foo(models.Model):
    ...

and another model that basically gives per-user information about Foo:

class UserFoo(models.Model):
    user = models.ForeignKey(User)
    foo = models.ForeignKey(Foo)
    ...

    class Meta:
        unique_together = ("user", "foo")

I'd like to generate a queryset of Foos but annotated with the (optional) related UserFoo based on user=request.user.

So it's effectively a LEFT OUTER JOIN on (foo.id = userfoo.foo_id AND userfoo.user_id = ...)

django
10 Answers

The only way I see to do this without using raw etc. is something like this:

Foo.objects.filter(
    Q(userfoo_set__isnull=True)|Q(userfoo_set__isnull=False)
).annotate(bar=Case(
    When(userfoo_set__user_id=request.user, then='userfoo_set__bar')
))

The double Q trick ensures that you get your left outer join.

Unfortunately you can't set your request.user condition in the filter() since it may filter out successful joins on UserFoo instances with the wrong user, hence filtering out rows of Foo that you wanted to keep (which is why you ideally want the condition in the ON join clause instead of in the WHERE clause).

Because you can't filter out the rows that have an unwanted user value, you have to select rows from UserFoo with a CASE.

Note also that one Foo may join to many UserFoo records, so you may want to consider some way to retrieve distinct Foos from the output.

user_60351050

maparent's comment put me on the right way:

from django.db.models.sql.datastructures import Join

for alias in qs.query.alias_map.values():
  if isinstance(alias, Join):
    alias.nullable = True

qs.query.promote_joins(qs.query.tables)
user_7476160
Related