[SOLVED] How to add a calculated field to a django query expression

How to add a calculated field to a django query expression

I have a Django model, DocumentComments, with two datetime fields, created and updated. I am working on a search function that parses a search string and returns a Q expression to query the DocumentComments model based on the values in the search string.

I need to write something like Q(created.year=xxxx), where created.year is the year in the created datetime field. But "keywords can't be expressions" as Django has been telling me all morning.

I tried using a custom model manager and annotating the default queryset with a year field, but that did not work as I can't seem to access the created.year value in the get_queryset function.

class DocumentCommentManager(models.Manager):

   def get_queryset(self):
      c_year = self.created.year
      u_year = self.updated.year
      return super(DocumentCommentManager, self).get_queryset().annotate(created_year=c_year, updated_year=u_year)

What am I missing, or what is a better way to accomplish my goal?

Thanks!

Mark

Solution

I was able to solve my problem using Django's db function Extract (https://docs.djangoproject.com/en/3.1/ref/models/database-functions/#extract)

My DocumentCommentManager:

from django.db.models.functions import Extract

class DocumentCommentManager(models.Manager):
       def get_queryset(self):
          return super(DocumentCommentManager, self).get_queryset().annotate(created_year=Extract("created","year"))

This solves my original problem of adding a calculated datetime field to the model queries.

I still have not found a general way to add a calculated field to a model query using Q expressions. If you can share any examples, that would be great!