在具有Symfony和Doctrine的数据库中使用INSERT时忽略重复项

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:

和使用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.

但是如果记录存在同一个$ pubId和$ tagId,则新记录将插入新PK。

How to do INSERT IGNORE into table with symfony?

如何使用symfony INSERT IGNORE进入表?

$sbTagsArticles->isNew();

returns 1.

Thnx.

3 个解决方案

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

您应该确保在应用程序端而不是SQL端存在相同的记录。如果您不希望存在相同的文章/标记组合,则向(article_id,tag_id)添加唯一索引。这应该会产生一个mysql错误,这个错误反过来会产生一个你可以捕获的学说异常。没有用于保存的忽略标志...您可以使用在DBAL的较低级别(Doctrine_Query,Doctrine_Connection等)运行的标志,但不能从ORM层直接运行。

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.

Doctrine_Record :: isNew()将始终返回true,如果您已经实例化了记录,并将其从数据库中拉出来,否则它无法知道该记录是不是新的。

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:

另外你为什么要使用MyISAM存储引擎?我很确定这会在使用Doctrine时产生更多的开销,因为它需要在php端模拟约束。通常,您的架构看起来像这样:

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

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

#3

You could extend the SobTagsArticles object with a new save method, and check if the record already exists:

您可以使用新的保存方法扩展SobTagsArticles对象,并检查该记录是否已存在:

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