The Bakery Everything CakePHP

This article offers proposed code to be added either to the AppModel or Model level which can be used for establishing join records between two models in a simple, lean fashion for applications that find themselves limited by Model::save.

First, a little background to justify the need; code will follow, ending with a tutorial on page 2:

For hasAndBelongsToMany associations, CakePHP has built into Model::save the functionality to store the appropriate join records. However, there are two limitations to the Model::save methods.

First, Model::save requires that all associatedForeignKeys be passed to the model. This lends itself very well to using multiple select tags; however, thse are sometimes impractical when the number of joins or even the number of possible joins between two models is very large. The view elements themselves become quite daunting to the user. Also, the controller has to query the preexisting joins to pre-populate the selectTags or in the very least, provide Model::save with all the necessary data.

The second limitation is that Model::_saveMulti, which is used by Model::save to store these joins, works by deleting all join records and reinserting the join records for each element found in $this->data['assocModel']['assocModel']. Again, when the number of joins between two models is very large, a large number of INSERTs are executed on the database unnecessarily.

There are alternatives like RosSoft's method in ticket #1348 which take care of the first limitation with using multiple select tags; however, this method still pulls down all the preexisting joins to pass to Model::save with the insert, and the second limitation is still a problem.

One-at-a-time inserts and deletes become necessary for some applications requiring non-array based view elements and/or for applications requiring better performance from the database. The following code proposal is my attempt at rewriting RosSoft's code. The only major difference in functionality is that $id is now optional, just like in Model::read() if the developer chooses to first store the id into Model->id. In fact, the code for this was taken directly from Model::read. The rest of the code is largely based on Model::_saveMulti and I have done my best to mimic the coding style and conventions.

This code could be placed into app_model by users who find themselves here or could (hopefully) be included, probably in some modified fashion, in the core model class as an enhancement (I'll keep my fingers crossed).

Model Class:

Download code <?php 
    /**
     * Adds a join record between two records of a hasAndBelongsToMany association
     *
     * @param mixed $assoc The name of the HABTM association
     * @param mixed $assoc_ids The associated id or an array of associated ids
     * @param integer $id The id of the record in this model
     * @return boolean Success
     */
    function addAssoc($assoc, $assoc_ids, $id = null)
    {
        if ($id != null) {
            $this->id = $id;
        }

        $id = $this->id;

        if (is_array($this->id)) {
            $id = $this->id[0];
        }
        
        if ($this->id !== null && $this->id !== false) {
            $db =& ConnectionManager::getDataSource($this->useDbConfig);
            
            $joinTable = $this->hasAndBelongsToMany[$assoc]['joinTable'];
            $table = $db->name($db->fullTableName($joinTable));
            
            $keys[] = $this->hasAndBelongsToMany[$assoc]['foreignKey'];
            $keys[] = $this->hasAndBelongsToMany[$assoc]['associationForeignKey'];
            $fields = join(',', $keys);
            
            if(!is_array($assoc_ids)) {
                $assoc_ids = array($assoc_ids);
            }
        
            // to prevent duplicates
            $this->deleteAssoc($assoc,$assoc_ids,$id);
            
            foreach ($assoc_ids as $assoc_id) {
                $values[]  = $db->value($id, $this->getColumnType($this->primaryKey));
                $values[]  = $db->value($assoc_id);
                $values    = join(',', $values);
                
                $db->execute("INSERT INTO {$table} ({$fields}) VALUES ({$values})");
                unset ($values);
            }
            
            return true;
        } else {
            return false;
        }
    }

    /**
     * Deletes any join records between two records of a hasAndBelongsToMany association
     *
     * @param integer $id The id of the record in this model
     * @param mixed $assoc The name of the HABTM association
     * @param mixed $assoc_ids The associated id or an array of associated ids
     * @return boolean Success
     */
    function deleteAssoc($assoc, $assoc_ids, $id = null)
    {
        if ($id != null) {
            $this->id = $id;
        }

        $id = $this->id;

        if (is_array($this->id)) {
            $id = $this->id[0];
        }
        
        if ($this->id !== null && $this->id !== false) {
            $db =& ConnectionManager::getDataSource($this->useDbConfig);
            
            $joinTable = $this->hasAndBelongsToMany[$assoc]['joinTable'];    
            $table = $db->name($db->fullTableName($joinTable));
            
            $mainKey = $this->hasAndBelongsToMany[$assoc]['foreignKey'];
            $assocKey = $this->hasAndBelongsToMany[$assoc]['associationForeignKey'];
            
            if(!is_array($assoc_ids)) {
                $assoc_ids = array($assoc_ids);
            }
            
            foreach ($assoc_ids as $assoc_id) {
                $db->execute("DELETE FROM {$table} WHERE {$mainKey} = '{$id}' AND {$assocKey} = '{$assoc_id}'");
            }
            
            return true;
        } else {
            return false;
        }
    }
?>