CakePHP ExtendAssociations - HABTM Update by HABTM primary key

Published on Wednesday, December 16th 2009 at 20:35
Last updated on Monday, February 13th 2017 at 22:47

For a few years now I have been using Brandon Parise’s great CakePHP behaviour, ExtendAssociations, which I originally found here at the Bakery. Lately I have been developing an app that has a HABTM relationship that has extra data stored in the relationship, and also can have multiple entries for the same association.
To explain further, I have a Sale model, that can be associated to multiple Items. each association stores the sale price and the quantity of the item. Also, say for example a Sale has 4 of Item X associated to it at £99, this cound be added to at a later date with 2 Item X at £130 for example. Since each HABTM association cannot be identified uniquely by the tuple of sale_id and item_id, the HABTM table must have a primary key field for itself.

The original ExtendAssociations update action did not provide an update action. I provided one a while ago here, but this is not sufficient for the scenario mentioned above, so here is another action which can update an HABTM association using the HABTM join table primary key as a reference:

function habtmUpdatePrimary(&$model, $assoc, $ids = array(), $extra = array() ) {
        if(!is_array($ids)) {
            $extra = array($extra);
            $ids = array($ids);
        }

    // make sure the association exists
    if(isset($model-&gt;hasAndBelongsToMany[$assoc])) {
        // get association data
        $joinTable = $model-&gt;hasAndBelongsToMany[$assoc]['joinTable'];
        $associationForeignKey = $model-&gt;hasAndBelongsToMany[$assoc]['associationForeignKey'];
        $foreignKey = $model-&gt;hasAndBelongsToMany[$assoc]['foreignKey'];

        $joinClass = $model-&gt;hasAndBelongsToMany[$assoc]['with'];   // Thanks to CrashHD for this change
        $joinTablePrimaryKey = $model-&gt;$joinClass-&gt;primaryKey;

        if( !empty($joinTable) &amp;&amp; !empty($joinTablePrimaryKey) ) {

            $success = true;
            foreach($ids as  $index =&gt; $id)
            {
                // execute SQL query for each $id
                $sql = array();
                foreach( $extra[$index] as $key =&gt; $value ) {
                    if ($value != null) {
                        $sql[] = $key . " = '". addslashes($value) . "'";
                    } else {
                        $sql[] = $key . " = NULL";
                      }
                  }
                $result = $model-&gt;query( "UPDATE `$model-&gt;tablePrefix.$joinTable` set ".implode( "," , $sql )." WHERE $joinTablePrimaryKey = $id");
                if (!$result) $success = false;
            }
            return $success;
        } else {
            // invalid join table name or primary key field name
            return false;
        }
    } else {
        // association doesn't exist, return false
        return false;
    }
}</pre>

Here is the associated action to delete a HABTM by join table primary key:

function habtmDeletePrimary(&$model, $assoc, $ids) {
if(!is_array($ids))
$ids = array($ids);

// make sure the association exists
if(isset($model->hasAndBelongsToMany[$assoc])) {

//get extra data
$joinTable = $model->hasAndBelongsToMany[$assoc][‘joinTable’];
$foreignKey = $model->hasAndBelongsToMany[$assoc][‘foreignKey’];
$associationForeignKey = $model->hasAndBelongsToMany[$assoc][‘associationForeignKey’];

$joinClass = $model->hasAndBelongsToMany[$assoc][‘with’]; // Thanks to CrashHD for this change
$joinTablePrimaryKey = $model->$joinClass->primaryKey;

$table = (!empty($model->tablePrefix)) ? “$model->tablePrefix.$joinTable“ : “$joinTable“;

//SANITY CHECKS, thanks CrashHD
if (!$joinTable || !$foreignKey || !$associationForeignKey || !$joinClass || !$joinTablePrimaryKey || !$table) return FALSE;

$sql = “DELETE FROM $table WHERE “;
$first = true;
foreach($ids as $id) {
$sql .= ($first?””:” OR “) . “$joinTablePrimaryKey = ‘$id’”;
$first = false;
}

// execute SQL
$success = $model->query($sql);
return $success;
}

// association doesn’t exist, return false
return false;
}
Here is the full code for the version of ExtendAssociations that I currently use - any comments / criticisms / improvements welcome. I would again like to thank Brandon Parise for graciously publishing the original Behaviour, nice work!

/**

Extend Associations Behavior
Extends some basic add/delete function to the HABTM relationship
in CakePHP. Also includes an unbindAll($exceptions=array()) for
unbinding ALL associations on the fly.
*
Now also adds / updates extra data (ie fields in the HABTM join table other than the keys),
as well as handling HABTMs that have multiple entries for the same association
and their own primary key.
*
This code is loosely based on the concepts from:
http://rossoft.wordpress.com/2006/08/23/working-with-habtm-associations/
*
@author Brandon Parise <brandon@parisemedia.com>, updated and extended by Scott Donnelly <scott@donnel.ly>
@package CakePHP Behaviors
/
class ExtendAssociationsBehavior extends ModelBehavior {
/**
Model-specific settings
@var array
*/
var $settings = array();

/**

Setup
Noething sp
*
@param unknown_type $model
@param unknown_type $settings
*/
function setup(&$model, $settings = array()) {
// no special setup required
$this->settings[$model->name] = $settings;
}

/**

Add an HABTM association
*
@param Model $model
@param string $assoc
@param int $id
@param mixed $assoc_ids
@return boolean
*/
function habtmAdd(&$model, $assoc, $id, $assoc_ids , $extra = array() ) {
if(!is_array($assoc_ids)) {
$assoc_ids = array($assoc_ids);
$extra = array($extra);
}

// make sure the association exists
if(isset($model->hasAndBelongsToMany[$assoc])) {

// get association data
$joinTable = $model->hasAndBelongsToMany[$assoc][‘joinTable’];
$associationForeignKey = $model->hasAndBelongsToMany[$assoc][‘associationForeignKey’];
$foreignKey = $model->hasAndBelongsToMany[$assoc][‘foreignKey’];

$success = true;
$n = 0;

if( !empty( $joinTable ) ) {
foreach($assoc_ids as $assoc_id)
{
// execute SQL query for each $assoc_id
$values = array(“‘“ . $id . “‘“, “‘“ . $assoc_id . “‘“);
$cols = array(“" . $foreignKey . "“, “" . $associationForeignKey . "“);
foreach( $extra[$n] as $key => $value )
{
array_push($cols, ‘' . $key . '‘);
array_push($values, “‘“ . addslashes($value) . “‘“);
}

$n++;
$result = $model->query( “INSERT INTO $model->tablePrefix.$joinTable (“ . implode( “, “, $cols) . “) VALUES (“ . implode( “,” , $values ) . “)”);
if (!$result) $success = false;
}
} else {
return false;
}
return $success;
}

// association doesn’t exist, return false
return false;
}

/**

Delete an HABTM Association
*
@param Model $model
@param string $assoc
@param int $id
@param mixed $assoc_ids
@return boolean
*/
function habtmDelete(&$model, $assoc, $id, $assoc_ids) {
if(!is_array($assoc_ids))
$assoc_ids = array($assoc_ids);

// make sure the association exists
if(isset($model->hasAndBelongsToMany[$assoc])) {

//build SQL query
$sql = “DELETE FROM $model->tablePrefix.$joinTable WHERE $foreignKey = ‘$id’”;
if($assoc_ids[0] != ‘*’) {
$sql .= “ AND (“;
$first = true;
foreach($assoc_ids as $assoc_id)
{
$sql .= ($first?””:” OR “) . “$associationForeignKey = ‘$assoc_id’”;
$first = false;
}
$sql .= “)”;
}

// execute SQL
$success = $model->query($sql);
return $success;
}

// association doesn’t exist, return false
return false;
}

/**

Delete an HABTM Association, specified by primary key id, rather than foreignkey / associationforeignkey
*
@param Model $model
@param string $assoc
@param mixed $ids
@return boolean
*/
function habtmDeletePrimary(&$model, $assoc, $ids) {
if(!is_array($ids))
$ids = array($ids);

// make sure the association exists
if(isset($model->hasAndBelongsToMany[$assoc])) {

$joinClass = array($model->name, $assoc);
sort($joinClass);
$joinClass = Inflector::pluralize($joinClass[0]) . $joinClass[1];
$joinTablePrimaryKey = $model->$joinClass->primaryKey;

$sql = “DELETE FROM $model->tablePrefix.$joinTable WHERE “;
$first = true;
foreach($ids as $id)
{
$sql .= ($first?””:” OR “) . “$joinTablePrimaryKey = ‘$id’”;
$first = false;
}

// execute SQL
$success = $model->query($sql);
return $success;
}

// association doesn’t exist, return false
return false;
}

/**

update HABTM Associations, including extra data, referenced by joined models primary keys
*
@param Model $model
@param string $assoc
@param int $id
@param int $assoc_ids
@param mixed $extra
@return boolean
*/
function habtmUpdate(&$model, $assoc, $id, $assoc_ids, $extra = array() ) {
if(!is_array($assoc_ids)) {
$assoc_ids = array($assoc_ids);
$extra = array($extra);
}

// make sure the association exists
if(isset($model->hasAndBelongsToMany[$assoc])) {

if( !empty( $joinTable ) ) {

$success = true;
foreach($assoc_ids as $index => $assoc_id)
{
// build query for each $assoc_id
$sql = array();
foreach( $extra[$index] as $key => $value ) {
if ($value != null) {
$sql[] = $key . “ = ‘“. addslashes($value) . “‘“;
} else {
$sql[] = $key . “ = NULL”;
}
}
// update each association with the new values
$result = $model->query( “UPDATE $model->tablePrefix.$joinTable set “.implode( “,” , $sql ).” WHERE $foreignKey = $id AND $associationForeignKey = ‘$assoc_id’”);
if (!$result) $success = false;
}

return $success;
} else {
// join table not specified, return false
return false;
}
} else {
// association doesn’t exist, return false
return false;
}
}

/**

retrieve HABTM Associations, accessed by join table primary key
*
@param Model $model
@param string $assoc
@param int $id
@return boolean
*/
function habtmFetch(&$model, $assoc, $ids) {
if(!is_array($ids))
$ids = array($ids);

// make sure the association exists
if(isset($model->hasAndBelongsToMany[$assoc])) {

$joinClass = array($model->name, $assoc);
sort($joinClass);
$joinClass = Inflector::pluralize($joinClass[0]) . $joinClass[1];
$joinTablePrimaryKey = $model->$joinClass->primaryKey;

$n = 0;
if( !empty( $joinTable ) )
{
$whereClause = “(“;
foreach($ids as $id)
{
// craft where clause
if ($n > 0) $whereClause .= “ OR “;
$whereClause .= “$joinTablePrimaryKey = $id”;
$n++;
}
$whereClause .=”)”;

$res = $model->query( “SELECT * FROM $model->tablePrefix.$joinTable WHERE $whereClause”);
}
return $res;
}
// association doesn’t exist, return false
return false;
}

/**

Update HABTM Associations, accessed by join table primary key
*
@param Model $model
@param string $assoc
@param int $id
@param mixed $extra
@return boolean
*/
function habtmUpdatePrimary(&$model, $assoc, $ids = array(), $extra = array() ) {
if(!is_array($ids)) {
$extra = array($extra);
$ids = array($ids);
}

// make sure the association exists
if(isset($model->hasAndBelongsToMany[$assoc])) {
// get association data
$joinTable = $model->hasAndBelongsToMany[$assoc][‘joinTable’];
$associationForeignKey = $model->hasAndBelongsToMany[$assoc][‘associationForeignKey’];
$foreignKey = $model->hasAndBelongsToMany[$assoc][‘foreignKey’];

$joinClass = array($model->name, $assoc);
sort($joinClass);
$joinClass = Inflector::pluralize($joinClass[0]) . $joinClass[1];
$joinTablePrimaryKey = $model->$joinClass->primaryKey;

if( !empty($joinTable) && !empty($joinTablePrimaryKey) ) {

$success = true;
foreach($ids as $index => $id)
{
// execute SQL query for each $id
$sql = array();
foreach( $extra[$index] as $key => $value ) {
if ($value != null) {
$sql[] = $key . “ = ‘“. addslashes($value) . “‘“;
} else {
$sql[] = $key . “ = NULL”;
}
}
$result = $model->query( “UPDATE $model->tablePrefix.$joinTable set “.implode( “,” , $sql ).” WHERE $joinTablePrimaryKey = $id”);
if (!$result) $success = false;
}
return $success;
} else {
// invalid join table name or primary key field name
return false;
}
} else {
// association doesn’t exist, return false
return false;
}
}

/**

Delete All HABTM Associations
Just a nicer way to do easily delete all.
*
@param Model $model
@param string $assoc
@param int $id
@return boolean
/
function habtmDeleteAll(&$model, $assoc, $id) {
return $this->habtmDelete($model, $assoc, $id, ‘‘);
}

/**

Find
This method allows cake to do the dirty work to
fetch the current HABTM association.
*
@param Model $model
@param string $assoc
@param int $id
@return array
*/
function __habtmFind(&$model, $assoc, $id) {
// temp holder for model-sensitive params
$tmp_recursive = $model->recursive;
$tmp_cacheQueries = $model->cacheQueries;

$model->recursive = 1;
$model->cacheQueries = false;

// unbind all models except the habtm association
$this->unbindAll($model, array(‘hasAndBelongsToMany’ => array($assoc)));
$data = $model->find(array($model->name.’.’.$model->primaryKey => $id));

$model->recursive = $tmp_recursive;
$model->cacheQueries = $tmp_cacheQueries;

if(!empty($data)) {
// use Set::extract to extract the id’s ONLY of the $assoc
$data[$assoc] = array($assoc => Set::extract($data, $assoc.’.{n}.’.$model->primaryKey));
}

return $data;
}

/**

UnbindAll with Exceptions
Allows you to quickly unbindAll of a model’s
associations with the exception of param 2.
*
Usage:
$this->Model->unbindAll(); // unbinds ALL
$this->Model->unbindAll(array(‘hasMany’ => array(‘Model2’)) // unbind All except hasMany-Model2
*
@param Model $model
@param array $exceptions
*/
function unbindAll(&$model, $exceptions = array()) {
$unbind = array();
foreach($model->__associations as $type) {
foreach($model->{$type} as $assoc=>$assocData) {
// if the assoc is NOT in the exceptions list then
// add it to the list of models to be unbound.
if(@!in_array($assoc, $exceptions[$type])) {
$unbind[$type][] = $assoc;
}
}
}
// if we actually have models to unbind
if(count($unbind) > 0) {
$model->unbindModel($unbind);
}
}
}
$model->tablePrefix.