DatabaseWrapper operators

[pbzRPA]

I would like to know if anyone knows how to create custom operators
for querysets.

Currently you can do something like:

foo.objects.filter(myfield__icontains = x)

I would like to add my own operator so I can do something like:

foo.objects.filter(myfield__converttext = x)

where "converttext" would be my own operator. Currently there a few
operators in the django/db/backends/mysql/base.py:

operators = {
'exact': '= %s',
'iexact': 'LIKE %s',
'contains': 'LIKE BINARY %s',
'icontains': 'LIKE %s',
'regex': 'REGEXP BINARY %s',
'iregex': 'REGEXP %s',
'gt': '> %s',
'gte': '>= %s',
'lt': '< %s',
'lte': '<= %s',
'startswith': 'LIKE BINARY %s',
'endswith': 'LIKE BINARY %s',
'istartswith': 'LIKE %s',
'iendswith': 'LIKE %s',
}

I would like to add to this dict without modifying the django code.

Any help would be appreciated.

[Malcolm Tredinnick]

It's possible to add new lookup types, providing you use a custom
queryset, because you have to set the Query.query_terms attribute.
Easiest way to see how this is done in practice, is to look at
django.contrib.gis, which adds a bunch of GIS-specific query terms. In
particular, django/contrib/gis/db/models/sql/query.py.

One day we might add a way to add custom lookup types for fields, but
it's not entirely trivial, as there's a fine balance between making
something that's not too difficult to use, whilst being portable and
without leaking SQL all over the Field subclasses.

Regards,
Malcolm

[Ed Menendez]

It might be easier to write a manager depending on what exactly you're
trying to do. In my opinion you should write an operator for a
database feature that you're trying to implement but if you're trying
to cleanup text or transform text in some way... I think a manager
might be a better solution.

If the problem involves converting the text to the left side of the
equal sign (the table column).. then I would be careful you don't get
table scans all over the place.

[pbzRPA]

Hi, thank you for your input.

The trouble I am having is that I have a polish database of users with
polish letters in their names and surnames. Now not all international
users have access to polish letters on their system therefore I need
to be able to let a user enter English letters like "L" in there
search and the search results should include all the polish names with
"Ł" in them. This was why I was thinking of writing an operator that
calls a function in the database to convert the letters on the run.

Thanks.

[Clément Nodet]

2009/5/5 pbzRPA <pbz...@gmail.com>

Hi,

Depending on the database engine you're using, it could be way easier
to rely on collations to solve your problem.

Check out this link ( http://bugs.mysql.com/bug.php?id=9604 ) if you're running
MySQL; regarding the 'Ł' and 'L' equality in polish and general unicode
collations. Seems like cp1250_general_* is the only one comparing all polish
characters 'correctly' to their english equivalent. Hope that helps,
--
Clément

[pbzRPA]

Hi,

Thanks for the advice, it has solved some of my problem but the "ś"
for example still does not get found when looking for "s".

But it's a step in the right direction. :)

Thanks

[pbzRPA]

One more thing. Would you know if its mysql version related? I am
running 5.0.56 and am not sure if that might be the problem.

Thanks

[Clément Nodet]

Hi,

I'm not sure this mailing list is the best place to discuss further
that topic, I guess you'll get more feedback on
http://forums.mysql.com/ if you want to keep going in the collation
direction, or on django-users.

Anyway, on that 'ś' problem, you should know more or less the full
list of polish characters and their english counterpart you want to
have equality for. I suggest you do some tests using different
built-in collations (utf8_general_ci, utf8_unicode_ci, utf8_polish_ci,
cp1250_general_ci, cp1250_polish_ci, etc.) to see if one of those
matches exactly your needs.

Well, there might also be none. But in that case, there's a
straightforward solution, just define your own collation. In MySQL 5
you don't need to recompile if you want to add a new collation based
on utf8_unicode_ci. Have a look at those links :
http://forge.mysql.com/w/images/b/b7/HowToAddACollation.pdf
http://dev.mysql.com/doc/refman/5.0/en/adding-collation-unicode-uca.html
http://unicode.org/reports/tr35/

Then you can keep tables text field in a 'regular' polish collation,
but use the custom one for search queries.
--
Clément

2009/5/6 pbzRPA <pbz...@gmail.com>: