Yii 1.1: unique-attributes-validator

Validate unique constraints with more then one attribute
22 followers

Yii comes with bucket of validators including a unique validator which validates whether an attribute value is unique in the corresponding database table. But what if your database unique constraint contains more than one attribute?

I have seen a couple Yii extensions which solve the problem but none of them are using the built-in CUniqueValidator parts. There are two ways to solve the validation problem, the first one is to add some criteria to the unique validator:

public function rules() {
    return array(
        array('firstKey', 'unique', 'criteria'=>array(
            'condition'=>'`secondKey`=:secondKey',
            'params'=>array(
                ':secondKey'=>$this->secondKey
            )
        )),
    );
}

That will do the trick, however it can get a bit cleaner. The UniqueAttributesValidator does this for you:

public function rules() {
    return array(
        array('firstKey', 'UniqueAttributesValidator', 'with'=>'secondKey'),
    );
}

The UniqueAttributesValidator uses parts of CUniqueValidator and extends it by adding certain criteria. It also works for unique constraints with N attributes:

public function rules() {
    return array(
        array('firstKey', 'UniqueAttributesValidator',
                      'with'=>'secondKey,thirdKey,...'),
    );
}

Requirements

Yii 1.0 or above

Usage

public function rules() {
    return array(
        array('firstKey', 'UniqueAttributesValidator',
                      'with'=>'secondKey,thirdKey,...'),
    );
}

Resources

Total 11 comments

#18548 report it
tanebisse at 2014/11/14 04:50am
Error message for each attribute

I custom the code for display error message for each attribute in "with".

<?php
class UniqueAttributesValidator extends CValidator {
 
    /**
     * The attributes boud in the unique contstraint with attribute
     * @var string
     */
    public $with;
 
    /**
     * Validates the attribute of the object.
     * If there is any error, the error message is added to the object.
     * @param CModel $object the object being validated
     * @param string $attribute the attribute being validated
     */
    protected function validateAttribute($object,$attribute) {
 
        $with = explode(",", $this->with);
        if (count($with) < 1)
            throw new Exception("Attribute 'with' not set");
        $conditionParams = array();
        $params = array();
        $uniqueValidator = new CUniqueValidator();
        $uniqueValidator->attributes = array($attribute);
        foreach ($with as $attribute) {
            $conditionParams[] = "{$attribute}=:{$attribute}";
            $params[":{$attribute}"] = $object->$attribute;
            array_push($uniqueValidator->attributes, $attribute);
        }       
        $uniqueValidator->message = $this->message;
        $uniqueValidator->on = $this->on;
 
        $condition = implode(" AND ", $conditionParams);
        $uniqueValidator->criteria = array(
            'condition'=>$condition,
            'params'=>$params
        );
        $uniqueValidator->validate($object);
    }
 
}
#17715 report it
Isidoro Arroyo at 2014/07/17 06:32am
Don't work with PostgreSQL database

Hi. Thanks for the extension. It works fine but not when I use a PostgreSQL database.

The server raise an exception: Undefined function: 7 ERROR: operator don't exists: ` integer LINE 1....

This exception is because the ` cahracter can't be used in postgreSQL queries. PostgreSQL uses the " character instead, but it's only necessary when the column name has special characters.

For me the extension works fine when I changed the line num. 27 removing the ' characters:

foreach ($with as $attribute) {
          $conditionParams[] = "{$attribute}=:{$attribute}";
          $params[":{$attribute}"] = $object->$attribute;
      }

The model must be independent to the database backend, so, if is necessary insert the ' character shoud be the user who put it in the rules method:

public function rules() {
    return array(
        array('firstKey', 'UniqueAttributesValidator',
              'with'=>'`secondKey`,`thirdKey`,...'),
              );
    }

It would be great if you change the extensio to make it database-independent.

Regards. Isidoro.

#16081 report it
aj07mm at 2014/01/17 04:34am
Problem with Gii

Hey, im getting this exception when i try to use Gii - models,

UniqueAttributesValidator and its behaviors do not have a method or closure named "init".

#9906 report it
mirsch at 2012/09/20 05:37pm
work's like a charm except for values of NULL in MySQL

Thanks for this great extension, which works like a charm. But I noticed it's not working if one attribute is NULL. This is because in MySQL a comparison to NULL always returns false. e.g.

SELECT * FROM table WHERE col = NULL

will give 0 Rows even if there are rows col = NULL

So you have to compare using IS NULL in MySQL e.g.

SELECT * FROM table WHERE col IS NULL

To solve this, I slightly modified your extension:

protected function validateAttribute($object,$attribute) {
    $with = explode(",", $this->with);
    if (count($with) < 1)
        throw new Exception("Attribute 'with' not set");
    $uniqueValidator = new CUniqueValidator();
    $uniqueValidator->attributes = array($attribute);
    $uniqueValidator->message = $this->message;
    $uniqueValidator->on = $this->on;
    $conditionParams = array();
    $params = array();
    foreach ($with as $attribute) {
        $attribute = trim($attribute);
        if ($object->$attribute === null) {
            $conditionParams[] = "`{$attribute}` IS :{$attribute}";
        } else {
            $conditionParams[] = "`{$attribute}`=:{$attribute}";
        }
        $params[":{$attribute}"] = $object->$attribute;
    }
    $condition = implode(" AND ", $conditionParams);
    $uniqueValidator->criteria = array(
        'condition'=>$condition,
        'params'=>$params
    );
    $uniqueValidator->validate($object);
}

The interesting Part is form line 13 to 15, which checks the value for NULL and modifies the query.

#9467 report it
xNicox at 2012/08/13 09:14pm
where to install

abhishek2890

I downloaded to /protected/extensions/

and I add this rule: array('nro_documento', 'ext.UniqueAttributesValidator', 'with'=>'fk_tipodocumento_id'),

Be aware of "ext" prefix ! Work like a charm.

Regards

#9202 report it
Abhishek Shah at 2012/07/28 07:47am
where I put UniqueAttributesValidator.php

I am not understanding that,where to put file UniqueAttributesValidator.php

#8548 report it
Mariusz W. at 2012/06/11 01:49pm
Integrate with yii core

I agreee, this extensions should be integrated with yii core! :)

#7002 report it
Tijnos at 2012/02/18 12:39pm
RE: little bit problem

Ah indeed, a bug in the documentation! It should be array('firstKey', 'UniqueAttributesValidator', 'with'=>'secondKey'), And for more attributes you can comma-seperate them: array('firstKey', 'UniqueAttributesValidator', 'with'=>'secondKey,thridKey,...'), I will update the documentation.

To override the default error message you can set the 'message' property, the extension will pass that to the CUniqueValidator

#6999 report it
Peter JK at 2012/02/18 12:03pm
little bit problem

wonderful,

i have been waiting this extension.. but seem not work when I use:

array('firstKey', 'UniqueAttributesValidator', 'with'=>array('secondKey')),

the error said like this

explode() expects parameter 2 to be string, array given ..

error on line 17: $with = explode(",", $this->with);

but it work when I use this

array('firstKey', 'UniqueAttributesValidator', 'with'=>'secondKey'),

:-D ...

Probably, you may add ErrorMessage on this extension, so it will override the default error message that come only for firstKey..

But overall, this is a good extension.. Thank you..

#6994 report it
Tijnos at 2012/02/17 10:21am
RE: nice , very useful

Thank you for the appreciation.

Good point intergrate this into CUniqueValidator. In that case the mapping of 'unique'=>'CUniqueValidator' should be 'unique'=>'UniqueValidator'. Do you have a suggestion how to achieve this in the configuration file?

A solution would be to put CUniqueValidator::$builtInValidators['unique']='UniqueValidator'; somewhere in the beginning of the request, but in that case CValidator will be loaded every request instead of lazy-loaded.

#6987 report it
yiqing95 at 2012/02/17 07:01am
nice , very useful

thanks for sharing !actually i seldom met the composite unique constraint . but your work is helpful and give a good example for using 'unique' validator of yiis on this situation . and your extension is also save us a lot of time .

by the way , it seems better to integrate this functionality to the core CUniqueValidator , just add another attribute : 'with' .

Leave a comment

Please to leave your comment.

Create extension