Implementing ondelete restrict association in CakePHP

Question

Hi

I have Event belongsTo Venue association. When a user tries to delete a venue, I don't want it to happen when one or more event is associated with it. What's the most automagic way to do it?

Answer 1

I'd just code it, probably on the Venue model.

See http://book.cakephp.org/view/685/beforeDelete

Answer 2

If you setup a counterCache on the relationship and use deleteAll instead of delete you can pass conditions to the delete query based on the number of events attached to the venue.

<?php
    /**
     *  Event Model
     *
     *  uses table events
     *  @fields array( id, venue_id, ... )
     *  
     */

    class Event extends AppModel {

        public $name = "Event";

        // setup the relationship to venues table with a counterCache
        public $belongsTo = array(
            'Venue' => array(
                'className' => 'Venue',
                'counterCache' => true
            )
        );
    }
?>

<?php
    /**
     *  Venue Model
     *
     *  uses venues table
     *  @fields array( id, event_count, ... )
     *  
     */

    class Venue extends AppModel {

        public $name = "Venue";

        // setup the relationship to events table
        public $hasMany = array(
            'Event' => array(
                'className' => 'Event',
            )
        );
    }
?>

<?php
    /**
     *  Venues Controller
     *
     *  example of a delete function using deleteAll to include conditions instead of delete which only takes an id
     *  
     */

    class VenuesController extends AppController {

        /**
         *  delete a venue
         *
         *  checks to make sure a venue has no events and then deletes it.
         */

        public function delete( $id = null ){
            if( $id ){
                // make sure the conditions array checks for event_count re: counterCache
                $conditions = array( 'Venue.id' => $id, 'Venue.event_count' => 0 );

                // run deleteAll but enable callbacks so that the deleteAll functions as a normal delete
                ( $this->Venue->deleteAll( $conditions, true, true )) ? $this->Session->setFlash( "Event deleted" ) : $this->Session->setFlash( "Event still has attached events and could not be deleted." ); 
            }
            return $this->redirect( array( 'controller' => 'venues', 'action' => 'index' ));
        }
    }
?>

Answer 3

If you're using InnoDB tables you can add a foreign key constraint that prevents Venues from being deleted if it has events and if the Venue ID is updated, it automatically updates the venue_id values in the events table.

ALTER TABLE  `events` ADD FOREIGN KEY (  `venue_id` ) REFERENCES  `venues` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE ;

Or something like that anyway.

It just an option that you can use if you want that adds that extra robustness at the data level in case there is a bug in your app that prevents the restriction being detected in the code. I would add it in the code as well so your app doesn't get an SQL warning/error.