I had a scenario where I want to ensure that multiple values are unique in a table. Meaning I want to allow this:
|
|
+-----------------------------------+ |name tournamentid | |=========================== | | nathan gibbs 2 | | nathan gibbs 6 | | jeff tester 2 | | jeff tester 6 | +-----------------------------------+ |
But not this:
|
|
+-----------------------------------+ |name tournamentid | |=========================== | | nathan gibbs 2 | | nathan gibbs 2 | | jeff tester 6 | | jeff tester 6 | +-----------------------------------+ |
This is suprisingly easy to use when we implement unique composite keys. For an existing table:
ALTER TABLE TableName ADD UNIQUE KEY (KeyOne, KeyTwo, ...);
Now if a insert is attempted the following error is thrown:
Integrity constraint violation: 1062 Duplicate entry ‘Nathan Riley-4’ for key ‘name’
In yii, we trap this using a custom validator class in components/CompositeUniqueKeyValidator.php:
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);
}
}
}
}
?>
Then in our model’s rules():
public function rules()
{
return array(
array('name', 'CompositeUniqueKeyValidator', 'keyColumns' => 'name, tournamentId'),
);
}