PHP – Doctrine – Soft Delete – Multiple Rows at a Time


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.


PHP – Doctrine – DQL – Select Subquery


Whilst working on a recent project I needed the ability to do a subquery look-up as part of a select statement. Basically I needed to count the number of rows, without having to do a group by on the entire result set. After a few minutes of research I noticed there was no real definitive source on how to do do a subquery as a select using DQL, although there were some references to using a subquery within a where clause.

Below is a code snippet which worked for me:

$q = Doctrine_Query::create()

// You can put this anywhere within the select clauses, but must be done before  you make call to from()        
$subQ = $q->createSubquery()
    ->from('Table3 t3')
    ->innerJoin('t3.Table4 t4')
    ->innerJoin('t4.Table5 t5')            
    ->where('t4.status_id = :statusId')
    ->andWhere(' = t2.code_id');
$q->addSelect(sprintf('(%s) AS my_count', $subQ->getDql()))
    ->from('Table1 t1')
    ->innerJoin('t1.Table2 t2')
    ->where('t2.user_id = :userId', array('statusId' => $statusId, 'userId' => $userId);

// And you should now be able to access the count value using:
$result = $q->execute();


// Depending on how you have your models setup, you may have to do this instead:

Note, the table names are abstract for obvious reasons, but the snippet should demonstrate the ability to add a select subquery to a dql statement.

Here’s another example usage:

$q = Doctrine_Query::create()
$subQ = $q->createSubquery()
    ->from('UserHash uh2')
    ->where('uh2.hash = :hash');
$q->addSelect(sprintf('(%s) AS derived_user_id', $subQ->getDql()))
    ->from('UserHash uh1')            
    ->where('uh1.type_id = :typeId')
    ->having('uh1.user_id = derived_user_id', array('hash' => $hash, 'typeId' => $typeId));
return $q->execute();

PHP – ZipArchive – 5.3.x – Weird Issue when Unlinking a File Just Added to Archive


At work, I was tasked with porting some cronjobs from an old server (php 5.2.x) to a new server (php 5.3.x) and ran into a weird issue. The code to be ported was explicitly unlinking a file which was just added to the ZipArchive, in efforts to keep the filesystem clean, and ran fine on php 5.2.x, but when I ported the same code over to php 5.3.x I couldn’t get the zip file to write in it’s entirety.

Below is the code snippet:

$zip = new ZipArchive;

$zip->open('/path/to/', ZipArchive::OVERWRITE);

foreach ($files as $file) {

    $zip->addFile((string $file));

    // This is the offending line
    unlink((string) $file);

After I commented out the unlink, everything worked as expected. Thought I would bring it up in case anyone else faces the same situation.


PHPUnit – You must not expect the generic exception class


While working on updating my unit tests from PHPUnit v3.2 to v3.6, I came across the error message; “You must not expect the generic exception class”, upon first glance it sort of made sense, but after some thought and investigation, it really doesn’t make much sense. My preference is to throw exceptions, and codes, versus throwing specific exceptions, and the code which threw the error was doing just that, throwing a generic exception with an exception code. I spoke with the PHPUnit developer via github, and he stated that a fix is in 3.7 but will not added in 3.6 (current stable). Well I can’t wait for 3.7 to be released so I came up with the following solution:

 * @expectedException Test_Exception
 public function testDivByZero()
     try {
         // Fyi you don't need to do an assert test here, as we are only testing the exception, so just make the call
         $result = $this->object->div(1,0);
     } catch (Exception $e) {
         if ('Exception' === get_class($e)) {
             throw new Test_Exception($e->getMessage(), $e->getCode());

// Test_Exception.php
class Test_Exception extends Exception
    public function __construct($message = null, $code = 0, Exception $previous = null)
        parent::__construct($message, $code, $previous);

Basically all I did was create my own exception class, then when testing if an api call throws an exception, I would check the class of the exception, and if it was a ‘generic’ (Exception) class, then I would wrap it within the custom, expected Test_Exception.


PHP – Correctly Cast Numeric Strings to Numeric Datatypes


Recently I posted an article on how to compare two doctrine objects @ which works well in most cases, but ran into an issue yesterday where floats as strings were being set to 0 due to the (int) cast. This was a problem because on the website, I store ingredient portions in a float (decimal) format, which means that my object comparisons were not being evaluated correctly if I changed the ingredient portions from .5 to .66 as they would both be cast to int with a value of 0.

So I was working on a possible solution and came across a great little hack in the comments section of the docs for the is_numeric function @, in the comments, a user (, pointed out that you can add ‘+0′ to a string which evaluated to true using the is_numeric function and it will correctly cast the value of the string to the proper datatype, which worked well in converting string floats to float floats. So the revised code would look like:

$objectArray1 = array_map(function($value) { return (is_numeric($value)) ? ($value + 0): $value; }, $object1->toArray(false));

PHP – Reflection Class – Determine Parent Method Signature


As I was writing unit tests against a new caching wrapper which extends the Memcached API, I ran across an issue when overriding the Memcached::get() method. According to PHP docs regarding the Memcached::get() method, there are three arguments which which must be added to my extended signature, which I added, but kept getting “… should be compatible with that of Memcached::get()” errors. I tried looking for the method signature via source code but didn’t glean anything useful from the PECL documentation, so I turned to PHP’s RelectionClass to see if I could figure out what I was missing from my extending class signature which was causing the aforementioned errors. After a few minutes I ended up with the following code snippet:

$this->cache = Cache::factory(Cache::TYPE_VOLATILE);

$reflector = new ReflectionClass(get_class($this->cache));

foreach ($reflector->getMethod('get')->getParameters() as $param) {
    var_dump((string) $param);

Which outputted the following:

string(32) "Parameter #0 [ <required> $key ]"
string(37) "Parameter #1 [ <optional> $cache_cb ]"
string(39) "Parameter #2 [ <optional> &$cas_token ]"

After a few seconds investigating the output, I noticed that I wasn’t passing the third argument ($cas_token) by reference, but before I fixed my version I double checked the PHP docs regarding Memcached::get() and in fact noticed they indicated that $cas_token was indeed being passed by reference (as indicated by the ampersand &). After I altered my extended method to pass the third argument by reference, everything worked as expected. So if you ever need to introspect an API with little to no relevant documentation, try using PHP’s ReflectorClass to get the information you are after.


PHP – Manual Compilation – ld returned 1 exit status


If you are compiling PHP manually and compilation prematurely halts due to a “ld returned 1 exit status” try issuing the following command from within the build directory, I was running into the same issue and the command worked for me.

$ -> gmake distclean

PHP – Magento – Zend – Missing Hash Function


When it comes to manually configuring compiled applications on my systems, I am a minimalist, meaning that I always try to get away with the bare bones settings. Once in a great while this will come back to haunt me. In starting up a new project, I came across an issue where the Magento install process was throwing a fatal error regarding a ‘hash’ function (Zend Framework actually needed it). So I had to recompile with `–enable-hash` setting. Be sure to run a make clean before configure, otherwise your new setting won’t be picked up.


PHPUnit – How to Run PHPUnit Against a Specific Test


The other day I was debugging an error in one of my unit tests, and found it hard to track down because when I ran PHPUnit, it ran all the tests contained in the file where my problem unit test was located. After some Googling and reading the PHPUnit Api Docs, I found that you can specify a test, among other tests, by adding a comment with the @group annotation. Using @group and any group name you wish, you can tell PHPUnit, from the command line, to test only tests belonging to a specific group.

Consider the following:

public function testArrayCount()
    $this->assertCount(2, array('foo', 'bar');

 * @group grain
public function testArrayPopulated()

Notice how I specified the testArrayPopulated() method with a @group grain annotation, now I can tell PHPUnit to only test tests belonging to the grain group.

./phpunit --colors --group grain path/to/your/tests/

Now, when PHPUnit runs, it will only run tests against those tests to which you have assigned the grain group. This @group annotation is a really cool feature of PHPUnit and helped me a lot with debugging my unit tests.


Per Edo’s comments: You can also filter tests by adding ‘–filter testYourTestName’ (two hypens). This will make it so only that test or any test matching the string will be run, same benefits as group, but easier to implement. It does require you to put some thought into how you name the methods within your test.

Per Patrick’s comment: I corrected the order for the command line arguments. The options should ALWAYS go before the path to your tests.

Thanks for the feedback.


PHP – mysql_real_escape_string vs PDO::prepare()


I was recently made aware of some subtle differences between mysql_real_escape_string and PDO::prepare and thought I would pass on a great article stating why PDO::prepare() is preferred over m_r_e_s. If you are starting a new project or working on an existing project, and you are not using an ORM, I’d suggest using PDO for native SQL constructs vs the mysql_* family of commands, not only for the reasons stated in the linked article, but also for ease of use, and portability.

Go to Top