Raw SQL as column on Unique Validation Rule

[tembra]

Laravel Version

10.39.0

PHP Version

8.2.14

Database Driver & Version

PostgreSQL 15.5 on aarch64-apple-darwin21.6.0, compiled by Apple clang version 14.0.0 (clang-1400.0.29.102), 64-bit

Description

It's not possible to use raw SQL as a column on unique validation rule.

If you simple wrap the raw SQL in quotes Laravel accepts it as is, but the database interprets it as a table field.

In the other hand if you do not wrap it with quotes or if you wrap it with backtick Laravel interprets the commas as a part of unique validation rule (unique:table,column,ignore,idColumn,formatWheres).

Steps To Reproduce

Imagine you have a document column on database of type string that must be unique and stores unmasked data. For example you have a document number 012.345-6 that is stored as 0123456.

When showing this field you have an attribute casting on the model that applies the mask on getter and removes the mask on setter.

Finally a new request comes from a form that sets a new model. So you need to validate the model data, and for any reason you can't change the value that will be evaluated to remove the mask, e.g. because it's coming directly from the model that is applying the casting or because other validation rule may need the mask.

But knowing that you try to apply the mask on the column that will be compared with the value by using any of these:

Rule::unique(
    'table',
    "REGEXP_REPLACE(document, '(\d{3})(\d{3})(\d{1})', '\1.\2-\3', 'g')",
)->ignore($record);

Rule::unique(
    'table',
    "\"REGEXP_REPLACE(document, '(\d{3})(\d{3})(\d{1})', '\1.\2-\3', 'g')\"",
)->ignore($record);

Rule::unique(
    'table',
    "`REGEXP_REPLACE(document, '(\d{3})(\d{3})(\d{1})', '\1.\2-\3', 'g')`",
)->ignore($record);

"unique:table,REGEXP_REPLACE(document, '(\d{3})(\d{3})(\d{1})', '\1.\2-\3', 'g'),1,id"

"unique:table,\"REGEXP_REPLACE(document, '(\d{3})(\d{3})(\d{1})', '\1.\2-\3', 'g')\",1,id"

"unique:table,`REGEXP_REPLACE(document, '(\d{3})(\d{3})(\d{1})', '\1.\2-\3', 'g')`,1,id"

But nothing works. Then you need to write your own validation rule.

Note

In your own validation rule you make the rules 😝 So you can change the value because it will not change for others validation rules: where('document', preg_replace('/\D/', '', $value)) or you can use raw SQL whereRaw("REGEXP_REPLACE(document, '(\d{3})(\d{3})(\d{1})', '\1.\2-\3', 'g')", $value)

---

It would be nice if we had something like:

// RawSql would be a type wrapper just to let us know if we need to parse the column or not
Rule::unique(
    'table',
    RawSql::make("REGEXP_REPLACE(document, '(\d{3})(\d{3})(\d{1})', '\1.\2-\3', 'g')"),
)->ignore($record);

Rule::unique(
    'table',
    "raw(REGEXP_REPLACE(document, '(\d{3})(\d{3})(\d{1})', '\1.\2-\3', 'g'))",
)->ignore($record);

"unique:table,raw(REGEXP_REPLACE(document, '(\d{3})(\d{3})(\d{1})', '\1.\2-\3', 'g')),1,id"

Or maybe it will be more intuitive and easy to implement a new property rawColumn that is used if column is null or if given, takes precedence over column.

[anasmorahhib]

I think you can do this using custom validation rules:
custom-validation-rules

I haven't tried the code but it will be something like this:

<?php
 
namespace App\Rules;
 
use Closure;
use Illuminate\Contracts\Validation\ValidationRule;
use Illuminate\Support\Facades\DB;
 
class MyUnique implements ValidationRule
{
    /**
     * Run the validation rule.
     */
    public function validate(string $attribute, mixed $value, Closure $fail): void
    {
        $exist= DB::table('table')
                ->whereRaw("your raw query where $value")
                ->first();
        if ($exist) {
          $fail('The :attribute must be unique.');
         }

    }
}

[tembra]

I think you can do this using custom validation rules

Yeah, I can. I stated this on note section, just after mention that I need to write my own validation rule to achieve that.

The suggestion here is to achieve this result without the need to write a custom validation rule.

Anyway thanks for the answer.