Deleting from join table in CakePHP

Question

I am having a problem where I have tables users and teams, my relation is defined as below:

public $hasAndBelongsToMany = array(
    'Teams' => array(
        'className' => 'Team',
        'joinTable' => 'teams_users',
        'foreignKey' => 'user_id',
        'associationForeignKey' => 'team_id',
        'unique' => 'keepExisting',
        'order' => array('name' => 'ASC')
    )
);

Now the problem is that when I delete a user who for example belongs to team with id 1, all associations from teams_users that have id 1 disappear with it. In my unit tests I see that the delete query doesn't really care about the user_id and deletes all in my team :( following is the automated query:

DELETE `TeamsUser` 
FROM `enterpriseappstore_test`.`teams_users` AS `TeamsUser` 
WHERE `TeamsUser`.`team_id` = 1

This is the code responsible for deleting the user in Model:

$ok = $this->delete((int)$userId, false);

How do I delete only associations of the specific user, not team? So, from UsersController, I need to delete one user and his connections to all the teams that remain ... now for whatever reason, I am deleting user and all associations (connections from teams_users) where team_id is the same, not user_id

Instead of delete method, you can use the edit method and simply deselect the association between your Team and Users — Salines, Jun 05 '14 at 19:27
From UsersController, I need to delete one user and his connections to all the teams that remain ... now for whatever reason, I am deleting user and all associations (connections from teams_users) where team_id is the same, not user_id ... hope I wasn't mumbling too much! :) — Ondrej Rafaj, Jun 05 '14 at 19:42
From which controller do you call the delete method? Also is the association you mention in your post in the User model? — user221931, Jun 06 '14 at 02:37
I am deleting from UsersController and the association is indeed in the User model. I have even tried to unbind all the associations and all the entries with the same team will still get deleted from the join table :( Thinking about actually writing a $this->User->query('DELETE ...'); even tho I really don't like such solution as I have by using Cake agreed to use the system classes and the HABTM. — Ondrej Rafaj, Jun 06 '14 at 18:08
Can you remove any model files for ``Team`` and/or ``TeamsUser`` if they exist and check again? It would help to show more of the model's delete function and how you call it from the controller. Also are there any model callbacks that could be confusing things? — user221931, Jun 06 '14 at 22:39

score 0 · Accepted Answer · answered Jun 12 '14 at 23:06

So in the end I had to do manual SQL ... not really proud of that but it is so far the only solution I have found. Very happy to award better solution:

$this->query('DELETE FROM `teams_users` WHERE `user_id` = '.(int)$userId.';');
$this->query('DELETE FROM `users` WHERE `id` = '.(int)$userId.';');

score -1 · Answer 2 · answered Jun 06 '14 at 08:53

-1

From the controller do this:

$ok = $this->User->delete((int)$userId);

Ensure that the foreign key from teams_users to users table has constraint ON DELETE CASCADE, f.ex.:

ALTER TABLE teams_users 
    ADD CONSTRAINT fk_users_teams
    FOREIGN KEY(user_id)
    REFERENCES users(id)
    ON DELETE CASCADE;

http://www.mysqltutorial.org/mysql-on-delete-cascade/

answered Jun 06 '14 at 08:53

bancer

7,475
7
39
58

1

That's not really an answer as to why SQL that deletes the ``team_id`` instead of ``user_id`` is created for that table. While it is good practice to offload integrity checks to the DB it won't do him any good. – user221931 Jun 06 '14 at 17:19
The wrong record is deleted because `$this->delete((int)$userId, false);` is called from the wrong place. – bancer Jun 06 '14 at 21:26
Well not according to the user's comments in the main post. He claims to be calling it from inside the ``User`` model. – user221931 Jun 06 '14 at 22:43
@Ondrej, if you use InnoDb tables it is recommended to pass the responsibility to decide whether to delete associated records to the database. If you use MyIsam then post more data - all the queries generated by `delete` call. If they are enclosed by a transaction post the entire transaction. Do it on live database (or the replica), but not the test one. – bancer Jun 09 '14 at 12:04

Deleting from join table in CakePHP

2 Answers2