How to add collate to laravel query

Question

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?

score 9 · Answer 1 · answered Dec 12 '14 at 14:13

9

You can do it this way if it solves your problem:

SomeModel::whereField($value)->orderByRaw("name COLLATE utf8_bin ASC")->get();

answered Dec 12 '14 at 14:13

ddn

1,360
11
8

This 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` – BrandonO Sep 01 '20 at 13:23

Antonio Carlos Ribeiro · Accepted Answer · 2013-10-23T09:45:08.740

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(...);

Is 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. — Rob, Oct 23 '13 at 02:08

score 4 · Answer 3 · answered Oct 03 '20 at 20:20

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));

How to add collate to laravel query

3 Answers3

Linked