Search code examples
pythonsqlitepeewee

Case insensitive sort with peewee

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.

Solution

  • 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