Django QuertySet.annotate() received non-expression - how to add a derived field based on model field?

Question

First time with Django. Trying to add an annotation to queryset:

class EnrollmentManager(models.Manager.from_queryset(EnrollmentCustomQuerySet)):
  COURSE_DURATION = datetime.timedelta(days=183)

  def get_queryset(self):
      """Overrides the models.Manager method"""
      lookback = make_aware(datetime.datetime.today() - self.COURSE_DURATION)
      qs = super(EnrollmentManager, self).get_queryset().annotate( \
              is_expired=(Value(True)), output_field=models.BooleanField())
      return qs

At the moment I am just trying to add an extra 'calculated' field on the returned queryset, which is hard-coded to True and the attribute/field should be called is_expired.

If I can get that to work, then Value(True) needs to be a derived value based on this expression:

F('enrolled') < lookback

But since 'enrolled' is a database field and lookback is calculated, how will I be able to do that?

Note

I tried this, which executes without throwing the error:

qs = super(EnrollmentManager, self).get_queryset().annotate( \
         is_expired=(Value(True, output_field=models.BooleanField())))

and in the shell I can see it:

Enrollment.objects.all()[0].is_expired -> returns True

and I can add it to the serializer:

class EnrollmentSerializer(serializers.ModelSerializer):
  is_active = serializers.SerializerMethodField()
  is_current = serializers.SerializerMethodField()
  is_expired = serializers.SerializerMethodField()
  COURSE_DURATION = datetime.timedelta(days=183)

  class Meta:
    model = Enrollment
    fields = ('id', 'is_active', 'is_current', 'is_expired')

  def get_is_expired(self, obj):
    return obj.is_expired

So it is possible...but how can I replace my hard-coded 'True" with a calculation?

UPDATE

Reading the documentation, it states:

"Annotates each object in the QuerySet with the provided list of query expressions. An expression may be a simple value, a reference to a field on the model (or any related models), or an aggregate expression (averages, sums, etc.) that has been computed over the objects that are related to the objects in the QuerySet."

A simple value - so, not a simple COMPUTED value then?

That makes me think this is not possible...

score 1 · Accepted Answer · answered Apr 17 '20 at 16:02

It seems like a pretty good use-case for a Case expression. I suggest getting as familiar as you can with these expression tools, they're very helpful!

I haven't tested this, but it should work. I'm assuming enrolled is a tz-aware datetime for when they first enrolled...

from django.db.models import Case, When, Value

def get_queryset(self):
    """Overrides the models.Manager method"""
    lookback = make_aware(datetime.datetime.today() - self.COURSE_DURATION)
    qs = super(EnrollmentManager, self).get_queryset().annotate(
        is_expired=Case(
            When(
                enrolled__lt=lookback,
                then=Value(True)
            ),
            default=Value(False),
            output_field=models.BooleanField()
        )
    )

You also don't have to pre-calculate the lookback variable. Check out ExpressionWrappers and this StackOverflow answer that addresses this.

ExpressionWrapper(
    TruncDate(F('date1')) + datetime.timedelta(days=365),
    output_field=DateField(),
)

Django QuertySet.annotate() received non-expression - how to add a derived field based on model field?

1 Answers1