Any elegant solution how to make insertion to many to many table?

Question

I got these tables:

Tag - contains the tag names and id (Tag_ID, tag)
TagReview - linking table between tags and reviews (Review_ID, Tag_ID)
Review - holds reviews. (Review_ID, content, ...)

Currently i have made an insertions when review is added or edited. In my Tag table class extends from Zend_DB_Table... Insertion worked for some cases but then failed with sql error " SQLSTATE[23000]: Integrity constraint violation: 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,       
            ));
        }

    }
}

Ddi you get the violation inserting the tag or the reviewTag? — prodigitalson, Jul 12 '11 at 12:45

score 0 · Answer 1 · answered Aug 18 '11 at 20:36

so the problem is a:

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

If i had to take a guess without a stack trace, I would say the following line is the problem:

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

I assume you have a unique constraint on your tag table to make sure duplicate tags aren't added to the table.

The issue is that this line shouldn't be a insert, it should be a getTagIDByTag and failing that, insert. SO,

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,       
            ));
        }
    }
}

score 0 · Accepted Answer · answered Aug 19 '11 at 08:57

I have solved my solution by this code. Also handles the problem if on edit the tags has been removed or added.

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

Any elegant solution how to make insertion to many to many table?

2 Answers2