Django makes a request with a custom sort
This is how you can use a specific sort instead of setting the default sort for a given table / column. I assume you always want this to be case insensitive to utf8_general_ci, but you can easily change that in code or add it as a variable.
Note the use of params kwarg instead of the db literal function. Params exists for the same purpose.
def iexact(**kw):
fields = [['%s=%%s collate utf8_general_ci'%field,value] for (field,value) in kw.items()]
return dict(where=[f[0] for f in fields], params=[f[1] for f in fields])
if User.objects.extra(**iexact(username='joeblow')).exists():
status = "Found a user with this username!"
source to share
I solve this using a bit of hack;
The Django extra
method is similar to the method raw
, it uses the statetment request directly;
MyModel.objects.extra(where=["name LIKE '%%" + name + "%%' COLLATE utf8_general_ci"])
But like this sql injection is possible. We need to avoid the variable name
. I have searched a lot for a function that just escapes the line for db. Found one in the package MySQL-python
, but it cannot escape unicode strings. Also, the package has a literal
method in connection
, but we need an instance to use it (maybe it's for the db characteristic).
Finally, I used Django db.connection.cursor
.
from django.db import connection
cursor = connection.cursor()
name = cursor.db.connection.literal(name)[1:-1] # [1:-1] excluding quotes
So we also need an instance, but I'm guessing this doesn't require a db connection. And I believe this db method is independent. If I am wrong, correct me.
source to share
Usage is a extra()
little messy. Now you can do something like this with Func () (since Django 1.8):
username_ci = Func(
'username',
function='utf8_general_ci',
template='(%(expressions)s) COLLATE "%(function)s"')
This can be used in annotate()
:
User.objects.annotate(uname_ci=username_ci).filter(uname_ci='joeblow').exists()
Or in order_by()
to override the default sorting rules when sorting:
User.objects.order_by(username_ci)
It might sound like a mess now, but if you look at the docs and the Func () code , you'll find that it's very easy to subclass it and make it a reusable collation setter.
I used this trick with Postgres database.
source to share