Scott Donnelly's Blog

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

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

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])) {

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

//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])) {

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

$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])) {

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

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])) {

// 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;

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

Popularity: 48% [?]