Filter reference field on model to a date difference raise a TypeError

[Mounir]

I want to filter all products that the start_date and signed_date are lesser than or equal than a specific value.

Before 1.7, I was doing the following:

Product.objects.filter(start_date__isnull=False).extra(
            where=['"appname_product"."start_date" - "appname_product"."signed_date" <= %s' % days],
        ).count()

Now, I tried to do the following using F expressions:

Product.objects.filter(start_date__isnull=False).annotate(days=F('start_date')-F('signed_date')).filter(days__lte=days)

The problem, this will raise a TypeError because I think it will try to parse the value to Date:

/usr/local/lib/python2.7/dist-packages/django/db/models/query.pyc in filter(self, *args, **kwargs)
    677         set.
    678         """
--> 679         return self._filter_or_exclude(False, *args, **kwargs)
    680
    681     def exclude(self, *args, **kwargs):

/usr/local/lib/python2.7/dist-packages/django/db/models/query.pyc in _filter_or_exclude(self, negate, *args, **kwargs)
    695             clone.query.add_q(~Q(*args, **kwargs))
    696         else:
--> 697             clone.query.add_q(Q(*args, **kwargs))
    698         return clone
    699

/usr/local/lib/python2.7/dist-packages/django/db/models/sql/query.pyc in add_q(self, q_object)
   1307         existing_inner = set(
   1308             (a for a in self.alias_map if self.alias_map[a].join_type == INNER))
-> 1309         clause, require_inner = self._add_q(where_part, self.used_aliases)
   1310         self.where.add(clause, AND)
   1311         for hp in having_parts:

/usr/local/lib/python2.7/dist-packages/django/db/models/sql/query.pyc in _add_q(self, q_object, used_aliases, branch_negated, current_negated, allow_joins, split_subq)
   1335                     child, can_reuse=used_aliases, branch_negated=branch_negated,
   1336                     current_negated=current_negated, connector=connector,
-> 1337                     allow_joins=allow_joins, split_subq=split_subq,
   1338                 )
   1339                 joinpromoter.add_votes(needed_inner)

/usr/local/lib/python2.7/dist-packages/django/db/models/sql/query.pyc in build_filter(self, filter_expr, branch_negated, current_negated, can_reuse, connector, allow_joins, split_subq)
   1157         clause = self.where_class()
   1158         if reffed_expression:
-> 1159             condition = self.build_lookup(lookups, reffed_expression, value)
   1160             if not condition:
   1161                 # Backwards compat for custom lookups

/usr/local/lib/python2.7/dist-packages/django/db/models/sql/query.pyc in build_lookup(self, lookups, lhs, rhs)
   1099                     lhs = self.try_transform(lhs, name, lookups)
   1100                     final_lookup = lhs.get_lookup('exact')
-> 1101                 return final_lookup(lhs, rhs)
   1102             lhs = self.try_transform(lhs, name, lookups)
   1103             lookups = lookups[1:]

/usr/local/lib/python2.7/dist-packages/django/db/models/lookups.pyc in __init__(self, lhs, rhs)
     99     def __init__(self, lhs, rhs):
    100         self.lhs, self.rhs = lhs, rhs
--> 101         self.rhs = self.get_prep_lookup()
    102         if hasattr(self.lhs, 'get_bilateral_transforms'):
    103             bilateral_transforms = self.lhs.get_bilateral_transforms()

/usr/local/lib/python2.7/dist-packages/django/db/models/lookups.pyc in get_prep_lookup(self)
    137
    138     def get_prep_lookup(self):
--> 139         return self.lhs.output_field.get_prep_lookup(self.lookup_name, self.rhs)
    140
    141     def get_db_prep_lookup(self, value, connection):

/usr/local/lib/python2.7/dist-packages/django/db/models/fields/__init__.pyc in get_prep_lookup(self, lookup_type, value)
   1311         if lookup_type in ('month', 'day', 'week_day', 'hour', 'minute', 'second'):
   1312             return int(value)
-> 1313         return super(DateField, self).get_prep_lookup(lookup_type, value)
   1314
   1315     def get_prep_value(self, value):

/usr/local/lib/python2.7/dist-packages/django/db/models/fields/__init__.pyc in get_prep_lookup(self, lookup_type, value)
    725             return value
    726         elif lookup_type in ('exact', 'gt', 'gte', 'lt', 'lte'):
--> 727             return self.get_prep_value(value)
    728         elif lookup_type in ('range', 'in'):
    729             return [self.get_prep_value(v) for v in value]

/usr/local/lib/python2.7/dist-packages/django/db/models/fields/__init__.pyc in get_prep_value(self, value)
   1315     def get_prep_value(self, value):
   1316         value = super(DateField, self).get_prep_value(value)
-> 1317         return self.to_python(value)
   1318
   1319     def get_db_prep_value(self, value, connection, prepared=False):

/usr/local/lib/python2.7/dist-packages/django/db/models/fields/__init__.pyc in to_python(self, value)
   1272
   1273         try:
-> 1274             parsed = parse_date(value)
   1275             if parsed is not None:
   1276                 return parsed

/usr/local/lib/python2.7/dist-packages/django/utils/dateparse.pyc in parse_date(value)
     58     Returns None if the input isn't well formatted.
     59     """
---> 60     match = date_re.match(value)
     61     if match:
     62         kw = {k: int(v) for k, v in six.iteritems(match.groupdict())}

TypeError: expected string or buffer

[Josh Smeaton]

This ticket seems really familiar, but I can't seem to find a duplicate at the moment. I'll keep hunting for it. In the mean time, there's a workaround for what you want to do:

Product.objects.annotate(days=ExpressionWrapper(F('start_date')-F('signed_date'), output_field=DurationField())).filter(days__lte=2)

That works on Postgres at least, I'm not sure how other backends handle it.

I think Combinable._combine() may need special handling for date - date producing durations though.

[Baptiste Mispelon]

Updated test patch to latest master

[Mariusz Felisiak]

Replying to Josh Smeaton:

This ticket seems really familiar, but I can't seem to find a duplicate at the moment. I'll keep hunting for it. In the mean time, there's a workaround for what you want to do:

Product.objects.annotate(days=ExpressionWrapper(F('start_date')-F('signed_date'), output_field=DurationField())).filter(days__lte=2)

That works on Postgres at least, I'm not sure how other backends handle it.

I think Combinable._combine() may need special handling for date - date producing durations though.

This issue is not about combined expressions and LHS of a lookup, it's about RHS. Django calls get_db_prep_lookup() on LHS values when it's defined for the LHS output field. Currently the following example raises AttributeError on SQLite and MySQL, and ProgrammingError on PostgreSQL and Oracle (where DurationField.get_db_prep_lookup() is no-op).

Experiment.objects.annotate(days=F('start')-F('end')).filter(days__lte=11)

• SQLite and MySQL:

    File "/home/felixx/repo/django/django/utils/duration.py", line 46, in duration_microseconds
      return (24 * 60 * 60 * delta.days + delta.seconds) * 1000000 + delta.microseconds
                             ^^^^^^^^^^
  AttributeError: 'int' object has no attribute 'days'
  

• Oracle and PostgreSQL:

  django.db.utils.ProgrammingError: operator does not exist: interval <= integer
  LINE 1: ...art" - "expressions_ExPeRiMeNt"."end"))::interval <= 11 ORDE...
  

but it works fine when RHS is timedelta on all databases:

Experiment.objects.annotate(days=F('start')-F('end')).filter(days__lte=datetime.timedelta(11))

I'm not sure if there is anything to fix here.

[Jacob Walls]

Thanks for the triage!