How to create notification date field in DjangoORM using annotate

Question

I would like to create a new field named notification_date using annotate in DjangoORM.

Here is my model:

SpeciesType(models.Model):
   # ... some species type setting fields.
   heat_lapse = IntegerField()
   estrous = IntegerField()


Breeding(models.Model):
   # .. some breeding fields

   species_type = ForeignKey(SpeciesType, related_name="breedings", on_delete=CASCADE)


   created_at = DateTimeField(auto_add_now=True)

Now the formula of date notification of breeding is

Breeding.created_at + (SpeciesType.heat_lapse * SpeciesType.estrous) in days 

e.g. 1/29/2017 11:21PM + (3 * 21) in days = 4/2/2017 as notification date

So in order to achieved this i created this query filter with timedelta, F() object, and ExpressionWrapper:

from django.db.models import F, ExpressionWrapper, DateField
from datetime import date, timedelta

Breeding.objects.annotate(
     notification_date = ExpressionWrapper(
        F('created_at') + 
        timedelta(days=(
            F('species_type__heat_lapse') * F('species_type__estrous')
        ))
     , output_field=DateField())
).filter(
    notification_date__lte == date.today()
)

but this wont work because you cannot do an F() inside a timedelta. Anyone knows how to formulate this desired query? it would be a great help for me.

Answer 1

Maybe consider using a cached_property on your model. This will be much easier and doesn't involve any additional queries if you take care, all the used values are properly prefetched. Also you can use it like you would use a normal property, that means accessing it with my_breading_instance.notification_date

from datetime import date, timedelta

from django.db import models
from django.utils.functional import cached_property


Breeding(models.Model):
    # .. your breeding fields

    @cached_propery
    def notification_date(self):
        delta = timedelta(days=(self.species_type.heat_leapse * self.species_type.estrous))
        return self.created_at + delta

Also the value will be cached after the first access.

UPDATE:

If you really need this to be annotated, because you want to further filter your queryset on the notification_date you'll have to write your own aggregation function.

As you already noticed, you can't use timedelta inside annotations because the value to be annotated has to be calculated entirely inside the database. Therefore you can only use database functions for calculating it.

Django provides some common functions like SUM, COALESCE or simmilar that produce valid sql inside your query.

The one you need however is not implemented in django. But you can write your own. The one you need for mysql is called DATEADD. The function has to create sql that looks e.g. like this:

SELECT OrderId,DATE_ADD(OrderDate,INTERVAL 30 DAY) AS OrderPayDate FROM Orders

It should look like this:

class DateAdd(models.Func):
    """
    Custom Func expression to add date and int fields as day addition
    """
    function = 'DATE_ADD'
    arg_joiner = ", INTERVAL "
    template = "%(function)s(%(expressions)s DAY)"
    output_field = models.DateTimeField()

This creates sql that looks about like this:

DATE_ADD("created_at", INTERVAL ("heat_lapse" * "estrous") DAY) AS "notifiaction_date"

It's a dirty trick to use the arg_joiner to concat the two parameters of the DateAdd function so that it creates the necessary sql.

You can use it like this:

qs = Breeding.objects.annotate(
    notifiaction_date=DateAdd('created_at', (models.F('species_type__heat_lapse') * models.F('species_type__estrous')))
)

I took some of it from this answer, but this is a function that works for postgres only. I tested this and it works on a postgres database. I didn't test my code for mysql so maybe you have to adapt it a little bit. But this is basicly how to do it.

If you want to know more about how to write own Expression, look here or dig into the django source and have a look at already implemented expressions like CAST.