Issues

ZF-4414: if there are more than one foreign keys corresponding to the same parent table in dependent table class, cascade delete doesn't work properly

Description

I happend to have a child table with 2 foreign keys corresponding to the same primary field in parent table.

table recipes -> primary key: r_id table recipe_relevance > fields : rr_r_id (FK coresponding to recipes.r_id) and rr_relevant_recipe_id (FK corresponding to recipes.r_id) and other fields

when I delete a row from recipes it only deletes from recipe_relevance where rr_r_id = r_id and NOT where rr_relevant_recipe_id, ALSO.


class Table_Recipes extends Zend_Db_Table {
...
$this->_dependentTables = array(..., 'Table_RecipeRelevance',...)
}

neither of this implementations works: 1.


class Table_RecipeRelevance extends Zend_Db_Table {
    
    protected $_primary;
    private $_adapter;
    private $_nameColumn;
    
    protected function _setupTableName() {
        $this->_primary = 'rr_id';
        $this->_name = 'recipe_relevance';
        $this->_referenceMap = array ('Recipe' => 
        array ('columns' => array ('rr_r_id', 'rr_relevant_recipe_id'),
         'refTableClass' => 'Table_Recipes',
         'refColumns' => array ('r_id', 'r_id'),
         'onDelete' => self::CASCADE,
         'onUpdate' => self::CASCADE ),
        );
        parent::_setupTableName ();
    }
//DOESN'T WORK BECAUSE IT BUILDS A WHERE ARRAY WHICH IS CONVERTED INTO SQL EXPR WITH "AND" BETWEEN CONDITIONS << AND THIS IS NOT WHAT I WANT
.....
}

2.1.


class Table_RecipeRelevance extends Zend_Db_Table {
    
    protected $_primary;
    private $_adapter;
    private $_nameColumn;
    
    
    
    protected function _setupTableName() {
        $this->_primary = 'rr_id';
        $this->_name = 'recipe_relevance';
        $this->_referenceMap = array ('Recipe' => 
        array ('columns' => array ('rr_r_id'),
         'refTableClass' => 'Table_Recipes',
         'refColumns' => array ('r_id'),
         'onDelete' => self::CASCADE,
         'onUpdate' => self::CASCADE ),
'RelevantRecipe' => 
        array ('columns' => array ('rr_relevant_recipe_id'),
         'refTableClass' => 'Table_Recipes',
         'refColumns' => array ( 'r_id'),
         'onDelete' => self::CASCADE,
         'onUpdate' => self::CASCADE ),
        );
        parent::_setupTableName ();
    }
.....
} // DOESN'T WORK BECAUSE OF REFERENCE MAP NORMALIZATION

}

For the first implementation I found a solution by rewriting the zend_db_table_abstract class function _cascadeDelete() as follows:


public function _cascadeDelete($parentTableClassname, array $primaryKey)
    {
        $rowsAffected = 0;
        foreach ($this->_getReferenceMapNormalized() as $map) {
            if ($map[self::REF_TABLE_CLASS] == $parentTableClassname && isset($map[self::ON_DELETE])) {
                switch ($map[self::ON_DELETE]) {
                    case self::CASCADE:
                        for ($i = 0; $i < count($map[self::COLUMNS]); ++$i) {
                            $col = $this->_db->foldCase($map[self::COLUMNS][$i]);
                            $refCol = $this->_db->foldCase($map[self::REF_COLUMNS][$i]);
                            $type = $this->_metadata[$col]['DATA_TYPE'];
                            $where[] = $this->_db->quoteInto(
                                $this->_db->quoteIdentifier($col, true) . ' = ?',
                                $primaryKey[$refCol], $type);
                            $rowsAffected += $this->delete($where); //<<<<<<

Comments

No comments to display