Case insensitive sort with peewee

Question

The following sort is case-sensitive:

MyModel.select().order_by(MyModel.name)
<class '__main__.MyModel'> SELECT t1."id", t1."name" FROM "mymodel" AS t1 ORDER BY t1."name" []

How do I add a NOCASE to make it case-insensitive?

Or is there another way to make it case-insensitive?

I couldn't find anything about it on the documentation.

Would `MyModel.select().order_by(fn.Lower(MyModel.name))` work? That's how it works for SQLAlchemy. — Martijn Pieters, May 20 '14 at 14:15
For SQLite I see that Peewee also supports [custom collations](http://peewee.readthedocs.org/en/latest/peewee/playhouse.html?highlight=collate#sqlite-ext-api-notes), which should be helpful here. — Martijn Pieters, May 20 '14 at 14:23

score 6 · Accepted Answer · edited May 23 '17 at 12:20

You can use a SQL function (with the fn helper) to lowercase the model name:

MyModel.select().order_by(fn.Lower(MyModel.name))

For SQLite, you should be able to use a custom collation as well:

@db.collation()
def collate_case_insensitive(s1, s2):
    return cmp(s1.lower(), s2.lower())

The @db.collation() decorator provided by the SqliteExtDatabase() subclass lets you register a custom collation function which you can then use to sort by:

MyModel.select().order_by(collate_case_insensitive.collation(MyModel.name))

Of course, SQLite already has a built-in NOCASE collation which should be used here instead, but to use it on must build a SQL() object to include the raw SQL:

MyModel.select().order_by(Clause(MyModel.name, SQL('collate NOCASE')))

This echoes how case insensitive ordering works for SQLAlchemy: SQL alchemy case insensitive sort order

Thanks. I finally understand what those `fn` are. – stenci May 20 '14 at 15:56 — stenci, May 20 '14 at 15:56

Case insensitive sort with peewee

1 Answers1