Case insensitive order by with ormlite and sqlite in android
android, ormlite
Solution
I think what you want is to use the `QueryBuilder.orderByRaw(String)` method. It would look something like:
ownerDao.queryBuilder().orderByRaw("Name COLLATE NOCASE").query();
Notice the raw string does not contain the `ORDER BY` part of the string. To quote the javadocs:
Add raw SQL "ORDER BY" clause to the SQL query statement. This should not include the "ORDER BY".
The `TEXT COLLATE NOCASE` `columnDefinition` might work although I don't have any experience with using that at all.
Also, I would not use the `orderBy("Name COLLATE NOCASE", true)` answer since typically ORMLite tries to escape the column names and the quotes would be in the wrong place.
Problem
I want to order my data objects from an ORMLite DAO case insensitively. Currently I am using the following sqlite code to order my owner items case sensitively: ``` ownerDao.queryBuilder().orderBy("Name", true).query(); ``` I see here that sqlite supports case insensitive "order by" with the following raw SQL: ``` SELECT * FROM owner ORDER BY Name COLLATE NOCASE ``` Any easy way (easier than calling `queryRaw()`) of adding the desired suffix? Could an alternative solution be to set the `columnDefinition` property on the DatabaseField annotation to `TEXT COLLATE NOCASE`?