How do I get DBIx::Class to collate with UTF-8?

Question

Hi folks,

I am trying to implement a web search functionality on a MySQL database using DBIx::Class::Resultset. I have it working apart from one problem: when searching for 'ü' the search is performed by MySQL as 'u' (in other words without the umlaut). The same is done for the other 'extended ASCII' characters. The table and the connection is in UTF8.

I have done some testing on the database and found the solution: add 'collate utf8_bin' the the where clause, as in:

 SELECT name FROM my_table WHERE name LIKE '%ü%' COLLATE utf8_bin;

But how do I implement this with DBIx::Class? My search does a 'WHERE ... LIKE' on two tables in the one query.

Thanks in advance,

Mauritz

Answer 1

$rs->search({
    name => \'LIKE ? COLLATE utf8_bin'
  }, {
    bind => [ '%' . $search_key . '%' ]
  }
);

perhaps?