django - 如何过滤反向外键字段上的 Django 注释

Question

我正在尝试获取具有特定字段值的所有相关模型的计数。

这是一些代码...

models.py:

class Author(models.Model):
  name = models.CharField(max_length=100)

class Book(models.Model):
  BAD = "BAD"
  MEH = "MEH"
  GOOD = "GOOD"
  GREAT = "GREAT"
  REVIEW_CHOICES = (
    (BAD, BAD.title()),
    (MEH, MEH.title()),
    (GOOD, GOOD.title()),
    (GREAT, GREAT.title()),
  )
  title = models.CharField(max_length=100)
  review = models.CharField(max_length=100, choices=REVIEW_CHOICES)
  author = models.ForeignKey(Author, related_name="books")

假设我想列出每个作者每种类型评论的数量。

我已经尝试过:

Authors.object.annotate(n_good_books=Count("books")).filter(books__review="GOOD").values("name", "n_good_books")  

我也尝试过:

Authors.object.annotate(n_good_books=Count("books", filter=Q(books_review="GOOD"))).values("name", "n_good_books")  

但是这些都不起作用。

有什么建议吗？

Answer 1

您需要在.annotate(..)之前添加.filter(..)，因此:

Authors.object.filter(
    <b>books__review="GOOD"</b>  # before the annotate
).annotate(
    n_good_books=Count("books")
)

这将生成 Author 的 QuerySet，其中每个 Author 都有一个额外的属性 .n_good_books包含好书的数量。相反，您仅将检索至少一本相关图书获得好评的作者 。原样specified in the documentation :

When used with an annotate() clause, a filter has the effect of constraining the objects for which an annotation is calculated. For example, you can generate an annotated list of all books that have a title starting with "Django" using the query:

>>> from django.db.models import Count, Avg
>>> Book.objects.filter(name__startswith="Django").annotate(num_authors=Count('authors'))

(..)

Annotated values can also be filtered. The alias for the annotation can be used in filter() and exclude() clauses in the same way as any other model field.

For example, to generate a list of books that have more than one author, you can issue the query:

>>> Book.objects.annotate(num_authors=Count('authors')).filter(num_authors__gt=1)

This query generates an annotated result set, and then generates a filter based upon that annotation.

Count(..., filter=Q(..)) 方法仅在 django-2.0 之后有效，所以在 django-1.11这行不通。