Django: filtering on an annotated datetime?

Question

I have a series of events (with timestamps) that are linked to users:

# models.py

from django.db import models

from django.contrib.auth.models import User

class Event(models.Model):
    name = models.CharField(max_length=255, blank=True, db_index=True)
    time = models.DateTimeField(db_index=True)
    user = models.ForeignKey(User, related_name='events', null=True)

I want to find the id of the first such event for each user (to be used in a later subquery). Should be simple, right?

activation_event_ids = User.objects  \
    .annotate(first_event_time=Min('events__time'))  \
    .filter(events__time='first_event_time')  \
    .values_list('events__id', flat=True)

But this blows up:

ValidationError                           Traceback (most recent call last)
/home/gabriel/.virtualenvs/gordon/local/lib/python2.7/site-packages/django/core/management/commands/shell.pyc in <module>()
      1 activation_event_ids = User.objects  \
      2     .annotate(first_event_time=Min('km_events__time'))  \
----> 3     .filter(km_events__time='first_event_time')  \
      4     .values_list(km_events__id, flat=True)

/home/gabriel/.virtualenvs/gordon/local/lib/python2.7/site-packages/django/db/models/query.pyc in filter(self, *args, **kwargs)
    619         set.
    620         """
--> 621         return self._filter_or_exclude(False, *args, **kwargs)
    622 
    623     def exclude(self, *args, **kwargs):

/home/gabriel/.virtualenvs/gordon/local/lib/python2.7/site-packages/django/db/models/query.pyc in _filter_or_exclude(self, negate, *args, **kwargs)
    637             clone.query.add_q(~Q(*args, **kwargs))
    638         else:
--> 639             clone.query.add_q(Q(*args, **kwargs))
    640         return clone
    641 

/home/gabriel/.virtualenvs/gordon/local/lib/python2.7/site-packages/django/db/models/sql/query.pyc in add_q(self, q_object, used_aliases, force_having)
   1251                 else:
   1252                     self.add_filter(child, connector, q_object.negated,
-> 1253                             can_reuse=used_aliases, force_having=force_having)
   1254                 if force_having:
   1255                     self.having.end_subtree()

/home/gabriel/.virtualenvs/gordon/local/lib/python2.7/site-packages/django/db/models/sql/query.pyc in add_filter(self, filter_expr, connector, negate, trim, can_reuse, process_extras, force_having)
   1186         else:
   1187             self.where.add((Constraint(alias, col, field), lookup_type, value),
-> 1188                 connector)
   1189 
   1190         if negate:

/home/gabriel/.virtualenvs/gordon/local/lib/python2.7/site-packages/django/db/models/sql/where.pyc in add(self, data, connector)
     67 
     68         if hasattr(obj, "prepare"):
---> 69             value = obj.prepare(lookup_type, value)
     70             super(WhereNode, self).add((obj, lookup_type, annotation, value),
     71                 connector)

/home/gabriel/.virtualenvs/gordon/local/lib/python2.7/site-packages/django/db/models/sql/where.pyc in prepare(self, lookup_type, value)
    316     def prepare(self, lookup_type, value):
    317         if self.field:
--> 318             return self.field.get_prep_lookup(lookup_type, value)
    319         return value
    320 

/home/gabriel/.virtualenvs/gordon/local/lib/python2.7/site-packages/django/db/models/fields/__init__.pyc in get_prep_lookup(self, lookup_type, value)
    707         if lookup_type in ('month', 'day', 'week_day'):
    708             return int(value)
--> 709         return super(DateField, self).get_prep_lookup(lookup_type, value)
    710 
    711     def get_prep_value(self, value):

/home/gabriel/.virtualenvs/gordon/local/lib/python2.7/site-packages/django/db/models/fields/__init__.pyc in get_prep_lookup(self, lookup_type, value)
    308             return value
    309         elif lookup_type in ('exact', 'gt', 'gte', 'lt', 'lte'):
--> 310             return self.get_prep_value(value)
    311         elif lookup_type in ('range', 'in'):
    312             return [self.get_prep_value(v) for v in value]

/home/gabriel/.virtualenvs/gordon/local/lib/python2.7/site-packages/django/db/models/fields/__init__.pyc in get_prep_value(self, value)
    799 
    800     def get_prep_value(self, value):
--> 801         value = self.to_python(value)
    802         if value is not None and settings.USE_TZ and timezone.is_naive(value):
    803             # For backwards compatibility, interpret naive datetimes in local


/home/gabriel/.virtualenvs/gordon/local/lib/python2.7/site-packages/django/db/models/fields/__init__.pyc in to_python(self, value)
    783 
    784         msg = self.error_messages['invalid'] % value
--> 785         raise exceptions.ValidationError(msg)
    786 
    787     def pre_save(self, model_instance, add):

ValidationError: [u"'first_event_time' value has an invalid format. It must be in YYYY-MM-DD HH:MM[:ss[.uuuuuu]][TZ] format."]

I'm currently using a local SQLite DB. Is it really not possible to filter on annotated datetimes? Or am I just doing something stupid?

Thanks!

Answer 1

If you want to reference the annotation field, you need to use F:

from django.db.models import F

...

.filter(events__time=F('first_event_time'))

For reference, see: https://docs.djangoproject.com/en/dev/topics/db/queries/#query-expressions