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.

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 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

Answer 2

You could extend the SobTagsArticles object with a new save method, and check if the record already 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();
  }
}

This way the object will be saved only if it doesn't exist.

You could also set an unique index to your table like so:

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

Your schema would 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