composite unique key validation

Hi,

it seems it’s not possible with built-in validators, how I should implement this feature in the model?

Look at this topic: http://www.yiiframework.com/forum/index.php?/topic/2635-how-to-validate-composite-unique-key

Here qiang says it will be probably supported in Yii 1.1, but I’m not sure if it’s already supported. I would do it using a method-based validator.

I didn’t find such a feature in 1.1

So, my solution

Validator




<?php


/**

 * CompositeUniqueKeyValidator class file.

 */

class CompositeUniqueKeyValidator extends CValidator {

    /**

     * @var string comma separated columns that are unique key

     */

    public $keyColumns;


    public $errorMessage = '"{columns_labels}" are not unique';


    /**

     * @var boolean whether the error message should be added to all of the columns

     */

    public $addErrorToAllColumns = false;


    /**

     * @param CModel $object the object being validated

     * @param string $attribute if there is an validation error then error message

     * will be added to this property

     */

    protected function validateAttribute($object, $attribute) {

        $class = get_class($object);

        Yii::import($class);


        $keyColumns = explode(',', $this->keyColumns);

        if (count($keyColumns) == 1) {

            throw new CException('CUniqueValidator should be used instead');

        }

        $columnsLabels = $object->attributeLabels();


        $criteria = new CDbCriteria();

        $keyColumnsLabels = array();

        foreach ($keyColumns as &$column) {

            $column = trim($column);

            $criteria->compare($column, $object->$column);

            $keyColumnsLabels[] = $columnsLabels[$column];

        }

        unset($column);

        $criteria->limit = 1;


        if ($class::model()->count($criteria)) {

            $message = Yii::t('yii', $this->errorMessage, array(

                '{columns_labels}' => join(', ', $keyColumnsLabels)

            ));

            if ($this->addErrorToAllColumns) {

                foreach ($keyColumns as $column) {

                    $this->addError($object, $column, $message);

                }

            }

            else {

                $this->addError($object, $attribute, $message);

            }

        }

    }


}


?>



Model




    public function rules() {

        return array(

            array('applicationId', 'CompositeUniqueKeyValidator', 'keyColumns' => 'user, applicationId'),

        );

    }



It looks good when we can attach error message to the certain property (when ‘addErrorToAllColumns’ is false) e.g. user must be unique within the application (composite unique key - user+applicationId)

I have this error




Parse error: syntax error, unexpected T_PAAMAYIM_NEKUDOTAYIM in /home/www/ed3a58441fae69684a5a8204d2f03575/web/centre10/protected/models/CompositeUniqueKeyValidator.php on line 44



This jewish error follows a typo, maybe you have forgot some brackets.

I usually have when I type:

MyModel::model->findAll()

Instead of

MyModel::model()->findAll()

The error line is




 if ($class::model()->count($criteria)) {



I have this error on remote computer.

My local machine run the script without error

I think the reason is PHP version. On your local machine you have 5.3 installed and some older version on remote computer.

Try this syntax:




CActiveRecord::model($class)->count($criteria)



Thank you very much… It’s working now!

This is the explanation from the PHP manual

So the error message should be read "unexpected double-colon …"

/Tommy

thanks gay!

thank you very much for this compositeUniqueKeyValidator class. I am highly appreciating your work. Superb Job It solved my problem. thank you again.

And I think It should be added to Yii next version.

I looked and it seems no one every submitted a ticket for it, so it fell through the cracks.

Done:

http://code.google.com/p/yii/issues/detail?id=2184

It works perfectly!

Thanks for the validator class ololo :)

Hey there,

This looks interesting, I was just searching to see if there was something ready-made.

ololo; Have you considered releasing this as a formal Yii extension? I looked in the repository and can only find http://www.yiiframework.com/extension/unique-multiple-columns-validate for the keyword “unique”, and it seems that one is not this one :) Yours seem a bit more updated.

Thanks,

Great bit of code, thanks for this!

I have one problem though - how do I prevent this validation running when updating an existing record and none of the composite keys have changed. It seems to be validating against itself, whereas I only want it to apply the composite validation if either or both of the composite key values have changed.

Hope this makes sense :)

its work well on insert, but also validate when update.

how do i ignore this validation while execute update?

Same for me tooo. How to skip validation on update.

In terms of handling existing records, there are 3 situations to consider:

  1. a new record

  2. an existing record, where none of the UK fields have changed

  3. an existting record, where one or more of the UK fields have changed

In the case of scenario 1) and 3), then we should verify there are zero rows in the database with the same UK fields

In the case of scenario 2), we can allow a single record to exist in the database, namely the previous version of this record.

The problem is that in order to distinguish scenario 2) from 3), you need to track the oldAttributes. I already have this in place in my application, for automatic changeLog creation:




class MyActiveRecord extends CActiveRecord {

	public $_oldAttributes = array();


	public function afterFind() {

    	$this->setOldAttributes($this->getAttributes());

	}


	public function getOldAttributes() {

    	return $this->_oldattributes;

	}


	public function setOldAttributes($value) {

    	$this->_oldattributes = $value;

	}

 }



All my models extend the above class.

Now, the modified code to make use of oldAttributes. Note that I renamed the class slightly and referenced this forum topic. changes from the above version are flagged with my initials ‘JJD’:




<?php


/**

 * CompositeUniqueKeyValidator class file.

 * from http://www.yiiframework.com/forum/index.php?/topic/15622-composite-unique-key-validation/

 */

class ECompositeUniqueKeyValidator extends CValidator {


	/**

 	* @var string comma separated columns that are unique key

 	*/

	public $keyColumns;

	public $errorMessage = '"{columns_labels}" are not unique';

	/**

 	* @var boolean whether the error message should be added to all of the columns

 	*/

	public $addErrorToAllColumns = false;


	/**

 	* @param CModel $object the object being validated

 	* @param string $attribute if there is an validation error then error message

 	* will be added to this property

 	*/

	protected function validateAttribute($object, $attribute) {


    	$class = get_class($object);

    	Yii::import($class);


    	$keyColumns = explode(',', $this->keyColumns);

    	if (count($keyColumns) == 1) {

        	throw new CException('CUniqueValidator should be used instead');

    	}

    	$columnsLabels = $object->attributeLabels();

    	$oldAttributes = $object->getOldAttributes();   // +JJD


    	$criteria = new CDbCriteria();

    	$keyColumnsLabels = array();

    	$changedKeyColumns = false;  // +JJD

    	foreach ($keyColumns as &$column) {

        	$column = trim($column);

        	$criteria->compare($column, $object->$column);

        	$keyColumnsLabels[] = $columnsLabels[$column];

        	if (!$changedKeyColumns && !$object->isNewRecord && ($oldAttributes[$column] != $object->$column)) {

            	$changedKeyColumns = true;  // +JJD

        	}

    	}

    	unset($column);


    	// handle existing records

    	//$criteria->limit = 1;

    	$allowed = ($object->isNewRecord || $changedKeyColumns ? 0 : 1);  // +JJD

    	//if ($class::model()->count($criteria)) {

    	$count = CActiveRecord::model($class)->count($criteria);  // +JJD

    	// +JJD  check whether we have too many rows in the database

    	if ($count > $allowed) {

        	$message = Yii::t('yii', $this->errorMessage, array('{columns_labels}' => join(', ', $keyColumnsLabels)));

        	if ($this->addErrorToAllColumns) {

            	foreach ($keyColumns as $column) {

                	$this->addError($object, $column, $message);

            	}

        	} else {

            	$this->addError($object, $attribute, $message);

        	}

    	}

	}


}


?>



I removed my last post with lots of code, and as I promised, I created the extension - http://www.yiiframework.com/extension/composite-unique-key-validatable/

Thanks