Django:如何有条件地过滤外键

Question

假设我有一些类(class)如下:

from django.db import models

REVIEW_RATING_CHOICES = (
    (5, 'Five'),
    (4, 'Four'),
    (3, 'Three'),
    (2, 'Two'),
    (1, 'One'),
)

class Publication(models.Model):
    name = models.CharField()

class Article(models.Model):
    publication = models.ForeignKey(Publication)
    content = models.TextField()

class Review(models.Model):
    date = models.DateTimeField()
    article = models.ForeignKey(Article)
    rating = models.IntegerField(choices=REVIEW_RATING_CHOICES)

假设我们想要突出显示“非常好的”出版物，我们将其定义为任何文章立即获得五星级评论的出版物。

如何获得文章的任何第一(即Review.date的最早值)评论被给予5星的出版物计数？

Answer 1

我相信你可以嵌套子查询，但我担心它的性能。

from django.db.models import OuterRef, Subquery, Exists

earliest_review = Review.objects.filter(article_id=OuterRef('id')).order_by('date')
initially_approved_articles = Article.objects.annotate(
    earliest_review_status=Subquery(earliest_review.values('status')[:1]),
).filter(
    earliest_review_status=APPROVED,
    publication_id=OuterRef('id'),
)
Publication.objects.annotate(
    has_initially_approved_article=Exists(initially_approved_articles),
).filter(
    has_initially_approved_article=True,
)