I ran across this thread over the weekend when I was putting in the code to restrict deletes when there is an existing foreign key reference to an item instead of using the reset or cascade options that are provided in the builder (and after reading this thread, I thought others might find what I ended up with useful.)
One key thing is that when I set up the tables, I used the reset option so I had some code to modify in my item models and so I didn't have to remember all the foreign key references, i.e. the delete functions in my item models already had the calls to integrityReset which I could change.
In my case, I thought it would be easier to add an integrityRestrict function to the item class in admin\classes\jmodel.item.php so I could call it (instead of the reset function) wherever I needed to restrict the delete process.
The code that I added for the integrityRestrict looked like this:
/**
* Method to restrict deleting fk referenced items.
*
* @access public
* @param string $key The foreign key which relate to the cids.
* @param array &$cid The ids to be deleted in foreign table. (referenced ids are removed)
*
* @return boolean True on success
*/
public function integrityRestrict($key, &$cid = array())
{
if (count( $cid ))
{
$db = JFactory::getDBO();
$table = $this->getTable();
JArrayHelper::toInteger($cid);
$cids = implode( ',', $cid );
$query = 'SELECT '. $db->quoteName($key) . 'AS fkid FROM ' . $db->quoteName($table->getTableName())
. ' WHERE ' . $db->quoteName($key) . ' IN ( ' . $cids . ' ) GROUP BY ' . $db->quoteName($key);
$db->setQuery($query);
$list = $db->loadObjectList();
$cidReferenced = array();
if (count($list) > 0) {
foreach($list as $item)
$cidReferenced[] = $item->fkid;
$cid = array_diff($cid, $cidReferenced);
JError::raiseWarning(500, JText::plural('VOLUNTEERSIGNUP_ITEMS_REFERENCED_NOT_DELETED', count($cidReferenced)));
}
}
return true;
}
As you can see, I chose to treat things as a warning and the In the language file I added this as the message:
VOLUNTEERSIGNUP_ITEMS_REFERENCED_NOT_DELETED="%s item(s) were referenced and not deleted."
And finally, in the item models where I wanted to restrict deleting referenced items, I changed the delete method to look something like this:
/**
* Method to delete a event
*
* @access public
* @param array &$pks The Ids of elements to delete.
*
* @return boolean True on success
*/
public function delete(&$pks = array())
{
if (!count( $pks ))
return true;
//Integrity : Restrict fk : event_id in session
$model = JModel::getInstance('session', 'VolunteersignupModel');
if (!$model->integrityRestrict('event_id', $pks))
return false;
if (!count( $pks ))
return false;
if (!parent::delete($pks))
return false;
return true;
}
The important thing to note is that the integrityRestrict method is called before the delete and that this function modifies the cid array and removes any referenced items so that only those items that are not referenced get deleted.
Hopefully I explained things clearly ...
Dave
)
