Calculate a date in queryset

Using Django Forms & APIs
1

Hello,

In the process of creating a Filter in an admin List, I’m trying to make a queryset that will return the last objects of a related object and subsequently filter the ones that a calculated date are greater than today.

Models:

class Animal(models.Model):
name = models.CharField(max_length=100)
uuid = models.UUIDField(default=uuid.uuid4, editable=False, unique=True)
…

class Subscription(models.Model):
uuid = models.UUIDField(default=uuid.uuid4, editable=False, unique=True)
start_date = models.DateField(default=timezone.now)
duration = models.IntegerField(default=12) # Duration in months
animal = models.ForeignKey(‘animals.Animal’, on_delete=models.PROTECT)
…

This queryset returns me the last subscription of each animal. It seems to work

qs = animals.filter(subscription__isnull=False).order_by(‘id’, ‘-subscription__start_date’).distinct(‘id’)

After I try to calculate a date in the future ( +1 year -1 day) based on the start_date field (DateField) in the subscription object

today = datetime.date.today()
qs = qs.annotate(end_date=Cast(F(‘subscription__start_date’) + relativedelta(months=12) - timedelta(days=1), output_field=DateField())).filter(end_date__gt=today)

When I ‘print’ qs I got this error:

Traceback (most recent call last):
File “/usr/local/lib/python3.8/site-packages/django/db/backends/utils.py”, line 84, in _execute
return self.cursor.execute(sql, params)
psycopg2.ProgrammingError: can’t adapt type ‘relativedelta’
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File “”, line 1, in
File “/usr/local/lib/python3.8/site-packages/django/db/models/query.py”, line 256, in repr
data = list(self[:REPR_OUTPUT_SIZE + 1])
File “/usr/local/lib/python3.8/site-packages/django/db/models/query.py”, line 262, in len
self._fetch_all()
File “/usr/local/lib/python3.8/site-packages/django/db/models/query.py”, line 1324, in _fetch_all
self._result_cache = list(self._iterable_class(self))
File “/usr/local/lib/python3.8/site-packages/django/db/models/query.py”, line 51, in iter
results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)
File “/usr/local/lib/python3.8/site-packages/django/db/models/sql/compiler.py”, line 1175, in execute_sql
cursor.execute(sql, params)
File “/usr/local/lib/python3.8/site-packages/django/db/backends/utils.py”, line 66, in execute
return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
File “/usr/local/lib/python3.8/site-packages/django/db/backends/utils.py”, line 75, in _execute_with_wrappers
return executor(sql, params, many, context)
File “/usr/local/lib/python3.8/site-packages/django/db/backends/utils.py”, line 84, in _execute
return self.cursor.execute(sql, params)
File “/usr/local/lib/python3.8/site-packages/django/db/utils.py”, line 90, in exit
raise dj_exc_value.with_traceback(traceback) from exc_value
File “/usr/local/lib/python3.8/site-packages/django/db/backends/utils.py”, line 84, in _execute
return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: can’t adapt type ‘relativedelta’

I guess that I do not correctly calculate the ‘end_date’ within the queryset.

Could someone help me with this.

Thanks in advance for your time.

2

Unfortunately, I don’t have an answer for your specific question.

But I would look at this a different way. Rather than needing to calculate a new date for each row while the query is being executed, I’d calculate it once outside the query.

Subtract 12 months and add 1 day to today, and compare it to start_date.

3

Thanks a lot for your time.

As the duration in the Subscription model can also change I still would be ‘obligated’ to calculate a date. I think I will end to calculate and store the end_date for each specific subscription. So if the start_date or the duration is changed, the end_date will be recalculated.

Do you think is more appropriate to do that with a post_save signal or within the save method ?

4

Definitely save method. (See various posts around here and other places as to why “signals” should generally be avoided. There are very few situations where they’re needed - otherwise they tend to cause more problems than they solve.)

If you’re now storing an end date, then there’s still no need to perform a calculation in the query. It’ll be just a straight comparison.

5

Yes indeed

Thanks again for your time