phpzend-frameworkzend-db-table

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


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

    }
}

Solution

  • 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;
    }