postgresqlyii2validationrules

Implementing Case-Insensitive Unique Validation in Yii2


I'm trying to make the unique rule perform a case-insensitive check. If the database contains the string hello, then inputs like Hello or heLLo should be considered duplicates, and the validation should return an error.

Here is what I tried but not working.

    public function rules()
    {
        return
        [
            ['code', 'unique', 'filter', 'filter' => 'strtolower', 'message' => 'This code already exists.'],
        ];
    }

Here is another solution I found

public function rules()
{
    return
    [
        [
            'code', 'unique', 'targetClass' => self::class, 
            'filter' => function ($query) {
                $query->andWhere('LOWER(code) = LOWER(:code)', [':code' => $this->code]);
            },
            'message' => 'This code already exists (case-insensitive).'
        ]
    ];
}

Can anyone please guide me what I am doing wrong.


Solution

  • I am able to get it working. Posting my solution in case anyone face similar issue.

    Here is how my model code looks like

        public function rules()
        {
            return
            [
                ['code','validateCaseInsensitive']
            ];
        }
    
        public function validateCaseInsensitive($attribute, $params)
        {
            $query = self::find()
                ->where('LOWER(code) = LOWER(:code)', [':code' => strtolower($this->$attribute)]);
    
            // Exclude current record on update
            if (!$this->isNewRecord) {
                $query->andWhere(['<>', 'id', $this->id]);
            }
            
            if ($query->exists()) {
                $this->addError($attribute, 'Code already exists.');
            }
        }