On Dec 5, 5:31 pm, Doug Van Horn <[EMAIL PROTECTED]> wrote:
> I'm running into a funny issue when testing with SQLite3 (Python 2.5).
>
> I have a model with the following (simplified):
>
> class Event(models.Model):
> event_title = models.CharField()
> event_from_date = models.DateField()
>
> I create an event on 2008-01-01 (January 1st) and on 2008-01-02
> (January 2nd).
>
> I run the following in the shell:
>
> In [10]: Event.objects.filter(event_from_date__year=2008)
> Out[10]: [<Event: Event on 2nd.>]
>
> In [11]: Event.objects.filter(event_from_date__month=1)
> Out[11]: [<Event: Event On 1st.>, <Event: Event on 2nd.>]
>
> In [12]: Event.objects.filter(event_from_date__year=2008,
> event_from_date__month=1)
> Out[12]: [<Event: Event on 2nd.>]
>
> Here are the queries (I edited out a bunch of other fields from the
> select clause, but nothing from the where clause, so they're not
> exactly as output from django):
>
> In [18]: connection.queries
> Out[18]:
> [{'sql': u'SELECT
> "app_event"."id","app_event"."event_from_date","app_event"."title"
> FROM "app_event" WHERE ("app_event"."event_from_date" BETWEEN
> 2008-01-01 00:00:00 AND 2008-12-31 23:59:59.999999) ORDER BY
> "app_event"."event_from_date" ASC, "app_event"."title" ASC',
> 'time': '0.001'},
> {'sql': u'SELECT
> "app_event"."id","app_event"."event_from_date","app_event"."title"
> FROM "app_event" WHERE (django_extract("month",
> "app_event"."event_from_date") = 1) ORDER BY
> "app_event"."event_from_date" ASC, "app_event"."title" ASC',
> 'time': '0.001'},
> {'sql': u'SELECT
> "app_event"."id","app_event"."event_from_date","app_event"."title"
> FROM "app_event" WHERE (django_extract("month",
> "app_event"."event_from_date") = 1 AND "app_event"."event_from_date"
> BETWEEN 2008-01-01 00:00:00 AND 2008-12-31 23:59:59.999999) ORDER BY
> "app_event"."event_from_date" ASC, "app_event"."title" ASC',
> 'time': '0.001'}]
>
> Could it be that the 'year' predicate is missing the 'django_extract'
> function?
>
> Thanks for any help...
>
> Doug Van Horn
I'm going to follow up on my own post with some additional
information.
In db.models.query.get_where_clause, the __year lookup type is treated
like a range using 'between':
elif lookup_type in ('range', 'year'): # could remove 'year' from
here.
return '%s BETWEEN %%s AND %%s' % field_sql
Whereas __month and __day use sql functions to extract the relevant
column:
elif lookup_type in ('month', 'day'): # could add 'year' here.
return "%s = %%s" %
connection.ops.date_extract_sql(lookup_type, field_sql)
The %%s portion of those strings are filled in by
db.models.fields.Field.get_db_prep_lookup(self, lookup_type, value).
__year lookups return the following:
elif lookup_type == 'year':
try:
value = int(value)
except ValueError:
raise ValueError("The __year lookup type requires an
integer argument")
return ['%s-01-01 00:00:00' % value, '%s-12-31
23:59:59.999999' % value]
#return ['%s' % value] # could treat year as a single
value.
This matches the between clause we saw earlier.
__month and __day return:
if lookup_type in ('exact', 'regex', 'iregex', 'gt', 'gte',
'lt', 'lte', 'month', 'day', 'search'):
return [value]
This matches the extract function we saw earlier.
So understanding how all this works, my question is why are __year
lookups treated this way?
I changed the django code to treat __year the same as __month and
__day (as commented above) and fixed the issue of the first day of
January being missing from the event_from_date__year=2008 query I
referenced in my first post, so it's at least functional this way (not
necessarily correct).
I'm sure I'm missing something, so if someone knows why __year queries
are treated as betweens rather than sql function extractions I'd like
to know. :-)
Regards,
Doug Van Horn
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---
