Annotating a Django queryset with a left outer join?

Question

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

Answer 1

A solution with raw might look like

foos = Foo.objects.raw("SELECT foo.* FROM foo LEFT OUTER JOIN userfoo ON (foo.id = userfoo.foo_id AND foo.user_id = %s)", [request.user.id]) 

You'll need to modify the SELECT to include extra fields from userfoo which will be annotated to the resulting Foo instances in the queryset.