Django making a query with custom collation

Question

Is it possible to make query with a collation different from database table have?

Answer 1

Using extra() is a little messy. Something similar can now be achieved with Func() expression (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 default collation rules when sorting:

User.objects.order_by(username_ci)

Now, it still may seem messy, but if you look at the docs and code of Func(), you will discover that it is very easy to subclass it and make a reusable collation setter.

I used this trick with Postgres database.

Answer 2

Here is how you can use a specific collation instead of the default collation for a given table/column. I'm assuming you always want that to be the case insensitive utf8_general_ci, but you can easily change that in the code or add it as a variable.

Note the use of the params kwarg instead of the db literal function. Params exists for the exact 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!"