Benjamin Eberlei
added a comment - 04/Apr/11 6:20 AM This is expected, cascade remove is working with in memory instances of the to be deleted objects. This way lifecycle events are triggered.
If you want to perform this operation on the databse level you have to set onDelete="CASCADE" as an option of the join column:
http://www.doctrine-project.org/docs/orm/2.0/en/reference/annotations-reference.html#annref-joincolumn
This difference should be explained in the docs on Working with Associations, 8.6 Transitive persistence / Cascade Operations i guess.

I have now tested quite a number of different scenarios, both scenarios that involve inheritance, and scenarios that do not.

Inheritance was just the matter in the original issue because the initial observation was that, at the database level, only inheritance relationships would declare an ON DELETE CASCADE constraint. Inheritance does not really have an effect on the basic problem that delete operations are not cascaded.

As the application code I use involves a lot of inheritance, and since you explicitly mentioned it, I included inheritance in the tests, too, just to see if it made a difference. I performed the tests based on two different data models - first one where the target entity class of the association would inherit from another entity class, and a second one where it does not.

In the end, inheritance did not matter. The results were the same whether the target entity inherits from a base class or not.

What did matter is the way in which the delete was performed.

I have accidentally given you wrong information about the way I actually deleted the entity instances in my last post. Since calling the remove method is the regular way I delete objects in my application, I blindly assumed it would be the same in this scenario. Unfortunately, I overlooked a special case here. In that case, the delete is performed using a DQL DELETE query.

It seems that, for deletions performed using DQL queries, the cascade options are not respected. This is only logical, since - assuming that DQL gets compiled down straight to SQL and has no side effects on the in-memory objects - there will not be any in-memory object graph, and therefore any technique based purely on in-memory objects cannot possibly work for DQL.

A solution would be to declare ON DELETE CASCADE constraints at the database level too (as already done in case of inheritance relationships) for associations that declare "remove" as a cascade option, so that the SQL resulting from the compilation of DQL DELETE queries will have the expected semantics, and maybe the onDelete annotation would be mandatory here.

There is an entity class "Something" which refers to an entity class "SomethingElse" with a bidirectional one-to-one association
The association declares a typical set of cascade options I use, including "remove". That's it.

Here is the second model, where the target entity class of the association inherits from another entitiy class.
Except for the inheritance, this second model is functionally identical to the first:

We now have an entity instance of class "Something", which refers to an entity instance of class "SomethingElse". The database with the model involving inheritance is identical, except for the implementation detail that the entity instance of class "SomethingElse" inherits from an entity instance of class "BaseClassForSomethingElse".

This is the setup. Now to the program code:

This code deletes both entity instances correctly, as expected, including the base class row in case of the second data model:

The code deletes the entity instance of class "Something", but will not delete the dependent data object of class "SomethingElse".

It seems like the cascade option for the cascading delete is completely ignored by any delete operations performed through DQL queries (as opposed to e. g. calling the remove method on the entity manager).

In the trivial example above, the query could simply be replaced by a call to the remove method, of course. Fortunately, in the application I am developing, this is possible, so I can resort to that.
But for queries involving more complex WHERE clauses, it will not be easily possible, except maybe by first performing a SELECT query to have the WHERE clause evaluated, and then iterating over the result object-by-object to delete them.

Is this behaviour (cascade options being bypassed by DQL queries) intended? At least it is logical according to your explanation of the in-memory object-graph approach.
It would be a pain to understand though, with no clear mention of this fundamental restriction being available in the docs.

However, I found a mention of an "onDelete" option in the "transitive persistence" section of the reference documentation

"[...] To rely on the database level cascade operations for the delete operation instead, you can configure each join column with the onDelete option. See the respective mapping driver chapters for more information."

Personally, I would opt for generally using ON DELETE CASCADE constraints at the database level - either exclusively, or in addition to anything that might happen in the object domain. This is IMO the only efficient way to make the declared cascade=

{"remove"}

constraint be generally effective.

Please clarify if the behaviour is intended, and if declaring onDelete="cascade" is mandatory to have DQL queries cascade delete operations.

Daniel Alvarez Arribas
added a comment - 26/Sep/11 3:24 AM - edited Thanks for your quick reply.
I have now tested quite a number of different scenarios, both scenarios that involve inheritance, and scenarios that do not.
Inheritance was just the matter in the original issue because the initial observation was that, at the database level, only inheritance relationships would declare an ON DELETE CASCADE constraint. Inheritance does not really have an effect on the basic problem that delete operations are not cascaded.
As the application code I use involves a lot of inheritance, and since you explicitly mentioned it, I included inheritance in the tests, too, just to see if it made a difference. I performed the tests based on two different data models - first one where the target entity class of the association would inherit from another entity class, and a second one where it does not.
In the end, inheritance did not matter. The results were the same whether the target entity inherits from a base class or not.
What did matter is the way in which the delete was performed.
I have accidentally given you wrong information about the way I actually deleted the entity instances in my last post. Since calling the remove method is the regular way I delete objects in my application, I blindly assumed it would be the same in this scenario. Unfortunately, I overlooked a special case here. In that case, the delete is performed using a DQL DELETE query.
It seems that, for deletions performed using DQL queries, the cascade options are not respected. This is only logical, since - assuming that DQL gets compiled down straight to SQL and has no side effects on the in-memory objects - there will not be any in-memory object graph, and therefore any technique based purely on in-memory objects cannot possibly work for DQL.
A solution would be to declare ON DELETE CASCADE constraints at the database level too (as already done in case of inheritance relationships) for associations that declare "remove" as a cascade option, so that the SQL resulting from the compilation of DQL DELETE queries will have the expected semantics, and maybe the onDelete annotation would be mandatory here.
Here are the details of the tests I performed:
First, here are the entity models used for the test:
Here's the first, simple, model:
<?php
namespace persistentData\model;
/**
* @Entity
*/
class Something {
/**
* @Id
* @Column(type= "bigint" )
* @GeneratedValue
*/
public $dbID;
/**
* @OneToOne(targetEntity= "persistentData\model\SomethingElse" , inversedBy= "thing" , cascade={ "persist" , "remove" , "detach" , "merge" })
* @JoinColumn(name= "otherThing_dbID" , referencedColumnName= "dbID" )
*/
public $otherThing;
}
/**
* @Entity
*/
class SomethingElse {
/**
* @Id
* @Column(type= "bigint" )
* @GeneratedValue
*/
public $dbID;
/**
* @OneToOne(targetEntity= "persistentData\model\Something" , mappedBy= "otherThing" )
*/
public $thing;
}
?>
There is an entity class "Something" which refers to an entity class "SomethingElse" with a bidirectional one-to-one association
The association declares a typical set of cascade options I use, including "remove". That's it.
Here is the second model, where the target entity class of the association inherits from another entitiy class.
Except for the inheritance, this second model is functionally identical to the first:
<?php
namespace persistentData\model;
/**
* @Entity
*/
class Something {
/**
* @Id
* @Column(type= "bigint" )
* @GeneratedValue
*/
public $dbID;
/**
* @OneToOne(targetEntity= "persistentData\model\SomethingElse" , inversedBy= "thing" , cascade={ "persist" , "remove" , "detach" , "merge" })
* @JoinColumn(name= "otherThing_dbID" , referencedColumnName= "dbID" )
*/
public $otherThing;
}
/**
* @Entity
* @InheritanceType( "JOINED" )
* @DiscriminatorColumn(name= "doctrineTypeDiscriminator" , type= "string" , length=64)
* @DiscriminatorMap({ "baseClassForSomethingElse" = "persistentData\model\BaseClassForSomethingElse" ,
* "somethingElse" = "persistentData\model\SomethingElse" })
*/
abstract class BaseClassForSomethingElse {
/**
* @Id
* @Column(type= "bigint" )
* @GeneratedValue
*/
public $dbID;
}
/**
* @Entity
*/
class SomethingElse extends BaseClassForSomethingElse {
/**
* @OneToOne(targetEntity= "persistentData\model\Something" , mappedBy= "otherThing" )
*/
public $thing;
}
?>
I used the Doctrine command-line tools to generate the proxy classes and update the database model. Consequently, the following tables were generated:
For the simple model:
mysql> describe Something;
+-----------------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+------------+------+-----+---------+----------------+
| dbID | bigint(20) | NO | PRI | NULL | auto_increment |
| otherThing_dbID | bigint(20) | YES | UNI | NULL | |
+-----------------+------------+------+-----+---------+----------------+
mysql> describe SomethingElse;
+-------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+----------------+
| dbID | bigint(20) | NO | PRI | NULL | auto_increment |
+-------+------------+------+-----+---------+----------------+
And for the model involving inheritance:
mysql> describe Something;
+-----------------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+------------+------+-----+---------+----------------+
| dbID | bigint(20) | NO | PRI | NULL | auto_increment |
| otherThing_dbID | bigint(20) | YES | UNI | NULL | |
+-----------------+------------+------+-----+---------+----------------+
mysql> describe SomethingElse;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| dbID | bigint(20) | NO | PRI | NULL | |
+-------+------------+------+-----+---------+-------+
mysql> describe BaseClassForSomethingElse;
+---------------------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------------+-------------+------+-----+---------+----------------+
| dbID | bigint(20) | NO | PRI | NULL | auto_increment |
| doctrineTypeDiscriminator | varchar(64) | NO | | NULL | |
+---------------------------+-------------+------+-----+---------+----------------+
Using doctrine, I then created some sample entity instances:
$something = new Something();
$somethingElse = new SomethingElse();
$doctrineEntityManager->persist($somethingElse);
$something->otherThing = $somethingElse;
$doctrineEntityManager->persist($something);
The database ends up with the following content:
For the simple model:
mysql> select * from Something;
+------+-----------------+
| dbID | otherThing_dbID |
+------+-----------------+
| 2 | 2 |
+------+-----------------+
1 row in set (0.00 sec)
mysql> select * from SomethingElse;
+------+
| dbID |
+------+
| 2 |
+------+
1 row in set (0.00 sec)
And for the model involving inheritance:
mysql> select * from Something;
+------+-----------------+
| dbID | otherThing_dbID |
+------+-----------------+
| 1 | 1 |
+------+-----------------+
1 row in set (0.00 sec)
mysql> select * from SomethingElse;
+------+
| dbID |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql> select * from BaseClassForSomethingElse;
+------+---------------------------+
| dbID | doctrineTypeDiscriminator |
+------+---------------------------+
| 1 | somethingElse |
+------+---------------------------+
1 row in set (0.00 sec)
We now have an entity instance of class "Something", which refers to an entity instance of class "SomethingElse". The database with the model involving inheritance is identical, except for the implementation detail that the entity instance of class "SomethingElse" inherits from an entity instance of class "BaseClassForSomethingElse".
This is the setup. Now to the program code:
This code deletes both entity instances correctly, as expected, including the base class row in case of the second data model:
$query = $doctrineEntityManager->createQuery('SELECT something
FROM \persistentData\model\Something something
WHERE something.dbID = 2');
$something = $query->getSingleResult();
$doctrineEntityManager->remove($something);
This code will also work correctly, having the exact same effect as the code above:
$something = $doctrineEntityManager->find('persistentData\model\Something', 2);
$doctrineEntityManager->remove($something);
However, this code will not work:
$query = $doctrineEntityManager->createQuery('DELETE
FROM \persistentData\model\Something something
WHERE something.dbID = 2');
$something = $query->execute();
The code deletes the entity instance of class "Something", but will not delete the dependent data object of class "SomethingElse".
It seems like the cascade option for the cascading delete is completely ignored by any delete operations performed through DQL queries (as opposed to e. g. calling the remove method on the entity manager).
In the trivial example above, the query could simply be replaced by a call to the remove method, of course. Fortunately, in the application I am developing, this is possible, so I can resort to that.
But for queries involving more complex WHERE clauses, it will not be easily possible, except maybe by first performing a SELECT query to have the WHERE clause evaluated, and then iterating over the result object-by-object to delete them.
Is this behaviour (cascade options being bypassed by DQL queries) intended? At least it is logical according to your explanation of the in-memory object-graph approach.
It would be a pain to understand though, with no clear mention of this fundamental restriction being available in the docs.
However, I found a mention of an "onDelete" option in the "transitive persistence" section of the reference documentation
" [...] To rely on the database level cascade operations for the delete operation instead, you can configure each join column with the onDelete option. See the respective mapping driver chapters for more information."
Do I have to rely on that, even if cascade="remove" is declared?
The annotation reference explains the onDelete annotation as "onDelete: Cascade Action (Database-level)". However, this is misleading, because a cascade option is understood to be one of "persist"/"remove"/"detach"/"merge"/"all" throughout the annotation reference. Only the Doctrine 1.2 docs contain a mention of "database-level cascades" ( http://www.doctrine-project.org/documentation/manual/1_1/ru/defining-models:transitive-persistence:database-level-cascades ). Reading the source, I assume the syntax would be onDelete="cascade", but from the docs this is not evident.
Personally, I would opt for generally using ON DELETE CASCADE constraints at the database level - either exclusively, or in addition to anything that might happen in the object domain. This is IMO the only efficient way to make the declared cascade=
{"remove"}
constraint be generally effective.
Please clarify if the behaviour is intended, and if declaring onDelete="cascade" is mandatory to have DQL queries cascade delete operations.
Thanks.

Ah ok, using DELETE makes all the difference. This is actually the exepcted behavior. Cascade Operations are purely in-memory operations and dont trigger on DELETE statements. That is waht the join column on-delete definitions are for.

Benjamin Eberlei
added a comment - 27/Sep/11 7:25 AM Ah ok, using DELETE makes all the difference. This is actually the exepcted behavior. Cascade Operations are purely in-memory operations and dont trigger on DELETE statements. That is waht the join column on-delete definitions are for.
This is documented in the DQL chapter, but should probably be documented in the cascade section aswell: http://www.doctrine-project.org/docs/orm/2.0/en/reference/dql-doctrine-query-language.html#delete-queries

Thanks for the clarification. Doctrine 2 really is one complex beast to tame.

Maybe a note in the architecture section could point out that anything involving DQL belongs to the database domain as opposed to the object domain. As DQL is toutet as an object-query-language it is quite contrary to the principle of least astonishment that cascade constraints defined at the object-domain-level are ignored. Bypassing object-domain constraints basically makes DQL a mere SQL abstraction rather than a fully-blown object-query-language aware of the object domain constraints. This is a very fundamental architectural tenet.

I will close this issue as invalid, because it does work as intended by the creators, even if the behaviour IMO constitutes a questionable design choice, being highly counter-intuitive without compensating for it by giving adequate other benefits to the user that would necessarily depend on that design choice.

Daniel Alvarez Arribas
added a comment - 27/Sep/11 2:36 PM Thanks for the clarification. Doctrine 2 really is one complex beast to tame.
Maybe a note in the architecture section could point out that anything involving DQL belongs to the database domain as opposed to the object domain. As DQL is toutet as an object-query-language it is quite contrary to the principle of least astonishment that cascade constraints defined at the object-domain-level are ignored. Bypassing object-domain constraints basically makes DQL a mere SQL abstraction rather than a fully-blown object-query-language aware of the object domain constraints. This is a very fundamental architectural tenet.
I will close this issue as invalid, because it does work as intended by the creators, even if the behaviour IMO constitutes a questionable design choice, being highly counter-intuitive without compensating for it by giving adequate other benefits to the user that would necessarily depend on that design choice.