Django 1.8条件注释导致INNER JOIN而不是LEFT OUTER JOIN [英] Django 1.8 conditional annotation results in INNER JOIN instead of LEFT OUTER JOIN

问题描述

模型：

class Bar(GenericModel):
    ...

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

查询：

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

内部连接： SELECT ... FROMbarINNER JOINfooON（bar。id=foo。bar_id）...

我打算获得的是一个LEFT OUTER JOIN（用foo.amountsu注释的bar对象的完整列表ms，如果与bar相关的foo不存在）而不是INNER JOIN？是否可以不退缩到原始SQL？

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?

推荐答案

这种方式似乎正常工作：

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()
                                 )
                            ),
                )

这篇关于Django 1.8条件注释导致INNER JOIN而不是LEFT OUTER JOIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！