How to add collate to laravel query
16,361
Solution 1
Since you can configure MySQL driver to use one:
'mysql' => array(
'driver' => 'mysql',
'host' => 'localhost',
'database' => 'database',
'username' => 'root',
'password' => '',
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => '',
),
You can create a different connection for your particular query:
'mysql-collation' => array(
'driver' => 'mysql',
'host' => 'localhost',
'database' => 'database',
'username' => 'root',
'password' => '',
'charset' => 'utf8',
'collation' => '<your collation>',
'prefix' => '',
),
And use that connection on your query:
$users = DB::connection('mysql-collation')->select(...);
EDIT:
On a Model, you probably will be able to set a connection this way:
$posts = new Word;
$posts->setConnection('mysql-collation');
$posts->where(...);
Solution 2
You can do it this way if it solves your problem:
SomeModel::whereField($value)->orderByRaw("name COLLATE utf8_bin ASC")->get();
Solution 3
If you only need to apply this to the values in the WHERE clause:
$value = "àBc123";
$query->whereRaw('field_name COLLATE utf8mb4_bin = (?)', $value);
- This query is both case sensitive and accent sensitive.
- Replace
utf8mb4_bin
with whatever collation makes sense on your system (eg.utf8_bin
). - Using
(?)
should utilize Laravel's query sanitization.
For accent sensitive but NOT case sensitive:
$value = "àBc123";
$query->whereRaw('LOWER(field_name) COLLATE utf8mb4_bin = (?)', strtolower($value));
Author by
Rob
Updated on June 07, 2022Comments
-
Rob almost 2 years
I need to run a query having
collate utf8_bin
like so:SELECT * FROM `table` WHERE `field`='value' collate utf8_bin;
This is strictly for an admin script and I don't want to update the table charset itself, just for the particular query.
Can I do this using the Eloquent ORM or do I need to write this query out?
-
Rob over 10 yearsIs there anyway to do this on a model:
Word::connection('mysql-collation')->where()...
? If I do:DB:connection('mysql-collation')->table()...
, it doesn't let me use->save()
later on the object returned. -
BrandonO over 3 yearsThis worked for me. One issue I had is that I needed to have the collation in double quotes or else I was getting an error. Mine looks like this:
->orderByRaw('name COLLATE "C" ASC');
. Otherwise, I was getting this error:Undefined object: 7 ERROR: collation "c" for encoding "UTF8" does not exist