how to use SQL wildcard % with Queryset extra>select?
- by tylias
I'm trying to add weights to search terms I'm using to filter a queryset. Using the '%' wildcard is causing me some problems.
I'm using the extra() modifier to add a weight parameter to the queryset, which I will be using to inform a sort ordering.
(See http://docs.djangoproject.com/en/1.1/ref/models/querysets/#extra-select-none-where-none-params-none-tables-none-order-by-none-select-params-none )
Here's the gist of the code:
def viewname(request)
...
exact_matchstrings=""
exact_matchstrings.append("(accountprofile.first_name LIKE '" + term + "')")
exact_matchstrings.append("(accountprofile.first_name LIKE '" + term + '\%' + "')")
extraquerystring = " + ".join(exact_matchstrings)
return_queryset = return_queryset.extra(
select = { 'match_weight': extraquerystring },
)
The effect I'm going for is that if the search term matches exactly, the weight associated with the record is 2, but if the term merely starts with the search term and isn't an exact match, the weight is 1. (for example, if 'term' = 'Jon', an entry with first_name='Jon' gets a weight of 2 but an entry with an entry with first_name = 'Jonathan' gets a weight of 1.)
I can test the statement in SQL and it seems to work well enough. If I make this SQL query from the mysql shell, no problem:
select (first_name like "Carl") + (first_name like "Car%") from accountprofile;
But trying to run it via the extra() modifier in my view code and evaluating the resulting queryset gives me the following error:
Traceback (most recent call last):
File "<console>", line 1, in <module>
File "/usr/local/lib/python2.6/dist-packages/django/db/models/query.py", line 68, in __repr__
data = list(self[:REPR_OUTPUT_SIZE + 1])
File "/usr/local/lib/python2.6/dist-packages/django/db/models/query.py", line 83, in __len__
self._result_cache.extend(list(self._iter))
File "/usr/local/lib/python2.6/dist-packages/django/db/models/query.py", line 238, in iterator
for row in self.query.results_iter():
File "/usr/local/lib/python2.6/dist-packages/django/db/models/sql/query.py", line 287, in results_iter
for rows in self.execute_sql(MULTI):
File "/usr/local/lib/python2.6/dist-packages/django/db/models/sql/query.py", line 2369, in execute_sql
cursor.execute(sql, params)
File "/usr/local/lib/python2.6/dist-packages/django/db/backends/util.py", line 22, in execute
sql = self.db.ops.last_executed_query(self.cursor, sql, params)
File "/usr/local/lib/python2.6/dist-packages/django/db/backends/__init__.py", line 217, in last_executed_query
return smart_unicode(sql) % u_params
ValueError: unsupported format character ''' (0x27) at index 309
I've tried it escaping and not escaping % wildcard but that doesn't solve the problem. Doesn't seem to affect it at all, really. Any ideas?
