Annotating a Django queryset with a left outer join?

Notice: This method does not work in Django 1.6+. As explained in tcarobruce's comment below, the promote argument was removed as part of ticket #19849: ORM Cleanup.

---

Django doesn't provide an entirely built-in way to do this, but it's not neccessary to construct an entirely raw query. (This method doesn't work for selecting * from UserFoo, so I'm using .comment as an example field to include from UserFoo.)

The QuerySet.extra() method allows us to add terms to the SELECT and WHERE clauses of our query. We use this to include the fields from UserFoo table in our results, and limit our UserFoo matches to the current user.

results = Foo.objects.extra(
    select={"user_comment": "UserFoo.comment"},
    where=["(UserFoo.user_id IS NULL OR UserFoo.user_id = %s)"],
    params=[request.user.id]
)

This query still needs the UserFoo table. It would be possible to use .extras(tables=...) to get an implicit INNER JOIN, but for an OUTER JOIN we need to modify the internal query object ourself.

connection = (
    UserFoo._meta.db_table, User._meta.db_table,  # JOIN these tables
    "user_id",              "id",                 # on these fields
)

results.query.join(  # modify the query
    connection,      # with this table connection
    promote=True,    # as LEFT OUTER JOIN
)

We can now evaluate the results. Each instance will have a .user_comment property containing the value from UserFoo, or None if it doesn't exist.

print results[0].user_comment

(Credit to this blog post by Colin Copeland for showing me how to do OUTER JOINs.)

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.

This answer might not be exactly what you are looking for but since its the first result in google when searching for "django annotate outer join" so I will post it here.

Note: tested on Djang 1.7

Suppose you have the following models

class User(models.Model):
    name = models.CharField()

class EarnedPoints(models.Model):
    points = models.PositiveIntegerField()
    user = models.ForeignKey(User)

To get total user points you might do something like that

 User.objects.annotate(points=Sum("earned_points__points"))

this will work but it will not return users who have no points, here we need outer join without any direct hacks or raw sql

You can achieve that by doing this

 users_with_points = User.objects.annotate(points=Sum("earned_points__points"))
 result = users_with_points | User.objects.exclude(pk__in=users_with_points)

This will be translated into OUTER LEFT JOIN and all users will be returned. users who has no points will have None value in their points attribute.

Hope that helps