One of the big fallacies of working with an ORM is that behind the scenes there may be high number of queries being executed to support the application. For example, say you want to delete several messages from a user’s inbox. We create a simple form; message subject, time message was sent, status (read, unread) and a checkbox. The checkbox is used for selecting specific messages upon which we wish to conduct a specific action, let’s say delete for example. If I select ten messages at a time, for delete, and follow the normal ORM process, I would have to load each message object, then call the delete() method for each one. If you tail your query log you will notice that you just issued twenty queries; ten queries to load the message, then ten messages to delete the object. Why all this overhead when we can use MySQL’s ‘IN’ clause and issue only one delete query?

At first I added a ‘status_id’ column for each message, which would indicate if the message was in a ‘deleted’ state, otherwise known as a “soft delete”. But then I remembered that Doctrine offers this behavior via the schema config file using the ActAs: SoftDelete construct. When done reading I learned that I needed to add a ‘deleted_at’ column to my user_message table to support Doctrine’s soft delete behavior. After some thought I agreed that this would be a better approach, as opposed to using a ‘status_id’ column. Mainly because there is a precedent regarding datetime columns and Doctrine behaviors, for example created_at and updated_at; timestampable behavior.

Unfortunately, in order to take advantage of the SoftDelete behavior, I would have to issue the aforementioned twenty queries, but this time when I call the delete method on each object, it would only update the deleted_at column, vs doing a hard delete (removing it from the table permanently). So how could I take advantage of the SoftDelete behavior AND using only one query, after some tinkering I came up with the following DQL statement:

public function unlink(array $messageIds)
    $q = Doctrine_Query::create()
        ->set('deleted_at',  new Doctrine_Expression('NOW()'))
        ->whereIn('id', $messageIds)

Notice the name of the method, I prefer using link/unlink naming convention, to indicate that I may be deleting one or many rows, and the method is strategically placed within the correct class, in case you have a pivot table breaking up a M:M relationship, or on a source table with no regard to any ancillary tables.

With the above example, you now only need issue one query to affect multiple rows, and still use the MySQL server’s timezone, vs using PHP date functions which rely on the local systems timezone.