yii: how to make a unique rule for two attributes

Question

I have a table like this: (id, name, version, text). (name, version) is unique key, how can i make a rule to validate this.

Coplanar

Answer 1

This can be done by Yii itself, you do not need an extension for it. However an extension can help cleaning up the rules() method as described here:

http://www.yiiframework.com/extension/unique-attributes-validator/

This is the code (copied from that site) which will work without using the extension:

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

In case the value of $this->secondKey is not available inside rules()-method you can add the validator in CActiveRecords beforeValidate()-method like this:

public function beforeValidate()
{
    if (parent::beforeValidate()) {

        $validator = CValidator::createValidator('unique', $this, 'firstKey', array(
            'criteria' => array(
                'condition'=>'`secondKey`=:secondKey',
                'params'=>array(
                    ':secondKey'=>$this->secondKey
                )
            )
        ));
        $this->getValidatorList()->insertAt(0, $validator); 

        return true;
    }
    return false;
}

Perice

I tried this method, but i am not getting the value $this->secondKey – Killjoy

$this->secondKey should be the second attribute that has to be unique. You have to adjust this to your attribute name. – Perice

i know that. I tried with my attribute name. I'm not just getting the value of attribute inside rules() – Killjoy

Thanks. I will check it. BTW i ended up in writing a custom validator method. – Killjoy

+1 for addressing the case where secondKey is not available inside rules(). That solved my problem here. – Pointblank

Usually it's also possible to access secondKey in rules() direct from $_GET or $_POST - just need to use it carefully (f.e. because of possible SQL injections) ':secondKey'=>isset($_POST[get_class($this)]['secondKey']) ? $_POST[get_class($this)]['secondKey'] : 0, – Gait

@Gait this is not a good idea. The model should never access request data directly. This makes it impossible to reuse it in other places and makes code really hard to understand and maintain. – Perice

@cebe, of course it's not perfect idea - that's why I said "use it carefully", but... to be fair, I don't understand why you think, that it makes $_POST impossible to reuse in other places. Could you explain? Also, on the other side, maintaining validators written in two different places (rules() and beforeValidate()) isn't perfect solution too. What I'm trying to say is: both solutions are some kind of workarounds, yours is cleaner and safer, mine is only faster. – Gait

@Nashi, the point of clean code is not to be faster now, but so save your hours of debugging later. If everything is in one class you have that class to look at. If you use $_POST, you have your entire code base to search for bug. – Perice

Answer 2

You do not need complicated content of rules() method nor 3rd party extensions. Just create your own validation method. It's much easier to do it on your own.

public function rules()
{
  return array(
    array('firstField', 'myTestUniqueMethod'),
  );
}

public function myTestUniqueMethod($attribute,$params)
{

   //... and here your own pure SQL or ActiveRecord test ..
   // usage: 
   // $this->firstField;
   // $this->secondField;
   // SELECT * FROM myTable WHERE firstField = $this->firstField AND secondField = $this->secondField ...
   // If result not empty ... error

  if (!$isUnique)
  {
    $this->addError('firstField', "Text of error");
    $this->addError('secondField', "Text of error");
  }

}

Infiltrate

Answer 3

Yii1 :

http://www.yiiframework.com/extension/composite-unique-key-validatable/

Yii2 :

// a1 needs to be unique
['a1', 'unique']
// a1 needs to be unique, but column a2 will be used to check the uniqueness of the a1 value
['a1', 'unique', 'targetAttribute' => 'a2']
// a1 and a2 need to be unique together, and they both will receive error message
[['a1', 'a2'], 'unique', 'targetAttribute' => ['a1', 'a2']]
// a1 and a2 need to be unique together, only a1 will receive error message
['a1', 'unique', 'targetAttribute' => ['a1', 'a2']]
// a1 needs to be unique by checking the uniqueness of both a2 and a3 (using a1 value)
['a1', 'unique', 'targetAttribute' => ['a2', 'a1' => 'a3']]

http://www.yiiframework.com/doc-2.0/yii-validators-uniquevalidator.html

Runny

Answer 4

They've added support for unique composite keys in the next release candidate of Yii1.14rc, but here's (yet another) solution. BTW, this code uses the same 'attributeName' in the rules that the Yii framework will use in the next official release.

protected/models/Mymodel.php

    public function rules()
    {
        return array(
            array('name', 'uniqueValidator','attributeName'=>array(
              'name', 'phone_number','email') 
        ),
...

• 'name' in the beginning of the rule is the attribute the validation error will be attached to and later output on your form.
• 'attributeName' (array) contains an array of keys that you would like to validate together as a combined key.

protected/components/validators/uniqueValidator.php

  class uniqueValidator extends CValidator
    {
        public $attributeName;
        public $quiet = false; //future bool for quiet validation error -->not complete
    /**
     * 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)
    {
        // build criteria from attribute(s) using Yii CDbCriteria
        $criteria=new CDbCriteria();
        foreach ( $this->attributeName as $name )
            $criteria->addSearchCondition( $name, $object->$name, false  );

        // use exists with $criteria to check if the supplied keys combined are unique
        if ( $object->exists( $criteria ) ) {
            $this->addError($object,$attribute, $object->label() .' ' .
              $attribute .' "'. $object->$attribute . '" has already been taken.');
        }
    }

}

You can use as many attributes as you like and this will work for all of your CModels. The check is done with "exists".

protected/config/main.php

'application.components.validators.*',

You may have to add the above line to your config in the 'import' array so that uniqueValidator.php will be found by the Yii application.

Positive feedback and changes are very welcome!

Legislatorial

I'm still on 1.13 for a pretty big app that I haven't had time to upgrade. From a quick test, this works great, thanks! It also avoids having to either create a custom validator (not hard, granted but can get messy) and solves some of the issues with passing a dynamic value as the second attribute (can get really messy). – Luca

Answer 5

In Yii2:

public function rules() {
    return [
        [['name'], 'unique', 'targetAttribute' => ['name', 'version']],
    ];
}

Septima

Answer 6

Based on the function above, here is one function you can add onto your ActiveRecord Model

You would use it like so,

array( array('productname,productversion'), 'ValidateUniqueColumns', 'Product already contains that version'),


/*
 * Validates the uniqueness of the attributes, multiple attributes
 */
public function ValidateUniqueColumns($attributes, $params)
{
    $columns = explode(",", $attributes);
    //Create the SQL Statement
    $select_criteria = "";
    $column_count = count($columns);
    $lastcolumn = "";

    for($index=0; $index<$column_count; $index++)
    {
        $lastcolumn = $columns[$index];
        $value = Yii::app()->db->quoteValue( $this->getAttribute($columns[$index]) );
        $column_equals = "`".$columns[$index]."` = ".$value."";
        $select_criteria = $select_criteria.$column_equals;
        $select_criteria = $select_criteria."  ";
        if($index + 1 < $column_count)
        {
            $select_criteria = $select_criteria." AND ";
        }
    }

    $select_criteria = $select_criteria." AND `".$this->getTableSchema()->primaryKey."` <> ".Yii::app()->db->quoteValue($this->getAttribute( $this->getTableSchema()->primaryKey ))."";

    $SQL = " SELECT COUNT(`".$this->getTableSchema()->primaryKey."`) AS COUNT_ FROM `".$this->tableName()."` WHERE ".$select_criteria;

    $list = Yii::app()->db->createCommand($SQL)->queryAll();
    $total = intval( $list[0]["COUNT_"] );

    if($total > 0)
    {
        $this->addError($lastcolumn, $params[0]);
        return false;
    }

    return true;
}

Wonted

Answer 7

It's very easy. In your array, include a param created in your extensions class.

Next code is inside Model.

array('name', 'ext.ValidateNames', 'with'=>'lastname')

Next code is from class ValidateNames into the extensions folder.

class ValidateNames extends CValidator
{
    public $with=""; /*my parameter*/

    public function validateAttribute($object, $attribute)
    {
        $temp = $this->with;  
        $lastname = $object->$temp;
        $name = $object->$attribute;
        $this->addError($object,$attribute, $usuario." hola ".$lastname);
    }
}

Issykkul

Your answer does not contain any information that is not already provided by at least one of the other answers. – Calaverite

Answer 8

May be you can add this rules onto your code

return array(
    array('name', 'unique', 'className'=>'MyModel', 'attributeName'=>'myName'),
    array('version', 'unique', 'className'=>'MyModel', 'attributeName'=>'myVersion')
);

Spadix

This will only validate that name is unique and version is unique alone. it does not validate the pair of columns (name, version). – Perice