#5745: Saving HABTM relationship deletes wrong data if there is no primary key on join table ------------------------------------------------------------+--------------- Reporter: [EMAIL PROTECTED] | Type: Optimization Status: new | Priority: Low Milestone: | Component: Model Version: RC3 | Severity: Minor Keywords: habtm hasandbelongstomany delete model save | Php_version: PHP 5 Cake_version: 1.2.0.7692 RC3 | ------------------------------------------------------------+--------------- I guess this should also be titled "Join tables shouldn't need a primary key".
By not reading the docs closely enough, I setup my habtm like this: {{{ table organisations_sites organisation_id references organisations.id site_id references sites.id class Organisation { var $habtm = array("Site"); } class Site { var $habtm = array("Organisation"); } }}} I have the following data in organisations_sites ||organisation_id||site_id|| ||1||21|| ||2||22|| ||1||22|| Seems right enough. However, when I go to save a "Site" with ID "21", the following SQL is generated (trimmed for brevity): {{{ DELETE FROM organisations_sites WHERE organisation_id = 1 INSERT INTO organisations_sites VALUES (1, 21); }}} This is bad, it deletes rows from my join table that apply to other sites. What I expected was for this to occur instead: {{{ DELETE FROM organisations_sites WHERE site_id = 21 INSERT INTO organisations_sites VALUES (1, 21) }}} I read through cake/lib/model/model.php a bit, and it looks like the missing step is in "deleteAll" {{{ Line 1680: return $db->delete($this, array($this->alias . '.' . $this->primaryKey => $ids)); }}} where $this is "OrganisationsSites". It looks like somehow, "primaryKey" is getting the "organisation_id" column, even though my table has no primary key, and there is none defined in the model. The fix is to simply add a "id integer primary key" column to the table in the database, but perhaps the deleteAll method could be a little more picky about the situation and not go deleting all my data when I make this kind of mistake? At a minimum, I would've expected it to delete by the key "site_id" which referred to the table of the Model object that I was actually saving. Someone with more Cake-foo than me would need to make that change however, I suspect there are quite a few implications to changing that. -- Ticket URL: <https://trac.cakephp.org/ticket/5745> CakePHP : The Rapid Development Framework for PHP <https://trac.cakephp.org/> Cake is a rapid development framework for PHP which uses commonly known design patterns like ActiveRecord, Association Data Mapping, Front Controller and MVC. Our primary goal is to provide a structured framework that enables PHP users at all levels to rapidly develop robust web applications, without any loss to flexibility. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "tickets cakephp" group. To post to this group, send email to tickets-cakephp@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/tickets-cakephp?hl=en -~----------~----~----~----~------~----~------~--~---