2

I found the Scala slick package's "sortBy" method is not case sensitive. Ex: after implementing the following command: q.sortBy(columnMap("name").desc), I got:

TestingIsFun, testing foo1, Testing foo,

Is this expected behavior? How can I make it case sensitive? Thx.

matanster
  • 15,072
  • 19
  • 88
  • 167
SexyNerd
  • 1,084
  • 2
  • 12
  • 21
  • 3
    MySQL is not case sensitive. This is usually worked around by adding `BINARY` to the query. *e.g.:* `SELECT * FROM users ORDER BY BINARY fullName`. Using an ORM will certainly make that tricky. – Michael Zajac Jun 18 '14 at 00:28
  • Looks like *["BINARY" in mySQL](http://dev.mysql.com/doc/refman/5.0/en/sorting-rows.html)* makes that tricky (as well as ORM)! I did some search but couldn't find the counterpart in Slick. Looks like Slick itself is not able to do it.. – SexyNerd Jun 18 '14 at 01:30

2 Answers2

1

I think as it currently stands, slick just depends on the RDBMS default handling of case in sorting. You did not mention the RDBMS type, but e.g. in mysql, case-insensitive is the default in sorting. However, you can define a column to-be-sorted in a way overiding that, in mysql, as per Altering Mysql Table column to be case sensitive. This will work without having to touch the query or slick parameters, as the solution is at the schema definition level. It should be possible to define the column as a binary string in the first place, with slick if needed:

O.DBType("binary") in the slick column definition should work for that.

Community
  • 1
  • 1
matanster
  • 15,072
  • 19
  • 88
  • 167
1

When it comes to the database, the sorting of particular column will be done according to the collation for that column. By default, MySQL uses case-insensitive collation (unless you specify binary charset). You can override the default collation on any of the 4 levels (server, database, table or column) or even only in specific ORDER BY clause. Which way is the most efficient, depends on your particular use case. Using case-sensitive collation obviously affects performance, so most of the time it makes sense doing it either on table or on column level.

Ashalynd
  • 12,363
  • 2
  • 34
  • 37