unique rule filter question

Hello,

I’m validating that the social security number is unique in the database before I submit. I need to assure that all numbers submitted are unique EXCEPT if the user submits 999999999. I’m using ActiveRecord and regular validation rules() so accomplish this, but it’s not working as I expect it to.

In my validation rules I have this:




['ssn', 'unique', 'targetAttribute' => ['ssn'], 'message' => 'Social security number already exists in the database.', 'filter' => function ($query) { $query->where(['not', ['ssn' => '999999999']]); }],



With this code, it does flag a number as unique. However, it does not exclude the 999999999 number from the check.

Any help is appreciated.

Thank you.

-cs

This should work:




[

    'ssn',

    'unique',

    'message' => 'Social security number already exists in the database.',

    'filter' => function($query) {

        /** @var $query ActiveQuery */

        $query->andWhere(['not', ['ssn' => '999999999']]); // maybe because andWhere()

    }

],



Note that you should not have unique index on ssn column

Second variant you can use:




[

    'ssn',

    function($attribute) {

        if($this->ssn != '999999999') {

            if(self::find()->where(['ssn' => $this->ssn])->count()) {

                $this->addError('Social security number already exists in the database.');

            }

        }

    }

],



1 Like

Indeed the ‘andWhere’ worked as suggested. Thank you very much, that help me out big! :) .

Oops, in second variant I meant this:




[

    'ssn',

    function($attribute) {

        if($this->ssn != '999999999') {

            if(self::find()->where(['ssn' => $this->ssn])->count()) {

                $this->addError('ssn', 'Social security number already exists in the database.');

            }

        }

    }

],