Symfony World blog is not maintained anymore. Check new sys.exit() programming blog.

Doctrine SoftDelete behavior usage

Scene from "The Neverending Story" by Wolfgang Petersen (1984)

SoftDelete

Recently, I created a detailed list of Doctrine behaviors ready to use in symfony projects. Among them, there is the SoftDelete behavior I want to focus on this time. For a chosen model, it adds a deleted_at column, which defines if a record has been marked as deleted (and if so, when).


where to use it?

Suppose your system needs to store all the detailed data history, every modification has to be marked and stay there forever. And even if the application allows users to delete objects, they are not really deleted - it is just an abstraction layer - in fact, objects are marked as deleted, but they stay in the database (and the application treats them as if they were deleted). This is where SoftDelete comes handy.


the code

All you gotta do is just to state that a given model is SoftDelete:

GivenModel:
  actAs:
    SoftDelete: ~
After this, the deleted_at column is added (of course, both to SQL and PHP class). But that's not all the job. Unfortunately, you have to tell the application how to use this column manually. Fortunately, this is really easy. For example, if you have an admin generated module, soft-deleted objects shall never appear in the list. Add/modify the buildQuery action of the admin module:
protected function buildQuery()
  {
    return parent::buildQuery()
      ->andWhere('deleted_at IS NULL');
  }
Additionally, if you want to have a full protection, you should update your admin module edit action to disable executing it for a soft-deleted object. So this was for the backend. For frontend, you shall modify your eventual data retrieving table class methods, like the following:
public function getObjectByIdQuery($id)
  {
    return Doctrine_Query::create()
      ->from('GivenModel gm')
      ->where('gm.id = ?', $id)
      ->andWhere('gm.deleted_at IS NULL');
  }
Of course, soft-deleted objects can NEVER be accessible from any type of frontend applications. There is no way to give a detailed list of modifications you need to provide 100% data protection, because all applications have different structure - you have to go through your functionalities on your own. These code lines above are just examples you will probably use.


enable DQL callbacks

Alternatively, you may enable DQL callbacks for your models in each of your config/ProjectConfiguration.class.php files:

public function configureDoctrine(Doctrine_Manager $manager)
  {
    // Enable callbacks so that softDelete behavior can be used
    $manager->setAttribute(Doctrine_Core::ATTR_USE_DQL_CALLBACKS, true);
  }
this will dynamically add an andWhere DQL clause to all SELECT queries for SoftDelete models (Doctrine_Template_Listener_SoftDelete::preDqlDelete()). Thanks to it, you don't have to manually update all possible SELECT queries in your project (such as buildQuery mentioned above).


be careful!

As the official behavior docs say, SoftDelete overrides the delete() method. When delete() is called, instead of deleting the record from the database, a deleted_at date is set. This means, if you try to delete records without using Doctrine_Record::delete() method, SoftDelete won't work! To provide 100% data protection covering SoftDelete you should replace all code like

Doctrine_Query::create()
  ->delete()
  ->from('GivenModel gm')
  ->where('gm.id = ?', $id)
  ->execute();
with
$objects = Doctrine_Query::create()
  ->from('Manufacturer m')
  ->where('m.id = ?', $id)
  ->execute();
 
foreach($objects as $object)
  $object->delete();
to make the Doctrine_Record::delete() be called whenever an object is supposed to be deleted, which finally means that the desired SoftDelete behavior is fired always when it should be.


objects related to SoftDelete-able objects

But there comes another question - what to do with related records of a soft-deleted object? For example, we have two model classes with the SoftDelete behavior: Manufacturer and Supplier which have a m:n relation table, ManufacturerSupplier which defines who supplies which manufacturers. If some specific manufacturer and supplier objects are marked as soft-deleted, their corresponding ManufacturerSupplier object (if it exists, let's suppose it does) is left and no one knows how to treat it. It is not marked as soft-deleted, since it hasn't got SoftDelete behavior. And it still exists in the database, like a full-fledged record. Probably, it does not raise any application problems, but there is a data consistency question - what does such m:n table record represent, when at least one of its related objects are soft-deleted? Shall it be soft-deleted after the related master object is soft-deleted? Or maybe hard-deleted?

16 comments:

  1. Actually you don't need to take care of the deleted elements by overriding the buildQuery() method.
    You can just enable DQL callbacks from the Doctrine configuration. In ProjectConfiguration.class.php, add:
    public function configureDoctrine(Doctrine_Manager $manager)
    {
    // Enable callbacks so that softDelete behavior can be used
    $manager->setAttribute(Doctrine_Core::ATTR_USE_DQL_CALLBACKS, true);
    }

    Then Doctrine automatically filters out the rows having a deleted_at that is not null.

    ReplyDelete
  2. Khepin, you're right. Thanks you for yur suggestion - the article is now updated.

    ReplyDelete
  3. 1) When DQL callbacks are enabled it is safe to try to delete records without using Doctrine_Record::delete(), because there is a method "preDqlDelete" in SoftDelete Listener and takes care of proper data handling.

    2) If delete time is irrelevant you can also configure SoftDelete to use boolean value is/isn't deleted (instead of timestamp).

    ReplyDelete
  4. your can enable DQL callbacks in database.yml (and other doctrine attributes):

    all:
    doctrine:
    class: sfDoctrineDatabase
    param:
    dsn: mysql:dbname=test;host=localhost
    username: test
    password: test
    attributes:
    default_table_collate: utf8_general_ci
    default_table_charset: utf8
    use_dql_callbacks: true

    ReplyDelete
  5. thanks for providing this useful tutorial!

    ReplyDelete
  6. See symfony schema.yml reference in addition to this tutorial: http://www.symfonyreference.com/schema

    ReplyDelete
  7. The correct link is
    http://www.symfonyreference.com/schema-yml

    ReplyDelete
  8. I Love this, i had implemented this manually and was WAY harder, Thanks!!

    ReplyDelete
  9. thanks a lot dude!

    ReplyDelete
  10. Hi, thanks for the tips !

    I would have a question about the way to use SoftDelete.

    In the application I am doing, I am using the SoftDelete on a table, but sometimes I might need to really delete a record from that table.

    I have a way to do it, but that seems a really bad manner as long as I have to do:

    Doctrine_Manager::getInstance()->setAttribute(Doctrine_Core::ATTR_USE_DQL_CALLBACKS, false);
    **do the deletion**
    Doctrine_Manager::getInstance()->setAttribute(Doctrine_Core::ATTR_USE_DQL_CALLBACKS, true);

    Is there a better of doing this ?

    ReplyDelete
  11. It's quite strange that you need SoftDelete and normal delete at the same time. Some model should be deleted either normally or softly. I guess there is no 'clean' solution here. You can try to remove records from the database, using raw SQL queries (but your solution seems better to me).

    By the way, could you describe why do you need to have a dynamic SoftDelete behavior for a model? I'm just curious...

    ReplyDelete
  12. Well, two things !

    First, your question made me re-thought about the business rules I had to achieve. In fact, as you mentionned, it seems weird to use SoftDelete in some cases and hard deletes in other cases on the same model.

    And the thing is I should not do that but rather :
    - unlink a given instance with other model instances ;
    - or hard delete my instance whenever it is not linked with other model instances.

    Second, I found a trick on a website to be able to easily query a table (in DQL) to include the soft deleted records. It may be useful to anybody using the SoftDelete behaviour !!

    http://blog.yoda-bzh.net/index.php?post/2010/04/06/Doctrine%3A-Howto-bypass-the-SoftDelete-behaviour

    ReplyDelete
  13. how does one configure softdelete? I see the file in lib/vendor/symfony/lib/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Template/softDelete.php. And in this file I see the options array. So I know I need to set those options some how. But we know we should not directly edit this file so I need to override this softDelete.php, but I do not know how to do that. Any info? Thanks!

    ReplyDelete
    Replies
    1. Hi...
      Did you get the solution for this?
      where to override the softdelete.php file?

      -Thanks,
      Monish

      Delete
  14. It's easy! It works the same way as other doctrine behaviors - modify schema YAML files:

    ProductQuantityHistory:
    __comment: "historia liczebności produktów"
    __actAs:
    ____Timestampable:
    ______updated:
    ________disabled: true
    ____Signable:
    ______updated:
    ________disabled: true

    and so a SoftDelete can be defined in the following way:

    ProductQuantityHistory:
    __comment: "historia liczebności produktów"
    __actAs:
    ____SoftDelete:
    ______name: is_deleted
    ______type: datetime

    PS sorry for the delay - I've been on a vacation ;)

    ReplyDelete
  15. i found a solution for the relations (cascade) at http://blog.elinkmedia.net.au/2010/01/30/doctrine-tricks/

    here an example that worked for me:

    # a question has many answers
    Question:
    actAs:
    SoftDelete: ~
    columns:
    title: string(200)
    relations:
    Answers:
    class: Answer
    local: id
    type: many
    foreign: question_id
    cascade: [delete]

    Answer:
    actAs:
    SoftDelete: ~
    columns:
    title: string(200)
    question_id: integer

    ReplyDelete