Case-sensitive "unique" Validator

[lamoni]

Is there a way to get the "unique" validator option to be case insensitive? It seems right now, Laravel assumes you're using a case-insensitive database.

[ArjanSchouten]

(homestead:)

• Mysql is case-insensitive
• Postgresql is case-sensitive
• Sqlite and MSSQL not tested yet

This is different behavior and that's not why I use Laravel. I want to be able to switch database and get the same behavior.

The best solution I think is adding a new option iunique (like ILIKE). It's actually a bit difficult to add this behavior. I think we can't add it in a simple way. Tried code similar to this and it worked.

$attribute = DB::raw('LOWER('.$attribute.')');
$this->validateUnique($attribute, $value, $parameters);

However this violates the idea of an database independent validator. Therefore we've to change the DatabasePresenceVerifier. But telling the DatabasePresenceVerifier which case to choose is impossible. Therefore we've to change the PresenceVerifierInterface.

This means we've to wait for Laravel 5.2 if Taylor decides to add this functionality or accept a PR for this. This because we change the interface which means that mentioning in an upgrade guide is required.

My suggestion is to change the getCount method to:
public function getCount($collection, $column, $value, $excludeId = null, $idColumn = null, array $extra = [], $ignoreCase = true);. However the code becomes more unreadable right now. There are too many parameters. (Probably we can do the same with getMultiCount).

When decided to add this functionality we've another problem. Making the database handling queries case-(in)sensitive is a bit difficult. In postgresql for example we can use ILIKE which means that we've to escape characters used in likes. Furthermore this is slow.

We can use LOWER(column)=LOWER(value) but this can't use indexes.

Conclusion: it's not impossible but is difficult, needs database specific code and can violate performance. We've to add functionality to the Grammers for this.

[lamoni]

@ArjanSchouten Excellent write-up, thanks.

I'd even prefer to make database drivers have to implement some sort of "getIsUnique" type interface, which the unique validator could then reference. I say this because I'm using Neo4j as my database, which I highly doubt would get implemented by default due to the lack of popular use of graph DBs.

[GrahamCampbell]

Ping @taylorotwell.

[fisharebest]

We can use LOWER(column)=LOWER(value)

Not in MySQL. If value is a case-insensitive string, then LOWER(value) is also a case insensitive string. Instead, you need to specify a binary collation

mysql> SELECT 'a' = 'A' COLLATE utf8_bin;
+----------------------------+
| 'a' = 'A' COLLATE utf8_bin |
+----------------------------+
|                          0 |
+----------------------------+

but this can't use indexes.

Yes it can?

mysql> CREATE TABLE t (col VARCHAR(32), INDEX(col));
mysql> INSERT INTO t (col) VALUES ('a'), ('b'), ('A');
mysql> SELECT * FROM t WHERE col = 'A';
+------+
| col  |
+------+
| a    |
| A    |
+------+
mysql> SELECT * FROM t WHERE col = 'A' COLLATE utf8_bin;
+------+
| col  |
+------+
| A    |
+------+
mysql> EXPLAIN SELECT * FROM t WHERE col = 'A' COLLATE utf8_bin;
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|  1 | SIMPLE      | t     | index | col           | col  | 99      | NULL |    3 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+

[ArjanSchouten]

@fisharebest its some time ago when I investigated this. I think it's due to the fact that mysql is by default case-insensitive that mysql is using the index. However if Im right, mysql can be configured to be case sensitive. I wonder if mysql is going to use the index if it's configured to handle queries case sensitive.

Note: mysql isn't the only database which is supported by Laravel. So we've to do some research on sqlite and mssql too!

[sangar82]

@GrahamCampbell Reopen please!

[nshontz]

I second the reopen! Is there a suitable workaround?

[lamoni]

My (crappy) workaround has to been to just have another column specifically for holding the lower-case values, and doing the validation on those columns when needed. For obvious reasons, this is not ideal, but luckily my data set allows me to do this.

[fisharebest]

Is there a suitable workaround?

Create the database/column with case-sensitive collation?

$table->string('col')->collate('utf8_bin');

[nshontz]

@fisharebest Thanks for your reply, I'm using Postgres, collation is en_US.UTF-8, I've also used $table->string('email')->collate('utf8_bin')->change(); but i'm still having the same issue where my unique rule ('email' => 'required|email|unique:user') is case sensitive and allowing multiple.

If this is a me problem or if there's something I can do to fix it, I'm all about doing that, but this behavior seems to be counter intuitive and should be looked into further either.

[fisharebest]

I'm using Postgres

IIRC, Laravel's ->collate() function is only implemented for the MySQL driver. You'll need to use raw SQL.

I wrote a PR for this (#3666) some time ago. It was rejected at the time, although the MySQL parts were subsequently implemented.

If you really don't want to use raw SQL, it is possible to extend the DB drivers.

[tortuetorche]

With PostgreSQL, we can do:

CREATE UNIQUE INDEX users_email_unique on users (LOWER(email));

But the command below, doesn't work:

ALTER TABLE users
  ADD CONSTRAINT users_email_unique UNIQUE (LOWER(email));

Source: http://shuber.io/case-insensitive-unique-constraints-in-postgres

So in my user migration file, I'm doing this:

<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateUsersTable extends Migration
{
    public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->increments('id');
            $table->string('email')/*->unique()*/;
            $table->string('password', 64);
            $table->string('remember_token', 100)->nullable();
            $table->timestamps();
        });
        DB::statement('CREATE UNIQUE INDEX users_email_unique on users (LOWER(email));');
    }

    //...
}