Datamapper: delete() only removes join_table data

[eluser]brent.fontaine[/eluser]
Hello, I have hit a wall here and hope someone can help me. This might be right under my nose....

I have created a number of objects that relate back to a phone object, and use one join table. My DataMapper config does not negate the default cascade_delete (nor do any of my models), and my database does not have any 'cascade' actions on any of my tables. There is no production-cache either.

Code:

/**
* Phone model...
*/
var $model = 'phone';
var $table = 'phones';
var $has_one = array(
  'individual'   => array('join_table'  => 'jn_phones'),
  'branch'       => array('join_table'  => 'jn_phones'),
  .... etc ...
var $validation = array(
  'Number' => array(
      'rules'     => array('clean_phone','required'),
      'get_rules' => array('format_phone'),
      'label'     => 'Phone Number'
  ),
);


/**
* Individual model...
*/
var $model = 'individual';
var $table = 'individuals';
var $has_many = array(
  'phone'        => array('join_table'  => 'jn_phones'),
  'location'     => array('join_table'  => 'jn_locations'),
  'note'         => array('join_table'  => 'jn_notes'),
);

I have tried to delete a Phone record a couple of different ways, and they all end with the same result:

Code:

/**
* Method 1...
*/
$Phone = new Phone($Phone_id);
$Phone->delete();

/**
* Method 2...
*/
$Individual->phone->where('id',$Phone_id)->get();
if ( ! $Individual->phone->delete() ){
  array_push($this->data, $Individual->phone->error->all);
  echo json_encode($this->data);
}

They end up with the same results. The Phone record remains intact, but the record in jn_phones is deleted, resulting in orphaned records. I tried this on a remote MySQL server, as well as a local XAMPP-MySQL server. There are no errors, and Datamapper returns TRUE when I delete().

Am I missing something incredibly simple? I would love it if there is a very simple fix that I overlooked. Thanks in advance.

[eluser]WanWizard[/eluser]
From a code point of view, a one-to-many with a junction table and a many-to-many (which always has a junction table) are treated equally.

In both cases the junction record is deleted, but not the related record.

At the moment, this feature is of limited functionality, as it only cascades one level deep (it doesn't delete relations of the related records), and only for one-to-one and one-to-many's (that use a FK).

The parameter was introduced not to enable this (as it was always enabled), but to allow you to disable this behaviour.

[eluser]brent.fontaine[/eluser]
Thanks for the quick reply WanWizard.

I tried a few other things since I read your response yesterday, and for the life of me, I still cannot get it to delete the Phone record, and the associated join-record.

Code:

mysql> describe jn_phones;
+--------------------+------------------+------+-----+---------+----------------+
| Field              | Type             | Null | Key | Default | Extra          |
+--------------------+------------------+------+-----+---------+----------------+
| id                 | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| phone_id           | int(10) unsigned | YES  | MUL | NULL    |                |
| office_id          | int(10) unsigned | YES  |     | NULL    |                |
| individual_id      | int(10) unsigned | YES  |     | NULL    |                |
... omit ...
+--------------------+------------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)


mysql> describe tbl_phones;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| id        | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| created   | datetime         | NO   |     | NULL    |                |
| updated   | datetime         | NO   |     | NULL    |                |
| Category  | int(10) unsigned | NO   |     | NULL    |                |
| Phone     | varchar(12)      | YES  |     | NULL    |                |
| Extension | varchar(10)      | YES  |     | NULL    |                |
+-----------+------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

I ended up trying to get MySQL to do cascade on delete with jn_phones, and it still didn't delete the real tbl_phones record.

If datamapper is intended to only delete the join-table record, how should one delete the actual record in tbl_phones?

I appreciate any help that anyone can provide.

[eluser]WanWizard[/eluser]
As said, cascading deletes is something that doesn't really work like you want it (and it should).

Before the introduction of that config key, the hardcoded behaviour of Datamapper was TRUE, which would always delete relation records, or reset FK's to NULL. On request of the community, this key was introduced so this hardcoded behaviour could be disabled.

The use case for this was that if you have INNODB tables with foreign key constraints (ON DELETE CASCADE), Datamapper can not be allowed to reset the FK's which will create constraint conflicts.

At the moment, this is the preferred solution when using Datamapper (let your RDBMS handle it).

To properly implement this in Datamapper, it should instantiate all related records for the record you're deleting, and run a delete() on them too, which would introduce a true cascading, but potentionally very slow and I/O intensive delete.

[eluser]brent.fontaine[/eluser]
Thanks again. I need to brush up on my MySQL. I really appreciate your help WanWizard.