0

I have a model like this:

class RentDue(models.Model):
    balance = DecimalField()
    day_due = IntegerField() # e.g. 1-5, representing the 1st through 5th of the month

I want to be able to annotate a queryset using the integer from day_due to create a new attribute rent_due_date so that I can filter on the next month's due date like this:

from datetime import date, timedelta

# create RentDue instances -- one due on the 1st another on the 3rd
rent_due_first = RentDue.objects.create(day_due=1)
rent_due_third = RentDue.objects.create(day_due=3)

# query for all RentDue on the 1st or earlier
RentDue.objects.annotate(
    rent_due_date=date(2022, 12, F('day_due'))
).filter(
    rent_due_date__lte=date(2022, 12, 1)
)
# -> returns [rent_due_first]


# query for all RentDue on the 3rd or earlier
RentDue.objects.annotate(
    rent_due_date=date(2022, 12, F('day_due'))
).filter(
    rent_due_date__lte=date(2022, 12, 3)
)
# -> returns [rent_due_first, rent_due_third]

However, passing in the F expression to date raises TypeError: an integer is required (got type F)

I've tried using ExpressionWrapper as laid out in this answer as well as TruncDate but I'm getting some weird errors I don't quite get.

A possible solution would be to annotate the entire queryset with DateField of next_month (hard-coded to date(2022, 12, 1) and then being able to use that in an F expression/ExpressionWrapper but that seems like a lot of work as well.

Just need some ideas to get my mind working.

Sunderam Dubey
  • 1
  • 11
  • 20
  • 40
Sean D.
  • 1,062
  • 9
  • 12
  • It seems like the same logic as `filter(day_due__lte=3)`, no? To me, it seems like you need to store the full date of when the rent is due to get the behavior that you're looking for. – vinkomlacic Nov 28 '22 at 22:54
  • @vinkomlacic yeah the difficulty is that it's a moving target every month. With `day_due=3`, this month the `rent_due_date` is December 3, 2022. Next month it's January 3, 2022. `RentDue` in this case is a configuration -- we don't have a new one each month. I could set a property that returns this month's due_date but we can't query on a property – Sean D. Nov 28 '22 at 23:14
  • 1
    But how can you differentiate between a rent that is this month and the one that isn't? – vinkomlacic Nov 29 '22 at 14:51
  • I may have named this sample model badly for purposes of SO. We're cross-referencing this with payment data in a separate model. This query will be run a few times a day, so we'll be feeding in today's date to populate `rent_due_date` (e.g. when it's run on Nov 30 `rent_due_date` will be Dec 3 and when it's run on Dec 20 `rent_due_date` will be Jan 3) – Sean D. Nov 30 '22 at 18:10

0 Answers0