Doctrine Gotchas – delete cascade, SoftDelete and foreignType

These are mistakes anyone can make, as proved by our team of experienced developers who only came across these late into a project – after developing with Doctrine for over 6 months.

Cascading / Transitive persistence

In Doctrine there are two ways to define a cascade, one will tell Doctrine what to do, the other defines how that condition is set up on your database when Doctrine builds the sql commands. The reason this was a gotcha for me, is that we only ever used one of the types, and were completely unaware of the other one! The Doctrine manual is quite long, and unless you’ve read it several times cover to cover it’s easy to miss these things – although next time I will at least be reading the appropriate section of the manual, regardless of what I think I already know.

So, you want a delete cascade? You might do something like this in your yaml file:

      type: integer
      class:       User
      local:       user_id
      foreign:     id
      onDelete:    CASCADE

What the above definition says is that this phone number is connected to a user, and if the user record is deleted – in order to maintain database integrity with foreign keys, the corresponding phone number records should also be deleted. This is perfect behaviour – but Doctrine does not do anything with it, it simply sets up the database to handle it, so if you are using a dbms that supports delete cascades then everything will be handled at the database level.

Adding softDelete to the equation

So here comes the gotcha, all of our tables use the softDelete behaviour – which means that instead of the row being deleted, a flag is set (deleted=true) which then magically makes the row appear deleted in all your queries (Providing you have dql callbacks turned on). This means that the row is never actually deleted at the databse level, and thus the cascade is never applied. Now I always knew this, I mean I wouldn’t install a behaviour before knowing what it does right? What I wrongly assumed was that the call to onDelete: CASCADE would take care of “soft deleting” my relations too, but it doesn’t, in fact that line is effectively useless when combined with softDelete. For that you need to set the doctrine cascade parameter:

  actAs: [softDelete]
      type: integer
      class:       User
      local:       user_id
      foreign:     id
      foreignType: one
      cascade:     [delete]

Now the behaviour will be as expected. If you are using softDelete and your application has a lot of delete operations, you should take some time to understand what is happening, because Doctrine must fetch and load all of the related objects and then check their relations and cascade settings and so on, so in a large system deleting a single user could result in quite a lot of database queries and cpu time. It is for this reason that database level cascades are preferable, so maybe being more picky about which models you apply the softDelete behaviour would be prudent. Learn more about Doctrine Transitive Persistance.

Defining types of foreign key relations

This one is also one of those “read the manual” situations, but similarly to the above example what caught us out here is that we again thought that there was only one option for defining what we wanted to do, when in fact there were too parameters we should have been considering. This gotcha also only applies if you are defining your relations in a yaml file, as the problem lies in its interpretation when Doctrine builds the base classes.

Look at the following example:

      type: integer
      notnull: true
      class:       Payment
      local:       id
      foreign:     user_id
      foreignType: many

The intention was that a user could have multiple payments, and it seemed to make sense to define it using the foreignType parameter above, however when the model was built – this always resulted in a $this->hasOne(…) statement being created, because what was actually happening is that we were not defining the relationship at the other end, we were actually defining it at the local end! So foreignType: many actually means “Many users can have one payment” – hence the interpretation of $this->hasOne(..)

In actual fact using the foreignType key is mostly useless, as Doctrine “guesses” this side of the relationship. It’s only necessary if Doctrine guesses incorrectly and you need to override it, our example above should actually have been using the “type” parameter, which defines the relationship from the other perspective:

      type: integer
      notnull: true
      class:    Payment
      local:    id
      foreign:  user_id
      type:     many

Adding [ foreignType: one ] to this schema declaration would have no offect, as Doctrine would guess it to be the case (in this example). The above declaration will now correctly form a $this->hasMany(…) statement in your base class. To make it easy to remember, just forget about foreignType all together unless you come across a special case where you nee it – and prepend the word “has” to the type parameter, that should give you a clue as to what you are defining.

So, with one-to-one relationships, the type parameter is unnecessary, with one-to-many you specify the type: many (Doctrine will guess the one part) and with many-to-one you also do not need to specify the type as it will be guessed based on how you’ve got the relation set up from the other model’s perspective.

Read more about relations in the Doctrine manual.


it’s hard to believe that after 6 months of coding a large application that currently has over 30,000 lines of code and growing we could have a schema file with several useless calls to onDelete: CASCADE and even more useless calls to foreignType. It’s even harder to believe that in the early days of the project, we simply overrode the setup methods to add the relations that we needed, when the Schema.yml file failed to come up with the goods. Next time I think I’ll spend a little more time with the documentation, although we are of course experts now – so maybe next time we won’t need to? 😉

18 Comments so far

  1. Giorgio Sironi on May 27th, 2009

    Nice… The fact that even with foreignType/Alias only one Relation object is created was also a gotcha for me.

  2. vince on June 1st, 2009

    Nice tips ! 🙂

  3. tomwys on July 15th, 2009

    Thx. Bookmarked.

  4. Stereoscott on November 7th, 2009

    I really learned a lot from this post, thank you! I had a few questions: 1) using symfony where is a logical place to turn on DQL callbacks? In the global project configuration file? Or, is it ok to set it at the top of any methods in your class that rely on it? 2) in your “user has many payments” example, it looks to me like you’d end up with a single “payment_id” column in your user table… wouldn’t you rather just put a user_id in your payments table?

  5. Russ on November 7th, 2009

    @Stereoscott: In symfony, either per application (via applicationConfiguration.class.php) or normally for the entire project (projectConfiguration.class.php). I’d be careful about doing it per class, because some methods will call other methods in other classes, which may then call other queries. Since dql callbacks are turned on you may get unexpected results – it’s better to know what to expect across your entire app. Saying that, I have had cases where I’ve had to turn callbacks off for a specific query, then immediately following the query I’ll turn the setting back to it’s original state.

    $originalAttribute = Doctrine_Manager::getInstance()->getAttribute(Doctrine::ATTR_USE_DQL_CALLBACKS);
    Doctrine_Manager::getInstance()->setAttribute(Doctrine::ATTR_USE_DQL_CALLBACKS, false);
    // Query
    Doctrine_Manager::getInstance()->setAttribute(Doctrine::ATTR_USE_DQL_CALLBACKS, $originalAttribute);

    For the second point, you are right – the example does not really reflect a real life scenario, I’ll fix that in a bit!

  6. Grunzig on January 20th, 2010

    I tried softDelete with Doctrine 1.2 and symfony 1.3, but the principle of cascade delete is not working. I enabled the DQL callback and adds “cascade [delete]” in schema.yml. Have you any idea?

  7. Tom on January 25th, 2010

    Thanks for this post, having similar gotcha moments. Got to admit, it’s a bit confusing, even with the documentation.

  8. stephenrs on January 28th, 2010

    Hmmm, I was with you until I got to the “user has many payments” part…putting a payment_id in the user table will never give you the expected result, whether you use Doctrine or not…unless you plan to duplicate the same user record over and over again with different payment_id’s…

    The Payment table needs a foreign key to the User table: user_id. Relational DB design 101, no?

    This makes the whole post a little suspicious in terms of its usefulness, but thanks anyway.

  9. Russ on January 28th, 2010

    Hmm, I did mention a little while back (if you read upwards) that I would fix that – it’s done now.

    Suspicion is good – it makes better developers out of all of us.

  10. stephenrs on January 28th, 2010

    Ah, you’re right, sorry I missed that, and thanks for addressing it. Personally, I prefer to define Doctrine/yaml relations at the other end – for me it seems a little more intuitive and easier maintain, by reinforcing the idea that the child table is dependent on the parent, and not the other way around. Your mileage may vary.

  11. dirk on January 29th, 2010

    I would be interested in the Symfony and Doctrine versions where SoftDelete really works (I assume it does, otherwise you would not do this report.)
    In symfony 1.3.1 with Doctrine 1.2.1 with SoftDelete enabled and dql callbacks set to true doctrine marks deleted table entries correctly (deleted_ad column is filled with actual datetime)
    but that does not help much. The problem is: Doctrine queries (DQL) ignore the “deleted_at” field which means that you have to do the filtering manually in an extra where clause.
    So I do not see any reason why anyone should use this behaviour because you have to do the filtering on your own.

  12. Darren on February 6th, 2010

    Thanks for this information, it helped our company. Was weird because I kept getting integrity constraint violations and wondering why things weren’t cascading.

  13. Chris on February 25th, 2010

    @dirk Well, for me the “deleted_at” column in the DQL Queries works perfect. Are you shure that you have enabled dql callbacks correctly?

  14. benjamin on May 4th, 2010

    second hit in google for “actAs SoftDelete symfony”
    and worth reading it (-; – thx

  15. muqker on August 27th, 2010

    I think your example of cascade delete is misleading. In the first example, the “SQL foreign key” cascade deletion (onDelete: CASCADE) will trigger the SQL deletion of PhoneNumber when the parent User is deleted.
    However, in the second example, the “doctrine model callback” cascade deletion (cascade: [delete]) will trigger the Doctrine deletion (preDelete(), postDelete(), additional cascading, etc., followed by SQL deletion or soft deletion) of User when any child PhoneNumber is deleted. So the direction of the cascade is now reversed!

  16. logicrow on February 10th, 2011

    eat your monkeydust yourself.

  17. timbee on February 21st, 2011

    By the way, you can also turn on DQL Callbacks in the database.yml file (repost from stackoverflow’s jwconsulting):

    class: sfDoctrineDatabase
    dsn: mysql:host=host;dbname=db
    username: user
    password: password
    use_dql_callbacks: true

  18. Surinderpal Singh on January 5th, 2012

    Cascade delete option deletes only one associated record not all records

Leave a reply