如何强制Django在查询中使用左外连接？

Question

我有两个模型：一个是“人”，另一个是“任务”。

class Person(models.Model):
    display_name = models.CharField()
    ...

class Task(models.Model):
    person = models.ForeignKey(Person)
    is_deleted = models.BooleanField()
    ...

我想要获取所有人的列表，并且显示每个人的任务数量（包括没有任务的人）。

最开始，我写了下面的查询，它运行得很好：

Person.objects.values('person_id', 'display_name').annotate(crt_task_amt=Count('task__id')).order_by('-crt_task_amt', 'display_name')

后来，我加了一个过滤条件，检查是否被删除。结果是没有任务的人就消失了：

Person.objects.filter(task__is_deleted=False).values('person_id', 'display_name').annotate(crt_task_amt=Count('task__id')).order_by('-crt_task_amt', 'display_name')

我想要的效果是这样的：

SELECT p.id, p.display_name, count(t.id) FROM dashboard_person p LEFT OUTER JOIN dashboard_task t ON (p.person_id=t.person_id AND t.is_deleted=0) GROUP BY t.person_id

有没有办法在不使用原始SQL的情况下实现这个？

Answer 1

q = Task.objects.filter(is_deleted=False).values('person__id').annotate(crt_task_amt=Count('id')).order_by('-crt_task_amt', 'person__display_name')

q[0].person_id  # gives person_id
q[0].display_name #gives person name
q[0].crt_task_amt # gives count of task of first person

更新：

希望这个能奏效。

Task.objects.filter(is_deleted=False, person__isnull = True).values('person__id').annotate(crt_task_amt=Count('id')).order_by('-crt_task_amt', 'person__display_name')

这可以通过连接（joins）轻松实现，但你需要用一点原始的SQL语句来做到这一点。

Answer 2

有时候，Django的ORM会选择使用INNER JOIN，有时候则是LEFT OUTER JOIN。我还没找到它背后的逻辑。不过，我测试了一些案例，得到了些许启发。

首先的案例（我使用的是Django 1.8.1）：

class Parent(...)
    ...

class Child(...):
    parent = ForeignKey(Parent)
    status = CharField()
    name   = CharField()
    ...

qs = Parent.object.all()

任务1：统计每个父记录包含多少个子记录

这个应该可以正常工作：

qs = qs.annotate(child_count_all=Count("child"))

查看一下qs.query，你会发现使用的是LEFT OUTER JOIN，这是正确的。

但是如果我用SUM加CASE-WHEN来做：

qs = qs.annotate(
        child_count=Sum(Case(default=1), output_field=IntegerField())
        )

查看qs.query，这次你会看到使用的是INNER JOIN，这会过滤掉所有没有子记录的父记录，导致结果错误。

解决这个问题的方法是这样的：

qs = qs.annotate(
        child_count=Sum(
            Case(
                When(child__id=None, then=0),
                default=1,
                output_field=IntegerField())
            ))

这次qs.query显示使用了LEFT OUTER JOIN，得到了正确的结果。

任务2：统计有多少个活跃的子记录

活跃的子记录是通过状态<>'INA'来判断的。基于之前的解决方案，我尝试了以下方法：

qs = qs.annotate(
        child_count=Sum(
            Case(
                When(child__id=None, then=0),
                When(child__status='INA', then=0),
                default=1,
                output_field=IntegerField())
            ))

但再次查看qs.query，发现使用的是INNER JOIN，因此得到了错误的结果（对我来说）。

解决方案是使用两个或的Q对象：

qs = qs.annotate(
        child_count=Sum(
            Case(
                When(Q(child__id=None) | Q(child__status="INA"), then=0),
                default=1,
                output_field=IntegerField())
            ))

再次查看qs.query，这次使用了LEFT OUTER JOIN，得到了正确的结果。

任务3：和任务2一样，但只统计那些名字已填写的记录

这个可以正常工作：

qs = qs.annotate(
        child_with_name_count=Sum(
            Case(
                When(Q(child__id=None) | Q(child__status="INA"), then=0),
                When(child__name__isnull=False, then=1),
                default=0,
                output_field=IntegerField())
            ))

结论

我不能确定为什么有时候使用INNER JOIN，有时候使用LEFT JOIN，所以我处理这个问题的方法是通过检查qs.query来测试各种组合，直到找到合适的结果。另一种方法是使用qs.raw/join/extra以及其他更原生和高级的Django ORM/SQL组合。