composite unique key validation

Yii 1.1 General Discussion
21

Hi.

First of all, thanks for this extension. I’ve started using it and I found something that maybe can be useful.

I’ve need to validate an unique restriction with null values. For example:

Id Value1 Value2

#1 A B (not valid)

#2 A C

#3 A B (not valid)

#4 B A

#5 B null (not valid)

#6 B null (not valid)

To support this feature I’ve modified the extension code as follow:

In the function validateCompositeUniqueKeys I’ve changed




$criteria = new CDbCriteria();

foreach ($uk['attributes'] as $attr) {

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

}

for




$criteria = new CDbCriteria();

foreach ($uk['attributes'] as $attr) {

  if ($object->$attr === null)

    $criteria->addCondition($attr.' is null');

  else

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

}
22

thank you for sharing, I’ve updated the extension

btw, validation behavior may also look strange when validating composite keys with empty strings (see CDbConnection::nullConversion and CDbCriteria::compare)

23

Watch out, that won’t work without correcting the camel case: [font=“Lucida Console”]$this->_oldattributes[/font] should be [font=“Lucida Console”]$this->_oldAttributes[/font]

24

I find that the built in CUniqueValidator is more than enough to handle this task. It is very flexible and you can even use relations to verify the uniqueness of an attribute. The default error message is appropriate but you can customize it to taste.

For example, an application can have many users and each user can have their own invoice numbers. However, you want the users not to use duplicate invoice numbers from their own account. In this example the unique composite key would consist of the "invoice_number" attribute and the "user_id" attribute through the relation "account". (You could of course have the user_id attribute in the invoice model but I wanted to demonstrate the use of relations along with the use of validating unique composite keys.) Your rule in the invoice model could look like this:




/**

 * @return array validation rules for model attributes.

 */

public function rules()

{

	// NOTE: you should only define rules for those attributes that

	// will receive user inputs.

	return array(

                array('invoice_number', 'unique', 'caseSensitive'=>false, 'criteria'=>array(

                                'condition'=>'account.user_id = :id',

				'params'=>array(':id'=>Yii::app()->user->id),

				'with'=>'account',

			),

			'message'=>'{attribute} "{value}" is already in use.'),

	);

}