ORDERBY, ignore accents

Asked

Viewed 616 times

2

I have a column name that belongs to the model Producer_BVU, whose names are:

2M1J
A. Coelho
ÓRBITA
Bruno

When I command:

\App\Producer_BVU::orderBy('name', 'ASC')->get();

The output gets:

ÓRBITA
2M1J
A. Coelho
Bruno

Is there any way, preferably without modifying mysql charset settings, of in this query orderby the accents are ignored?

Whose desired result is:

2M1J
A. Coelho
Bruno
ÓRBITA

It can be with some function of Laravel after the extraction of the data without problem, it doesn’t need to be soon in the call to the database, maybe even prefer.

My config/database settings for mysql:

...
    'mysql' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST', '127.0.0.1'),
        'port' => env('DB_PORT', '3306'),
        'database' => env('DB_DATABASE', 'forge'),
        'username' => env('DB_USERNAME', 'forge'),
        'password' => env('DB_PASSWORD', ''),
        'unix_socket' => env('DB_SOCKET', ''),
        'charset' => 'utf8',
        'collation' => 'utf8_unicode_ci',
        'prefix' => '',
        'strict' => true,
        'engine' => null,
    ],
...
php mysql laravel laravel-5

  • Try to put it in the file database.php those instructions in mysql - 'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',

  • I edited with these details @Diegosouza. But preface I would not like to touch here

  • And I’d like to move where? It can be with some function of Laravel after the extraction of the data.... Doing this is doing Order By twice because the first time didn’t work right...

  • Same if you run the query directly? Type by phpmyadmin?

  • @Hulahula this orderly field name was created in which collation?

  • 1

    Hello @Virgilionovic, went with utf8_unicode_ci, But I already did, I added to each item of the colection a Slug, and then I order for it. I’ve done it, but you can put the answer if you know another way

Show 1 more comment

1 answer

2

No need to specify ASC, because by default it’s already like that. Just need to specify the DESC.

$collection = \App\Producer_BVU::orderBy('name')->get();

Alternative function for sorting:

$collection = $collection->sortBy('name');

$collection->values()->all();

Another way is to change driver settings mysql in database.php, which particularly I consider more correct to not have to use unnecessary resources, as the above.

'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',

  • Thanks for the reply, but it did not work, the result is even, as for the configs of the mysql driver I have the same that put. I think I’ll create a column column with Slug, for this

Browser other questions tagged php mysql laravel laravel-5

You are not signed in. Login or sign up in order to post.