How to use INSERT IGNORE in ActiveRecord?
How to use INSERT IGNORE in ActiveRecord?
I use such method:
try {
$model->save();
} catch(CDbException $e) {}
In this case you should make sure your data is valid and the exception occuring is only cause of constraint violation.
yea, and when you need to add to data base lot of duplicated or UNIQUE index breaking items -> will have time penalty?
is it only solution run by SQL?
Yes, I know this is a really old thread. But I found it when searching for the same answer, so I will post here the solution I developed. I think this is better than simply catching the error because it specifically tests the database for the existence of a duplicate row, then decides whether or not to proceed with saving the new record.
Here’s a simplified version of my code. Yes, it can be made more elegant in several ways, but this will illustrate the concept. Add this function to the model:
public function beforeSave()
{
$exists = Yii::app()->db->createCommand('SELECT IF (EXISTS (SELECT * FROM myTable WHERE col1 = ' . $this->col1 . ' AND col2 = ' . $this->col2 . '), true, false)')->queryScalar();
if($exists) {
// abort save because there's already a row there with this col1 value and col2 value
return false;
}
return true;
}
Of course col1 and col2 are the names of the columns in myTable that make up the UNIQUE KEY. You can have as many or as few as you like. If your columns contain character or string data, be sure to add quotes around the model values, like this:
$exists = Yii::app()->db->createCommand('SELECT IF (EXISTS (SELECT * FROM myTable WHERE col1 = "' . $this->col1 . '" AND col2 = "' . $this->col2 . '"), true, false)')->queryScalar();
I hope this helps someone.
if you have proper validators (especially one for this UNIQUE index) then it will act the same, but will also set errors for object so you can check what went wrong after save() returns false.
Interesting. It didn’t occur to me to use the model’s validation rules. Sounds like an even better idea!