orderBy case insensitive with Laravel

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.

Answer 1

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.

Answer 2

You can simply do this as:

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

Answer 3

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