Django 2.0 Queryset annotate date

Question

Is there a way to pass an F() expression to a function such as dateutil's relativedelta inside a database annotate call.

Given the below scenario where it is intended that InterestLoan.objects.active_loans(start_date='2018-01-01', end_date='2019-01-01') will return a queryset a of active loans within the given period. The end_date needs to be annotated using the start_date + term.

Instead I get 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)

It compalins about the `+` in `relativedelta(month=+F('term'), ...)`. — Willem Van Onsem, Jul 23 '18 at 11:56
Removing the '+' produces a different error:TypeError: int() argument must be a string, a bytes-like object or a number, not 'F' @WillemVanOnsem — Todd Langford-Archer, Jul 23 '18 at 11:59
This isn't possible because the whole point of an expression is that it is done in the database. You will need to find an equivalent that can be converted to SQL. — Daniel Roseman, Jul 23 '18 at 12:20

score 0 · Answer 1 · answered Jul 23 '18 at 14:10

Here is a solution found following Daniel Roseman's comments that the expression needs to be taken care of in the database.

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)

This filters at an annual level but will need to be made more sophisticated to filter at a month or day level.

Kudos to Kyle Hansan's article

Django 2.0 Queryset annotate date

1 Answers1