[SOLVED] make ugly delete pretty?

[eluser]seanloving[/eluser]
In my application one "customer record" means one "contacts" record and one or more associated "numbers" "emailaddresses" and "addresses" records.

When I delete a contact then I want to delete all the addresses, numbers and emailaddresses associated with that contact.

Here are my data tables

contacts
contact_id | contact

addresses
address_id | address

numbers
number_id | number

emailaddress
emailaddress_id | emailaddress

Here are my link tables

addresses_contacts
address_id | contact_id

contacts_emailaddresses
emailaddress_id | contact_id

contacts_numbers
number_id | contact_id

When I want to delete a single customer contact then one way is to perform 6 database queries - 1 SELECT and 5 DELETE. Currently, here is my model function that I use to delete a contact:

Code:

function delete_contact( $id ) {
  
  $primarykeys = $this->db
   ->select( " contacts.contact_id,
      addresses.address_id,
      numbers.number_id,
      emailaddresses.emailaddress_id ",
      FALSE)
   ->from('contacts')
  
   ->join('addresses_contacts', 'addresses_contacts.contact_id = contacts.contact_id', 'left')
   ->join('addresses', 'addresses.address_id = addresses_contacts.address_id', 'left')  
  
   ->join('contacts_emailaddresses', 'contacts_emailaddresses.contact_id = contacts.contact_id', 'left')
   ->join('emailaddresses', 'emailaddresses.emailaddress_id = contacts_emailaddresses.emailaddress_id', 'left')  
  
   ->join('contacts_numbers', 'contacts_numbers.contact_id = contacts.contact_id', 'left')
   ->join('numbers', 'numbers.number_id = contacts_numbers.number_id', 'left')

   ->where('contacts.contact_id', $id)
   ->get()->row_array();
  
  $this->db->where('contact_id',$primarykeys['contact_id'])->delete('contacts');
  $this->db->where('address_id',$primarykeys['address_id'])->delete('addresses');
  $this->db->where('number_id',$primarykeys['number_id'])->delete('numbers');
  $this->db->where('emailaddress_id',$primarykeys['emailaddress_id'])->delete('emailaddresses');

I am using InnoDB with foreign keys in my link tables that are set to DELETE CASCADE. This automates things slightly so that I don't have to also perform three more DELETE queries on the link tables but... I know there must be a slick way to do all this with a single query.

More info:

Here is how my link tables are defined:

Code:

CREATE TABLE IF NOT EXISTS `addresses_contacts` (
  `contact_id` int(11) NOT NULL,
  `address_id` int(11) NOT NULL,
  KEY `address_id` (`address_id`),
  KEY `contact_id` (`contact_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
ALTER TABLE `addresses_contacts`
  ADD CONSTRAINT `addresses_contacts_ibfk_2` FOREIGN KEY (`address_id`) REFERENCES `addresses` (`address_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `addresses_contacts_ibfk_1` FOREIGN KEY (`contact_id`) REFERENCES `contacts` (`contact_id`) ON DELETE CASCADE ON UPDATE CASCADE;

I have tried to add a foreign key constraint on my data tables "addresses", "numbers" and "emailaddresses" such that the unique record in those tables will delete when a corresponding record is deleted from the corresponding link table. However, once those foreign key contraints are in place, I get an error "Cannot add or update a child row: a foreign key constraint fails" whenever I try to insert a record into any one of those "addresses", "numbers" or "emailaddresses" tables.

I hope somebody can enlighten me.

-seanloving

[eluser]seanloving[/eluser]
I figured it out. Pretty cool.:coolsmile:

Code:

function delete_contact( $id ) {
    
  /* Multi-Table Delete via MySQL
   Foreign Key References defined in the link tables are set to DELETE CASCADE
   To avoid FK REFs (e.g MyISAM) then just add the link table names to the DELETE statement
  */
  $this->db->query(" DELETE contacts, addresses, emailaddresses, numbers
       FROM contacts
        INNER JOIN addresses_contacts INNER JOIN addresses
        INNER JOIN contacts_emailaddresses INNER JOIN emailaddresses
        INNER JOIN contacts_numbers INNER JOIN numbers
       WHERE contacts.contact_id = $id
        AND addresses.address_id = addresses_contacts.address_id
        AND emailaddresses.emailaddress_id = contacts_emailaddresses.emailaddress_id
        AND numbers.number_id = contacts_numbers.number_id;
      ");
}

Is there any way to make Active Record add a JOIN clause to a DELETE query?