doctrine/archived-orm
1
0
Fork 0
mirror of https://github.com/doctrine/orm.git synced 2026-03-23 22:42:18 +01:00
Code Issues 5.3k Packages Projects Releases 100 Wiki Activity

DDC-130: Cascading and @ManyToMany associations is broken #160

New Issue
Closed
opened 2026-01-22 12:29:02 +01:00 by admin · 20 comments
admin commented 2026-01-22 12:29:02 +01:00
Owner
Copy Link

Originally created by @doctrinebot on GitHub (Nov 9, 2009).

Originally assigned to: @beberlei on GitHub.

Jira issue originally created by user nicokaiser:

I have two Entities: Users and Alerts. They are associated with @ManyToMany, the assiciation table should be "user_alert".

Entities:
http://pastebin.com/m7bca724a

Sample code:
http://pastebin.com/m4e530f42

The "remove" command in the sample code deletes the user entry and alert entry, but not the user_alert entry (which was automatically created though). This leaves an orphan entry (or the DBMS will complain because of FK constraints).

INSERT INTO users (name) VALUES (?)
array(1) {
  [1]=>
  string(3) "Bob"
}
INSERT INTO alert (name) VALUES (?)
array(1) {
  [1]=>
  string(9) "Testalert"
}
INSERT INTO user_alert (userId, alertId) VALUES (?, ?)
array(2) {
  [0]=>
  int(1)
  [1]=>
  int(1)
}
DELETE FROM users WHERE id = ?
array(1) {
  [0]=>
  int(1)
}
DELETE FROM alert WHERE id = ?
array(1) {
  [0]=>
  int(1)
}

The user_alert table should be automatically updated (on creation and removal)...

Originally created by @doctrinebot on GitHub (Nov 9, 2009). Originally assigned to: @beberlei on GitHub. Jira issue originally created by user nicokaiser: I have two Entities: Users and Alerts. They are associated with @ManyToMany, the assiciation table should be "user_alert". Entities: http://pastebin.com/m7bca724a Sample code: http://pastebin.com/m4e530f42 The "remove" command in the sample code deletes the user entry and alert entry, but not the user_alert entry (which was automatically created though). This leaves an orphan entry (or the DBMS will complain because of FK constraints). ``` INSERT INTO users (name) VALUES (?) array(1) { [1]=> string(3) "Bob" } INSERT INTO alert (name) VALUES (?) array(1) { [1]=> string(9) "Testalert" } INSERT INTO user_alert (userId, alertId) VALUES (?, ?) array(2) { [0]=> int(1) [1]=> int(1) } DELETE FROM users WHERE id = ? array(1) { [0]=> int(1) } DELETE FROM alert WHERE id = ? array(1) { [0]=> int(1) } ``` The user_alert table should be automatically updated (on creation _and_ removal)...
admin added the Bug label 2026-01-22 12:29:02 +01:00
admin closed this issue 2026-01-22 12:29:03 +01:00
admin commented 2026-01-22 12:29:05 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Nov 9, 2009):

@doctrinebot commented on GitHub (Nov 9, 2009): - relates to [DDC-677: Allow DQL DELETE statements to work with join table fk constraints](http://www.doctrine-project.org/jira/browse/DDC-677)
admin commented 2026-01-22 12:29:05 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Nov 11, 2009):

Comment created by romanb:

I think in most cases the entry in the association table should be deleted by the FK constraint. However I think that is currently not the default. You can force it by using onDelete="CASCADE" on the @JoinColumn definitions inside the @JoinTable. That should probably be set by default on join columns on an association table.

I'm not sure its worth supporting manual deletion of association table entries through Doctrine as all databases support proper foreign key constraints and this is the most effective way to delete these entries and also enforces the integrity on the database side.

So I'm rather voting for making onDelete="CASCADE" the default for join columns of association tables unless specified otherwise.

@doctrinebot commented on GitHub (Nov 11, 2009): Comment created by romanb: I think in most cases the entry in the association table should be deleted by the FK constraint. However I think that is currently not the default. You can force it by using onDelete="CASCADE" on the @JoinColumn definitions inside the @JoinTable. That should probably be set by default on join columns on an association table. I'm not sure its worth supporting manual deletion of association table entries through Doctrine as all databases support proper foreign key constraints and this is the most effective way to delete these entries and also enforces the integrity on the database side. So I'm rather voting for making onDelete="CASCADE" the default for join columns of association tables unless specified otherwise.
admin commented 2026-01-22 12:29:05 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Nov 13, 2009):

Comment created by romanb:

It appears that this doesnt work in JPA(2) either, or well, it depends on the implementation provider. But cascade=REMOVE on @ManyToMany is not allowed per the spec.

This issue here is probably one of the reasons for this. Meaning there might be no easy way for the ORM to deal with entries in association tables transparently on cascade=REMOVE.

We have the following options:

  1. Disallow cascade=REMOVE on ManyToManyMapping (throw a MappingException)

  2. Allow it but clearly document that entries in association tables are not removed by Doctrine, so that you need to apply onDelete="CASCADE" on the @JoinColumn definitions. Are there any more caveats with this I can not think of currently?

  3. Someone has an idea for transparently deleting entries in association tables on cascade=REMOVE. Solutions would need to be performant and not too cumbersome to implement.

Waiting for feedback.

@doctrinebot commented on GitHub (Nov 13, 2009): Comment created by romanb: It appears that this doesnt work in JPA(2) either, or well, it depends on the implementation provider. But cascade=REMOVE on @ManyToMany is not allowed per the spec. This issue here is probably one of the reasons for this. Meaning there might be no easy way for the ORM to deal with entries in association tables transparently on cascade=REMOVE. We have the following options: 1) Disallow cascade=REMOVE on ManyToManyMapping (throw a MappingException) 2) Allow it but clearly document that entries in association tables are not removed by Doctrine, so that you need to apply onDelete="CASCADE" on the @JoinColumn definitions. Are there any more caveats with this I can not think of currently? 3) Someone has an idea for transparently deleting entries in association tables on cascade=REMOVE. Solutions would need to be performant and not too cumbersome to implement. Waiting for feedback.
admin commented 2026-01-22 12:29:06 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Nov 14, 2009):

Comment created by @beberlei:

Hm after thinking about it the problem is that the collection has to be either:

  1. loaded into memory completely and put through to regular remove a single entity process. This would be required to mark entities for deleted that are also hydrated but attached to another many to many collection of this type.
  2. selected for all their ids and there should be a many to many deletion mechanism that works based on id, however then also finds also hydrated entities based on their id.
@doctrinebot commented on GitHub (Nov 14, 2009): Comment created by @beberlei: Hm after thinking about it the problem is that the collection has to be either: 1. loaded into memory completely and put through to regular remove a single entity process. This would be required to mark entities for deleted that are also hydrated but attached to another many to many collection of this type. 2. selected for all their ids and there should be a many to many deletion mechanism that works based on id, however then also finds also hydrated entities based on their id.
admin commented 2026-01-22 12:29:07 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Nov 16, 2009):

Comment created by nicokaiser:

Hm, I don't know if my report was unclear... What I meant was automatic update of the join table ("user_alert" in this example). When I delete e.g. a User, I don't want to have to care about updates in the "user_alert" table - this table was automatically generated (by the @JoinTable statement), so it should be automatically updated, e.g. when I delete either a User or an Alert...

@doctrinebot commented on GitHub (Nov 16, 2009): Comment created by nicokaiser: Hm, I don't know if my report was unclear... What I meant was automatic update of the join table ("user_alert" in this example). When I delete e.g. a User, I don't want to have to care about updates in the "user_alert" table - this table was automatically generated (by the @JoinTable statement), so it should be automatically updated, e.g. when I delete either a User or an Alert...
admin commented 2026-01-22 12:29:07 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Nov 19, 2009):

Comment created by alex:

How about removing a relation between a user and an alert?

Meaning the user and alert both remain in the database but they are not connected anymore (think about users with multiple roles).

@doctrinebot commented on GitHub (Nov 19, 2009): Comment created by alex: How about removing a relation between a user and an alert? Meaning the user and alert both remain in the database but they are not connected anymore (think about users with multiple roles).
admin commented 2026-01-22 12:29:08 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Nov 19, 2009):

Comment created by romanb:

@Alexander: Just like in normal OOP. If a user as a collection of alerts and an alert a collection of users (bidirectional many-many) and you want to remove an alert from a user you simply remove the alert from the collection and the user from the collection of the corresponding alert. This is very clean, the only problem with this is the efficiency because in order to remove the elements from the collections, the collections must be loaded. (Strictly speaking, you only need to adjust the "owning side", so only 1 collection needs to be loaded). Of course you can always use some custom SQL that directly manipulates the intermediate table. See also DDC-128. Also there is DDC-45 but I dont think thats doable.

Collections of entities always only represent the association of the entity that has the collection and the contained entities. If you remove an entity from a collection, the association is removed, not the entity itself. "The association is removed" means that either the foreign key is NULLed out (in the case of one-to-many) or that the entry in the association table is removed.

@doctrinebot commented on GitHub (Nov 19, 2009): Comment created by romanb: @Alexander: Just like in normal OOP. If a user as a collection of alerts and an alert a collection of users (bidirectional many-many) and you want to remove an alert from a user you simply remove the alert from the collection and the user from the collection of the corresponding alert. This is very clean, the only problem with this is the efficiency because in order to remove the elements from the collections, the collections must be loaded. (Strictly speaking, you only need to adjust the "owning side", so only 1 collection needs to be loaded). Of course you can always use some custom SQL that directly manipulates the intermediate table. See also [DDC-128](http://www.doctrine-project.org/jira/browse/DDC-128). Also there is [DDC-45](http://www.doctrine-project.org/jira/browse/DDC-45) but I dont think thats doable. Collections of entities always only represent the association of the entity that has the collection and the contained entities. If you remove an entity from a collection, the association is removed, not the entity itself. "The association is removed" means that either the foreign key is NULLed out (in the case of one-to-many) or that the entry in the association table is removed.
admin commented 2026-01-22 12:29:09 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Nov 19, 2009):

Comment created by alex:

@Roman: All sounds very simple. But in practice this doesn't seem to work.
I have the following:

$uRep = $em->getRepository('System_User');
$user = $uRep->findOneById(1);
unset($user->roles[0]);
$em->persist($user);
$em->flush();

Now, when I clear the manager and reload the user:

  • If the user had 1 role -> it still has one role
  • If the user had more roles -> it still has the removed role and all others are duplicated.
@doctrinebot commented on GitHub (Nov 19, 2009): Comment created by alex: @Roman: All sounds very simple. But in practice this doesn't seem to work. I have the following: ``` $uRep = $em->getRepository('System_User'); $user = $uRep->findOneById(1); unset($user->roles[0]); $em->persist($user); $em->flush(); ``` Now, when I clear the manager and reload the user: - If the user had 1 role -> it still has one role - If the user had more roles -> it still has the removed role and all others are duplicated.
admin commented 2026-01-22 12:29:09 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Nov 19, 2009):

Comment created by romanb:

@Alexander: Please open a separate issue for this. Thanks.

@doctrinebot commented on GitHub (Nov 19, 2009): Comment created by romanb: @Alexander: Please open a separate issue for this. Thanks.
admin commented 2026-01-22 12:29:10 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (May 6, 2010):

Comment created by mjh_ca:

Pastebin entries have expired and this bug is not attached to a specific version so it is difficult to tell the status. Nico, is this still a problem with the current release?

@doctrinebot commented on GitHub (May 6, 2010): Comment created by mjh_ca: Pastebin entries have expired and this bug is not attached to a specific version so it is difficult to tell the status. Nico, is this still a problem with the current release?
admin commented 2026-01-22 12:29:11 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (May 17, 2010):

Comment created by romanb:

My currently proposed solution to this would be to simply make onDelete="CASCADE" the default for join columns in join tables. Anyone has a strong objection or better idea?

@doctrinebot commented on GitHub (May 17, 2010): Comment created by romanb: My currently proposed solution to this would be to simply make onDelete="CASCADE" the default for join columns in join tables. Anyone has a strong objection or better idea?
admin commented 2026-01-22 12:29:11 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Jun 30, 2010):

Comment created by @beberlei:

sounds good to me

@doctrinebot commented on GitHub (Jun 30, 2010): Comment created by @beberlei: sounds good to me
admin commented 2026-01-22 12:29:11 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Jul 9, 2010):

Comment created by @beberlei:

Other tools actually delete the related records manually, no cascading involved. Since Sqlite doesn't even support foreign keys we should probably do the same instead of relying on "onDelete"="cascade".

Whats your take roman?

@doctrinebot commented on GitHub (Jul 9, 2010): Comment created by @beberlei: Other tools actually delete the related records manually, no cascading involved. Since Sqlite doesn't even support foreign keys we should probably do the same instead of relying on "onDelete"="cascade". Whats your take roman?
admin commented 2026-01-22 12:29:11 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Jul 9, 2010):

Comment created by @beberlei:

We have to support this anyways for the following reason:

Doctrine 2 gives you absolutely no access to the many-to-many join table, i.e. to be working with cascade it should not only be the default, but the only option (since other options don't work). We need to extend the way "delete" works inside each persister to also clean up many-to-many tables.

We need this mechanism anyways for Element Collections to delete all the related entries.

The option onDelete="cascade|noaction" is therefore only a hint for the persisters to decide if they perform this action themselves, or let the database vendor perform it.

@doctrinebot commented on GitHub (Jul 9, 2010): Comment created by @beberlei: We have to support this anyways for the following reason: Doctrine 2 gives you absolutely no access to the many-to-many join table, i.e. to be working with cascade it should not only be the default, but the only option (since other options don't work). We need to extend the way "delete" works inside each persister to also clean up many-to-many tables. We need this mechanism anyways for Element Collections to delete all the related entries. The option onDelete="cascade|noaction" is therefore only a hint for the persisters to decide if they perform this action themselves, or let the database vendor perform it.
admin commented 2026-01-22 12:29:11 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Jul 9, 2010):

Comment created by @beberlei:

I pushed my proposed changes to a feature branch: http://github.com/doctrine/doctrine2/tree/DDC-130

There are definatly refactorings that need to be done, however in that state its currently doing its job very well.

@doctrinebot commented on GitHub (Jul 9, 2010): Comment created by @beberlei: I pushed my proposed changes to a feature branch: http://github.com/doctrine/doctrine2/tree/[DDC-130](http://www.doctrine-project.org/jira/browse/DDC-130) There are definatly refactorings that need to be done, however in that state its currently doing its job very well.
admin commented 2026-01-22 12:29:12 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Jul 10, 2010):

Comment created by @beberlei:

What about DQL DELETE?

DELETE FROM Boo WHERE bar

For each join table:

DELETE FROM join_foo WHERE (SELECT id FROM foo WHERE bar)

Can this be done determinstically?

@doctrinebot commented on GitHub (Jul 10, 2010): Comment created by @beberlei: What about DQL DELETE? ``` DELETE FROM Boo WHERE bar ``` For each join table: ``` DELETE FROM join_foo WHERE (SELECT id FROM foo WHERE bar) ``` Can this be done determinstically?
admin commented 2026-01-22 12:29:13 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Jul 10, 2010):

Comment created by @beberlei:

The DQL stuff is just way to much to add. Foreign Key constraints should fail in these cases imho

@doctrinebot commented on GitHub (Jul 10, 2010): Comment created by @beberlei: The DQL stuff is just way to much to add. Foreign Key constraints should fail in these cases imho
admin commented 2026-01-22 12:29:13 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Jul 10, 2010):

Comment created by romanb:

I think we have to do this on bulk deletion then, too.

Examples from EclipseLink:

em.createQuery("delete from User2 u where u.id=1").executeUpdate();
=>
DELETE FROM USER2*GROUP2 WHERE EXISTS(SELECT ID FROM USER2 WHERE (ID = ?) AND ID = USER2_GROUP2.User2*ID)
DELETE FROM USER2 WHERE (ID = ?)

em.createQuery("delete from User2 u").executeUpdate();
=>
DELETE FROM USER2_GROUP2
DELETE FROM USER2
@doctrinebot commented on GitHub (Jul 10, 2010): Comment created by romanb: I think we have to do this on bulk deletion then, too. Examples from EclipseLink: ``` em.createQuery("delete from User2 u where u.id=1").executeUpdate(); => DELETE FROM USER2*GROUP2 WHERE EXISTS(SELECT ID FROM USER2 WHERE (ID = ?) AND ID = USER2_GROUP2.User2*ID) DELETE FROM USER2 WHERE (ID = ?) ``` ``` em.createQuery("delete from User2 u").executeUpdate(); => DELETE FROM USER2_GROUP2 DELETE FROM USER2 ```
admin commented 2026-01-22 12:29:13 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Jul 10, 2010):

Comment created by @beberlei:

Fixed in master using the following semantics:

  • If a many-to-many join table is to be found not using onDelete="CASCADE" execute this manually
  • In self-referential relationships delete both possible pairs.
  • Use Database onDelete="CASCADE" if present on both foreign keys of the join table.

Tested against normal entities, self-referential, CTI and STI, from the owning and the inverse side.

@doctrinebot commented on GitHub (Jul 10, 2010): Comment created by @beberlei: Fixed in master using the following semantics: - If a many-to-many join table is to be found not using onDelete="CASCADE" execute this manually - In self-referential relationships delete both possible pairs. - Use Database onDelete="CASCADE" if present on both foreign keys of the join table. Tested against normal entities, self-referential, CTI and STI, from the owning and the inverse side.
admin commented 2026-01-22 12:29:13 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Jul 10, 2010):

Issue was closed with resolution "Fixed"

@doctrinebot commented on GitHub (Jul 10, 2010): Issue was closed with resolution "Fixed"
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
BC Break
Bug
Can't Fix
Deprecation
Documentation
DQL
Duplicate
Failing Test
Hacktoberfest
Improvement
Improvement
Incomplete
Invalid
Missing Tests
New Feature
PR Created
pull-request
Mirrored from GitHub Pull Request
 Question
Regression
Status: Waiting feedback
Waiting feedback
WIP
Won't Fix
No Label Bug
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
admin
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: doctrine/archived-orm#160
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?