I got these tables:
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,
));
}
}
}
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;
}