Django 1.8 conditional annotation results in INNER

Question

The models:

class Bar(GenericModel):
    ...

class Foo(GenericModel):
    bar = models.ForeignKey(Bar, related_name='foo_bar')

The query:

bars = Bar.objects
       .prefetch_related('foo_bar')
       .annotate(sum_foo=Sum(
                             Case(
                                  When(foo_bar__is_deleted=False, then='foo_bar__amount'),
                                  default=Value(0),
                                  output_field=IntegerField()
                                 )
                            )
                )

The former results in an inner join: SELECT ... FROM "bar" INNER JOIN "foo" ON ( "bar"."id" = "foo"."bar_id" ) ...

What I intend to obtain is a LEFT OUTER JOIN (a full list of "bar" objects annotated with "foo.amount" sums, or 0s if "foo" related to "bar" doesn't exist) instead of the INNER JOIN? Is it possible to do without falling back to raw SQL?

Answer 1

This way seems to work correctly:

bars = Bar.objects
       .prefetch_related('foo_bar')
       .annotate(sum_foo=Sum(
                             Case(
                                  When(Q(foo_bar__is_deleted=False) | Q(foo_bar__is_deleted=None),
                                                 then='foo_bar__amount'),
                                  default=Value(0),
                                  output_field=IntegerField()
                                 )
                            ),
                )

Answer 2

This is a known bug, corrected in Django 1.8.3 (release notes).

As you noted, the issue is that an INNER JOIN is being created, filtering out Bar objects when there's no corresponding relation to Foo objects.

Using a Django version higher than 1.8.3 will solve the issue.