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 an object with Doctrine:

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

But if record exists with the same $pubId and $tagId new record will be insertet with new PK.

How to do INSERT IGNORE into table with symfony?

$sbTagsArticles->isNew();

returns 1.

Thnx.

Solution 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 should be ensuring that the same record doesnt exist on the application side not the SQL side. If you dont ever want the same article/tag combo to exist then add a unique index to (article_id, tag_id). That should generate a mysql error which will in turn generate a doctrine exception that you can catch. There isnt an ignore flag for saves... You might be able to use one operating at a lower level of the DBAL (Doctrine_Query, Doctrine_Connection, etc..) but not directl from the ORM layer.

Doctrine_Record::isNew() will always return true if you have instantiated record asopposed to pulling it from the db otherwise it has way it has no way to know that the record is/isnt new.

Also why are you using the MyISAM storage engine? Im pretty sure this will actually result in more overhead when using Doctrine since it then needs to emulate constraints on the php side. Normally your schema would 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

Solution 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
     */
}