orderBy case insensitive with Laravel

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.

You can simply do this as:

$orderClause = 'UPPER('.colname.') ASC';
App\User::orderByRaw($orderClause);

SORT_NATURAL|SORT_FLAG_CASE does not go by orderBy it goes with sortBy on collections, case insensitive orderBy isn't an Eloquent behavior it is better to build your SQL query directly on database