2008/4/28 unixdude_from_mars <[EMAIL PROTECTED]>:
>
> I am attempting to query my database for a DateField. I use the
> following lines to set the qset in my view code.
>
> query = request.GET.get('q','')
> if query:
> (
> Q(end__icontains=query) |
> Q(maintenance_contractor__name__icontains=query) |
> Q(purchase_order__icontains=query)
> )
>
> results = Maintenance_item.objects.filter(qset).distinct()
> else:
> results=[]
>
> This code fails and the gives the following dump when I attempt to
> query on date,
> P.S. even an input with an exact match will fail with the same error.
> note the example is a partial match on year.
>
> thanks for any help. --
>
> james
>
>
> Warning at /search/
> Incorrect date value: '%'2007'%' for column 'end' at row 1
> Request Method: GET
> Request URL: http://localhost:8000/search/
> Exception Type: Warning
> Exception Value: Incorrect date value: '%'2007'%' for column 'end'
> at
> row 1
> Exception Location: /opt/csw/lib/python/warnings.py in warn_explicit,
> line 102
> Template error
>
> In template /export/home/jhartley/djcode/mysite/templates/search.html,
> error at line 18
> Caught an exception while rendering: Incorrect date value: '%'2007'%'
> for column 'end' at row 1
First, it looks like there is an extra set of quotes around 2007, as though
the 'q' value you retrieve from the GET parameters is enclosed in quotes. I
wouldn't expect this query to work without first getting rid of the extra
quotes.
The second problem is that Django's icontains comparison maps to MySQL's
LIKE comparison. LIKE is a string comparison function, but a DateField is
not a string datatype. So you are comparing mismatched types, and this
generates a warning from MySQL. You can see this with just the mysql
command, no Django involved (on my own DB):
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5055
Server version: 5.0.45-Debian_1ubuntu3.3-log Debian etch distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> select count(*) from Puzzles where Date like '%2008%';
+----------+
| count(*) |
+----------+
| 740 |
+----------+
1 row in set, 1 warning (0.03 sec)
mysql> show warnings;
+---------+------+-----------------------------------------------------------+
| Level | Code | Message
|
+---------+------+-----------------------------------------------------------+
| Warning | 1292 | Incorrect date value: '%2008%' for column 'Date' at row 1
|
+---------+------+-----------------------------------------------------------+
1 row in set (0.01 sec)
So, mysql was able to compute the right answer, but did issue a warning
about the type mismatch. Under Django that warning causes an error, since,
as I understand it, some MySQL warnings are really serious error conditions,
and it is impossible for Django to distinguish the non-serious from the
serious ones. Therefore all warnings are converted to errors.
> This code DOES work correctly with mysql 5.0.27 and yields dataField
> data properly
>
I'd guess, then, that the warning was added to MySQL sometime between 5.0.27
and 5.0.45. I don't have an old level to check myself.
> Has anyone run across this and if so is there a patch.
>
A syntax to get MySQL to do this kind of comparison without a warning is:
mysql> select count(*) from Puzzles where cast(Date as char) like '%2008%';
+----------+
| count(*) |
+----------+
| 740 |
+----------+
1 row in set (0.00 sec)
However, I don't know of any way to make Django generate that kind of SQL.
Based on the other fields you are including in your filter, it looks like
you want to have a single (string) query parameter and use it to match
against different fields -- some string, at least one not. I don't know if
there is any way to do that. But it is late, so perhaps I am just missing
something...maybe someone else will have a good idea.
Karen
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
