获取相关项目的计数，并将其从django中的属性中减去

Question

我有三个模型，我正在努力简化我的查询。

我试图返回尚未完全注册/付费的Course对象的列表。所以我需要得到Payment对象的计数，然后看看这是否等于Course对象上的总available_seats。

我有一个Payment、Course和Registration型号：

class Payment(models.Model):
payment = models.ForeignKey(Payment, on_delete=models.PROTECT)
registration = models.ForeignKey(Registration, on_delete=models.PROTECT)
is_refunded = models.BooleanField(default=False)
class Course(models.Model):
course = models.ForeignKey(Course, on_delete=models.PROTECT)
location = models.ForeignKey(Location, on_delete=models.PROTECT)
seat_count = models.IntegerField()
class Registration(models.Model):
person = models.ForeignKey(Person, on_delete=models.PROTECT)
course = models.ForeignKey(Course, on_delete=models.PROTECT)
comments = models.CharField(max_length=200, blank=True, null=True)

我只想在做了Payment的情况下考虑一个座位被"占用"——所以我试图得到的是给定Course的Payment计数。

我在尝试这样的东西：

payments = (
Payment.objects.filter(
registration__course__id=OuterRef("pk"), is_refunded=False
).values("pk")
# .annotate(
#     total_seats_available=F("registration__course__seat_count")
#     - Count("registration")
# )
# .values("total_seats_available")
)
courses = (
Course.objects.filter(id__in=course_ids)
.prefetch_related(
Prefetch(
"registration_set",
queryset=Registration.objects.prefetch_related(
"payment_set"
),
)
)
.annotate(
paid_seats=Case(
When(
Exists(payments),
then=Count(payments),
),
default=None,
),
has_available_seats=Case(
# No Payment have been recorded
When(paid_seats=None, then=Value(True)),
# Payment exist and total_seats_available should calc
When(paid_seats__gt=0, then=Value(True)),
# Default to False
default=Value(False),
output_field=BooleanField(),
),
)
.filter(has_available_seats=True)
)

目前，它返回一个Payment对象的计数，如果它大于0，则认为该课程有可用的座位。

执行逻辑以使Course注释为seat_count - payments的值的最佳方式是什么，以准确了解实际可用的"多少"座位？

这种逻辑应该存在于哪里？我离开的时候有些逻辑，我已经评论过了，但不起作用。我似乎不太明白。

Answer 1

我能够使用F()表达式来实现这一点：

payments = (
Payment.objects.filter(
registration__course__id=OuterRef("pk"), is_refunded=False
).values("pk")
)
courses = (
Course.objects.filter(id__in=course_ids)
.prefetch_related(
Prefetch(
"registration_set",
queryset=Registration.objects.prefetch_related(
"payment_set"
),
)
)
.annotate(
paid_seats=Case(
When(
Exists(payments),
then=Count(payments),
),
default=None,
),
available_seats=Case(
When(Exists(payments), then=F('seat_count')-F('paid_seats'),
default=F('seat_count'),
output_field=IntegerField(),
),
has_available_seats=Case(
When(available_seats__gte=0, then=Value(True)),
default=Value(False),
output_field=BooleanField(),
),
)
.filter(has_available_seats=True)
)