python - 使用 annotate 连接 3 个表获取数据

Question

我有 3 个表:-

    class Organization(models.Model):
       id = models.AutoField(primary_key=True)
       name = models.CharField()
       .....
       #some more field
       .....



    class Billing(models.Model):
         id = models.AutoField(primary_key=True)
         organization = models.ForeignKey(Organization)
         amount = models.IntegerField()
         .....
         #some more field
         ....

.
   class Payment(models.Model):
       id = models.AutoField(primary_key=True)
       billing = models.ForeignKey(Billing)
       organization = models.ForeignKey(Organization)
       paymentDate = models.DateTimeField()
       .....
       #some more field
       ....

每当 organization 完成付款时，它每次都会为 payment 表创建一个新条目并更新 billing 金额。并且有多个计费对象可用计费表。因此，我正在使用注释计算为特定组织完成的金额和最新付款的总和。

我一直坚持使用注释查询为组织找到最新付款日期。

billing.objects.filter(
            orgId_id__in = organizationIdList,
            isCancel=0,
            billTime__range=(startDate, endDate)
        ).values('orgId_id').annotate(
            total_amount = Sum('amount'),
            lastest_payment_time = "HERE I AM STUCK",
        )

请帮帮我。

Answer 1

您可以使用 OuterRef 来尝试这样和 Subquery :

 latest_payment = Payment.objects.filter(organization=OuterRef('organization')).order_by("-paymentDate")
 Billing.objects.filter(organization__id__in=organizationIdList).values('organization__id').annotate(total_amount=SUM('amount'), latest_payment=Subquery(latest_payment.values('paymentDate')[:1]))

对于此解决方案，您至少需要 Django 1.11。