Answer a question

I'm using Eloquent (Laravel 5.4) and a postgreSQL database. I created a users table with a name string column. Running this code returns users ordered by name BUT the search is case-sensitive:

$users = \App\User::orderBy('name')->get();

This code returns something like:

Alix
Beatrice
Zorro
adam
bill

Is there a way to get this list case-insensitive. I would like to have something like:

adam
Alix
Beatrice
bill
Zorro

Is there a way to get a "case-insensitive order by" result ?

My question is about orderBy and Eloquent, not sortBy and Collections.

Answers

1. Why are you getting this result?

It is actually your database's collation which determines how strings are compared and ordered.

Probably your current (table) collation is a case-sensitive one. In Postgres, this collation is probably 'C' or 'POSIX'. This implies that App\User::where('name', 'aDaM')->first(); does NOT retrieve adam's entry and App\User::orderBy('name')->get(); yields the user names in a case-sensitive alphabetic order. You need a case-insensitive collation in order to have the opposite results. In Postgres, an example of such a collation is en_US.UTF8 (in case your encoding is UTF8).

2. Possible solutions

(1) Just put your query result in case-insensitive order (in Laravel)

App\User::orderBy('name')->get()->sortBy('name', SORT_NATURAL|SORT_FLAG_CASE);

Since Laravel 5.3, Eloquent's get() returns a Collection.

(2) Write a raw SQL query specifying the collation (in Laravel), for example

DB::select('select name from users order by name COLLATE "en_US.utf8"');

This statement returns an array.

(3) Choose another collation for your database. This solution will avoid similar problems in the future.

When you are in a dev situation and working with Laravel's Migrations and Seeders the best solution is to

• delete the database
• create the database again, with the right collation
• run the migrations and seeders

When you are in an other dev situation, you can

• export the database to a dump file (use the create option)
• replace any reference to the old collation by a refence to the new one in the dump file (text editor > find & replace)
• delete the database
• import the dump file

Note that you can change the collations on a running database as well, but be prepared for databases as well as tables as well as columns having (default) collation settings and for problems with indices.