2

I have a json column in my database with french characters. So when I use:

App\Job::where('name->fr', 'like', '%Fune%')->count();

It is not finding results for jobs that has an accent in the name like Funéraire. I can accomplish what I want by adding a collate in the query using the whereRaw:

App\Job::whereRaw('json_unquote(json_extract(`name`, \'$."fr"\')) LIKE \'%Fune%\' collate utf8mb4_general_ci')->count();

However, when I use bindings in my whereRaw method:

App\Job::whereRaw('json_unquote(json_extract(`name`, \'$."fr"\')) LIKE ? collate utf8mb4_general_ci', ['%Fune%'])->count();

I am receiving a database error:

COLLATION 'utf8mb4_general_ci' is not valid for CHARACTER SET 'binary' (SQL: select count(*) from jobs where json_unquote(json_extract(name, '$."fr"')) LIKE %Fune% collate utf8mb4_general_ci)

Just wondering why it's not working when I'm passing it with bindings.

Chin Leung
  • 14,621
  • 3
  • 34
  • 58
  • The error is actually more correct. The collation of JSON columns in MySQL is always a binary unicode collation. – apokryfos Oct 10 '19 at 17:04
  • @apokryfos But then why is it working if I use a raw query? – Chin Leung Oct 10 '19 at 17:07
  • I'll speculate that: A raw string will match the encoding of the connection, not the column. A prepared string will match the encoding of the column. The `utf8mb4_general_ci` collation might not apply to the column encoding while it applies to the connection encoding – apokryfos Oct 10 '19 at 17:09
  • This works for me: `whereRaw('json_unquote(json_extract(`name`, \'$."fr"\')) LIKE convert(? using utf8mb4)', ['%Fune%'])` – Jonas Staudenmeir Oct 10 '19 at 17:13
  • @JonasStaudenmeir Can you post it as the answer? It's working for me too. I'll accept it :) – Chin Leung Oct 10 '19 at 17:20
  • There should be no need to change collation. Could you tell us which `collation` is set for your db driver in `config/database.php`? By default, Laravel uses `utf8mb4_unicode_ci` and that matches `e` with `è` or`é` – Adam Dec 06 '19 at 13:19
  • 1
    @Adam I have the default collation but that doesn't work for json columns. – Chin Leung Dec 06 '19 at 13:41

2 Answers2

0

Chin.

if you have a JSON column the DB, you can directly fire a like query on the column as JSON is a string and like %text% search the text in the string.

you don't have to go through this much trouble for finding the alike name.

your query may somewhat look like this,

App\Job::where('name_of_column_of_json', 'like', '%Fune%')->count();

and you will get the desired output.

hit it and let us know, It works or not.

Mohammed Samgan Khan
  • 168
  • 1
  • 11
  • I've already tried that. It was my first attempt, but as I mentioned in my question, it is not working for results that has a french character such as `Funéraire`. It would only work if I query for `%Fun%` instead. – Chin Leung Oct 11 '19 at 12:30
  • Have to tries passing 'Funé' as input, I guess this will work. – Mohammed Samgan Khan Oct 12 '19 at 05:54
  • That's the point of my question... I want it to find the result even if I search for `Fune`... I know it will work if I search with accents. – Chin Leung Oct 12 '19 at 13:11
0

You need to convert the binding to UTF-8:

App\Job::whereRaw(
    'json_unquote(json_extract(name, \'$."fr"\')) LIKE convert(? using utf8mb4)', 
    ['%Fune%']
)
Jonas Staudenmeir
  • 24,815
  • 6
  • 63
  • 109