Ignore duplicates when using INSERT in a database with Symfony and Doctrine

Question

I have a table

CREATE TABLE `sob_tags_articles` ( `tag_id` int(11) NOT NULL, `article_id` int(11) NOT NULL, `id` int(11) NOT NULL auto_increment, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=112 

And triing to save the object using Doctrine:

 $sbTagsArticles = new SobTagsArticles(); $sbTagsArticles->article_id = $pubId; $sbTagsArticles->tag_id = $tagId; $sbTagsArticles->save(); 

But if an entry exists with the same $ pubId and $ tagId, a new entry will be inserted with the new PK.

How to make INSERT IGNORE to a table with symfony?

 $sbTagsArticles->isNew(); 

returns 1.

Thnx.

Answer 1

 try { $record->save(); } catch(Doctrine_Exception $e) { if($e->getErrorCode() !== $duplicateKeyCode) { /** * if its not the error code for a duplicate key * value then rethrow the exception */ throw $e; } /** * you might want to fetch the real record here instead * so yure working with the persisted copy */ } 

You must make sure that the same record does not exist on the application side, and not on the SQL side. If you never want the same article / tag combination to exist, add a unique index to (article_id, tag_id) . This should generate a mysql error, which in turn will throw a doctrine exception that you can catch. There is no ignoring the flag to save ... Perhaps you can use one that works at a lower DBAL level (Doctrine_Query, Doctrine_Connection, etc.), but not routed from the ORM level.

Doctrine_Record::isNew() will always return true if you created an instance record that was ready to pull it from db, otherwise it has a way to find out that record / is not new.

Also why do you use the MyISAM storage engine? I am sure that in fact this will lead to more overhead when using Doctrine, since then it needs to imitate the restrictions on the php side. Usually your circuit will look something like this:

 CREATE TABLE `sob_tags_articles` ( `tag_id` int(11) NOT NULL, `article_id` int(11) NOT NULL, `id` int(11) NOT NULL auto_increment, PRIMARY KEY (`id`), CONSTRAINT `some_unique_constraint_name_1` FOREIGN KEY `article_id` REFERENCES `article` (`id`) ON DELETE CASCADE, CONSTRAINT `some_unique_constraint_name_2` FOREIGN KEY `tag_id` REFERENCES `tag` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=112 

Answer 2

This is the actual code to be used.

 try { $record->save(); } catch(Doctrine_Connection_Exception $e) { if($e->getPortableCode() != Doctrine::ERR_ALREADY_EXISTS) { /** * if its not the error code for a duplicate key * value then rethrow the exception */ throw $e; } /** * you might want to fetch the real record here instead * so yure working with the persisted copy */ } 

Answer 3

You can extend the SobTagsArticles object with the new save method and check if the record exists:

 public function exists() { $q = Doctrine_Query::create() ->from('sobtagsarticles ta') ->where('ta.tag_id = ? and ta.article_id = ?', array($this->getTagId(), $this->getArticleId())); if (!$result = $q->execute()) { parent::save(); } } 

Thus, the object will be saved only if it does not exist.

You can also set a unique index to the table:

 UNIQUE INDEX `sb_tags_articles_unique` (`tag_id` ASC, `article_id` ASC) 

Your circuit will look like this:

 CREATE TABLE `sob_tags_articles` ( `tag_id` int(11) NOT NULL, `article_id` int(11) NOT NULL, `id` int(11) NOT NULL auto_increment, PRIMARY KEY (`id`), UNIQUE INDEX `sb_tags_articles_unique` (`tag_id` ASC, `article_id` ASC), CONSTRAINT `some_unique_constraint_name_1` FOREIGN KEY `article_id` REFERENCES `article` (`id`) ON DELETE CASCADE, CONSTRAINT `some_unique_constraint_name_2` FOREIGN KEY `tag_id` REFERENCES `tag` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=112