CODEWITHSUNDEEP
phpmysqllaravelcharacter-encodingeloquent
axsor
axsor

Reputation: 470

Ignore accented chars Eloquent query

I'm trying to query registers with fields that can contain accents like à á ä (Catalan language). When I search 'alex' it does not returns 'Àlex' register.

I'm using laravel 5.5, mysql 5.7 and PHP 7.0.

Search code:

$client = new Client();
$client = $client->newQuery();

if ($request->has('name') && !empty($name = $request->input('name'))) 
{
    $client->where('name', 'LIKE', "%$name%");
}

return $client->get();

How can I query registers without distinguishing accents and case of the chars using Eloquent and Laravel?

Upvotes: 6

Views: 4605

Answers (3)

axsor
axsor

Reputation: 470

The problem was the configuration of test. I was using sqlite instead of mysql. Thanks for the answers. The default configuration of laravel for mysql works:

config/database.php

'mysql' => [
    ...
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    ...
]

Upvotes: 2

Rick James
Rick James

Reputation: 142528

SHOW CREATE TABLE. You will probably find that COLLATION ..._bin. Change "bin" to "general_ci" so that accent stripping and case folding will be done during searching and sorting.

Upvotes: 0

Sapnesh Naik
Sapnesh Naik

Reputation: 11656

To have an accent insensitive search you can change the collation at runtime in the raw sql query,

Try:

$client->whereRaw("name like  '%$name%' collate utf8_general_ci ");

Upvotes: 1

Related Questions