有什么优雅的解决方案如何插入多对多表？

Question

我得到了这些表：

• 标签 - 包含标签名称和 id（Tag_ID，标签）
• TagReview - 标签和评论之间的链接表（Review_ID，Tag_ID）
• 评论 - 保存评论。 （Review_ID，内容，...）

目前，我在添加或编辑评论时进行了插入。 在我的 Tag 表类中，扩展自 Zend_DB_Table... 插入在某些情况下有效，但随后失败并出现 SQL 错误“ SQLSTATE[23000]：完整性约束违规：1452”

    public function insertTags($reviewId, $tagList) {
    $reviewTag = new Application_Model_DbTable_ReviewTag;
    $tags = explode(self::SEPERATE, $tagList);

    foreach ($tags as $tag) {
        $tag = trim($tag);
        $tagRow = $this->fetchRow(array('tag = ?' => $tag));
        if ($tagRow == null) {
            $tagId = $this->insert(array(
                'tag' => trim($tag)
            ));
            $reviewTag->insert(array(
                'Tag_ID'        => $tagId,
                'Review_ID'     => $reviewId,       
            ));
        }

    }
}

Answer 1

所以问题是：

"SQLSTATE[23000]: Integrity constraint violation: 1452"

如果我必须在没有堆栈跟踪的情况下进行猜测，我会说以下行是问题所在：

$tagId = $this->insert(array(
    'tag' => trim($tag)
));

我假设您对标签表有一个唯一的约束，以确保不会将重复的标签添加到表中。

问题是这一行不应该是插入，它应该是 getTagIDByTag，如果失败，则插入。
所以，
<代码>

public function insertTags($reviewId, $tagList)
{
    $reviewTag = new Application_Model_DbTable_ReviewTag;
    $tags = explode(self::SEPERATE, $tagList);

    foreach($tags as $tag)
    {
        $tag = trim($tag);
        $tagRow = $this->fetchRow(array('tag = ?' => $tag));
        if($tagRow == null)
        {
            // FIRST TRY TO GET THE TAG
            $tagId = $db->fetchCol('SELECT Tag_ID FROM Tag WHERE tag = ?', trim($tag));

            // ZEND RETURNS NULL IF THE QUERY DOESN'T RETURN ANYTHING
            if(is_null($tagId))
            {
                // CREATE THE TAG
                $tagId = $this->insert(array(
                    'tag' => trim($tag)
                ));
            }

            $reviewTag->insert(array(
                'Tag_ID'        => $tagId, // USE THE TAG ID LIKE NORMAL
                'Review_ID'     => $reviewId,       
            ));
        }
    }
}

<代码>

Answer 2

我已经通过这段代码解决了我的解决方案。如果在编辑时删除或添加了标签，还可以处理问题。

/**
 * Insert the tags.
 * @param reviewId int review which the tags belongs.
 * @param tagList string tags with seperated coma or space.
 */
public function insertTags($reviewId, $tagList) {
    // The join table to solve many-to-many relation
    $reviewTag = new Review_Model_DbTable_ReviewTag;
    $tags = explode(self::SEPERATE, $tagList);

    // Go through all the tags
    foreach ($tags as $tag) {
        $tag = trim($tag);

        // Check if already in Tag table
        $tagRow = $this->fetchRow(array('tag = ?' => $tag));
        if ($tagRow == null) {
            // It's new tag create new tag
            $tagId = $this->insert(array(
                'tag'               => trim($tag)
            ));
            // Add the the id's to join table
            $reviewTag->insert(array(
                'Tag_ID'            => $tagId,
                'Review_ID'         => $reviewId,       
            ));
        } else {
            // Tag is already in database use the id and check the uniquness
            $unique = $reviewTag->fetchRow(array(
                'Review_ID = ?'     => $reviewId, 
                'Tag_ID = ?'        => $tagRow->Tag_ID
            ));
            if ($unique == null) {
                $reviewTag->insert(array(
                    'Tag_ID'        => $tagRow->Tag_ID,
                    'Review_ID'     => $reviewId,                       
                ));
            }

        }
    }
    $this->deleteTags($tags, $this->getOnlyTags($reviewId), $reviewId);
}

/**
 * Delete tags from table which are listed in $tags array.
 * @param mixed $tags array
 * @param mixed $userInserted array
 * @param int $reviewId 
 */
public function deleteTags($tags, $userInserted, $reviewId) {
    $diffTags = array_diff($tags, $userInserted);
    $reviewTag = new Review_Model_DbTable_ReviewTag;

    foreach ($diffTags as $tag) {
        $tagId = $this->fetchRow(array('tag = ?' => $tag))->Tag_ID;
        $reviewTag->delete(array(
            'Review_ID = ?' => $reviewId,
            'Tag_ID = ?'    => $tagId,
        ));
    }
}
/**
 * Get the tags names related to review.
 * @param reviewId int review id
 * @return array name of the tags as string
 */
public function getOnlyTags($reviewId) {
    $tags = array();
    $reviewTags = $this->fetchTags($reviewId);
    foreach ($reviewTags as $reviewTag) {
        $tags[] = $reviewTag->tag;
    }
    return $tags;
}