[Solved] Adding MANY_MANY relation - ignoring Duplicate Key

Archives Yii 1.0.x Discussions
1

Hi all

I have a script which adds a MANY_MANY relation to a table when a user chooses to mark an object as belonging to a group.  At the moment I simply do this in my controller:



$model = new GroupObject();


$model->groupId = $_POST['groupId'];


$model->objectId = $_POST['objectId'];


$model->save();

This works fine, until a user adds an object and group that are already linked.  I then get the error message:

Integrity constraint violation: 1062 Duplicate entry '1-8' for key 'PRIMARY'

I know I can check for an existing relation in the table by running an SQL query before saving a new record, but this will be two SQL queries.  Is there any clever way I can 'try' the insert, and if it fails because of duplicates just treat it as successful?

2

I think maybe the exists method might be the best I can hope for (still two SQL queries though)



if (! GroupObject::model()->exists('groupId=? AND objectId=?',array($groupId, $objectId)))


{


  $model = new GroupObject();


  $model->groupId = $groupId;


  $model->objectId = $objectId;


  $model->save();


}
3

you can 'try' by using try catch



try {


 $model->save()


} catch (CDbException $e) {


  if ($e->code != 1067) {


    throw $e; //throw every other exception than duplicate key


  }


}
4

The best way to do this, assuming your using MySQL, is to use the INSERT IGNORE syntax which will ignore duplicates in associative enitities such as yours. One query, no fuss, no muss.

5

Excellent - thanks guys.