Doctrine LIKE with Russian characters

Question

I am using doctrine query builder to filter records this is the part which I think is causing the problem:

$query = $query->where('i.offernumber LIKE :search')
    ->orWhere('o.client LIKE :search')
    ->orWhere('o.sticker LIKE :search')
    ->orWhere('o.gu LIKE :search')
    ->orWhere('o.project LIKE :search')
    ->orWhere('o.quantity LIKE :search')
    ->orWhere('o.date LIKE :search')
    ->leftJoin('o.previous', 'p')
    ->leftJoin('p.input', 'pi')
    ->orWhere('pi.offernumber LIKE :search')
    ->setParameter('search', "%".$search."%");

The problem is that I get following error, when I enter Russian alphabet characters:

An exception occurred while executing 'SELECT c0_.id AS id0, c0_.client AS client1, c0_.quantity AS quantity2, c0_.next AS next3, c0_.status AS status4, c0_.date AS date5, c0_.sticker AS sticker6, c0_.gu AS gu7, c0_.project AS project8, c0_.completed AS completed9, c0_.rversion AS rversion10, c0_.input_id AS input_id11, c0_.previous AS previous12, c0_.user_id AS user_id13 FROM crm_order c0_ LEFT JOIN crm_input c1_ ON c0_.input_id = c1_.id LEFT JOIN crm_order c2_ ON c0_.previous = c2_.id LEFT JOIN crm_input c3_ ON c2_.input_id = c3_.id WHERE c1_.offernumber LIKE ? OR c0_.client LIKE ? OR c0_.sticker LIKE ? OR c0_.gu LIKE ? OR c0_.project LIKE ? OR c0_.quantity LIKE ? OR c0_.date LIKE ? OR c3_.offernumber LIKE ?' with params ["%\u041f\u0440\u0438\u0442\u043e\u0447\u043d\u0430\u044f%", "%\u041f\u0440\u0438\u0442\u043e\u0447\u043d\u0430\u044f%", "%\u041f\u0440\u0438\u0442\u043e\u0447\u043d\u0430\u044f%", "%\u041f\u0440\u0438\u0442\u043e\u0447\u043d\u0430\u044f%", "%\u041f\u0440\u0438\u0442\u043e\u0447\u043d\u0430\u044f%", "%\u041f\u0440\u0438\u0442\u043e\u0447\u043d\u0430\u044f%", "%\u041f\u0440\u0438\u0442\u043e\u0447\u043d\u0430\u044f%", "%\u041f\u0440\u0438\u0442\u043e\u0447\u043d\u0430\u044f%"]:

SQLSTATE[HY000]: General error: 1271 Illegal mix of collations for operation 'like'

What may be causing this error, and is there a way to fix it?

EDIT

After pushing code around and trying various combinations I pinpointed cause of error, it's these 2 folowing lines:

->orWhere('o.quantity LIKE :search')
->orWhere('o.date LIKE :search')

Apratently as those 2 variables are not string (number and date mysql types), and above query tries to "LIKE %string%" for a columns which is not a string type It causes the error. Same problem as in this post

What might be possible solutions to this problem, as I still need to search for string, number and date?

possible duplicate of [Troubleshooting "Illegal mix of collations" error in mysql](http://stackoverflow.com/questions/3029321/troubleshooting-illegal-mix-of-collations-error-in-mysql) — Mihai, Nov 06 '14 at 13:54

score 0 · Answer 1 · answered Nov 06 '14 at 16:16

0

Did you set charset in config.yml

doctrine:
    dbal:
        {# ... #}
        charset:  UTF8

answered Nov 06 '14 at 16:16

Lord Zed

750
7
28

Doctrine LIKE with Russian characters

EDIT

1 Answers1