django - Django 2.0 Queryset 注释日期

Question

有没有办法在数据库注释调用中将 F() 表达式传递给诸如 dateutil 的relativedelta之类的函数。

鉴于以下情况，InterestLoan.objects.active_loans(start_date='2018-01-01', end_date='2019-01-01')将返回给定期间内活跃贷款的查询集 a。end_date需要使用start_date + term进行注释。

相反，我得到 TypeError: bad operand type for unary +: 'F'

class InterestLoanSet(models.QuerySet):
    def add_end_date(self):
        return self.annotate(loan_end_date=ExpressionWrapper(F('start_date') + relativedelta(months=+F('term'), output_field=DateField())))

    def active_loans(self, start_date, end_date):
        return self.exclude(start_date__gt=end_date).add_end_date().exclude(loan_end_date__lt=start_date)


class InterestLoan(AbstractTransaction):
    objects = InterestLoanSet.as_manager()
    interest_nominal_code = models.ForeignKey(NominalCode, null=False, on_delete=models.PROTECT)
    balance_nominal_code = models.ForeignKey(NominalCode, null=False, on_delete=models.PROTECT, related_name='loans')
    principal = models.DecimalField(max_digits=10, decimal_places=2)
    term = models.PositiveIntegerField()
    interest_rate = models.DecimalField(max_digits=4, decimal_places=4)

标签： djangopython-3.xdjango-querysetdjango-annotate

Answer 1

这是根据 Daniel Roseman 的评论找到的解决方案，即需要在数据库中处理表达式。

class InterestLoanSet(models.QuerySet):
    def add_end_date(self):
        return self.annotate(loan_end_year=ExpressionWrapper(ExtractYear('start_date') + F('term'), output_field=IntegerField()))

    def active_loans(self, range_start_date, range_end_date):
        return self.exclude(start_date__gt=range_end_date).add_end_date().exclude(loan_end_year__lt=range_start_date.year, start_date__month__lt=start_date.month)

class InterestLoan(AbstractTransaction):
    objects = InterestLoanSet.as_manager()
    interest_nominal_code = models.ForeignKey(NominalCode, null=False, on_delete=models.PROTECT)
    balance_nominal_code = models.ForeignKey(NominalCode, null=False, on_delete=models.PROTECT, related_name='loans')
    principal = models.DecimalField(max_digits=10, decimal_places=2)
    term = models.PositiveIntegerField()
    interest_rate = models.DecimalField(max_digits=4, decimal_places=4)

此过滤器按年度过滤，但需要更复杂才能按月或按天过滤。

对 Kyle Hansan 的文章表示敬意