Django 相当于 sum+case

Question

我有一堆 django 模型

class ReviewItem(Model):
  review = models.ForegnKey("Review")
  person = models.ForeignKey("Person")
  category = models.ForeignKey("Category")
  item = models.ForeignKey("item")
  reviewed = models.DateTimeField(null=True)

class Person(Model):
  name = models.CharField(max_length=255)

class Category(Model):
  name = models.CharField(max_length=127)

class Item(Model):
  name = models.CharField(max_length=127)
  category = models.ForeignKey("Category")

(如您所见，ReviewItem 中的“Category”fk 是多余的)

最多有 NxM 条 ReviewItem 记录，其中 N 是人数，M 是他们可能分配给他们的项目数，并且他们将在经过审查后设置“审查”日期。项目按类别分组。

我想要的是计算每个项目有多少项目已经过审核，有多少没有。在 SQL 中，我可以做

select category.name, item.name,
sum(case when reviewed is null then 1 else 0 end) as un_reviewed
sum(case when reviewed is null then 0 else 1 end) as reviewed
from reviewitem
join  category on category.id = reviewitem.category_id
join  item on item.id = reviewitem.item_id
group by category.id, item.id
order by category.name, item.name

如果不在 django 中做两个单独的 QuerySets，我无法弄清楚如何做到这一点。

用两个 QuerySet 来做，我最终得到:

uncompleted_items = Item.objects.filter(
    reviewitem__review=current_review,
    reviewitem__person__reports_to=eff_user,
    reviewitem__reviewed__isnull=True
).select_related(
    'category',
).annotate(num_uncompleted=Count('reviewitem'))

completed_items = Item.objects.filter(
    reviewitem__review=current_review,
    reviewitem__person__reports_to=eff_user,
    reviewitem__reviewed__isnull=False
).select_related(
    'category',
).annotate(num_completed=Count('reviewitem'))

Answer 1

在 Django 1.8 中，这可以使用 Conditional Expressions 来完成。 .

因此，您的示例查询可能如下所示:

from django.db.models import When, Case, Sum, IntegerField

items = Item.objects.annotate(
    un_reviewed=Sum(Case(When(reviewed__isnull=True, then=1)
                         When(reviewed__isnull=False, then=0),
                         output_field=IntegerField())),
    reviewed=Sum(Case(When(reviewed__isnull=True, then=0)
                      When(reviewed__isnull=False, then=1),
                      output_field=IntegerField())))