Stop ACTA

Simple CoalesceBehavior for CakePHP

Posted in CakePHP on 22.09.2008.

Although I won't be needing it any time soon, I've decided to create a tiny CoalesceBehavior, utilizing the SQL COALESCE function.

I feel like COALESCE is a less known but much more powerful SQL function than the is null and is_null versions. For those of you who are unfamiliar with it, I recommend you read about it, it can be very helpful.

Now, onward to the behavior itself.

My knowledge in writing behaviors is limited, so the code might be terrible. I've only tested it for a few times, so it's not bulletproof - use with care.

Apparently, the beforeFind() is not called when a related model is queried. This is logical (I assume), but doesn't make it possible to use this behavior to coalesce columns from different models. Bummer. If you have an idea how to accomplish this, please let me know, I'd be glad to update the behavior.

The usage is fairly simple:

class Tab extends AppModel
{
    var $name = 'Tab';
    var $useTable = 'tab';

    var $actsAs = array
        (
            'Coalesce' => array
                (
                    'userDisplayName' => array
                        (
                            'full_name',
                            'nickname',
                            'username'
                        )
                )
        );
}

If you do a find() for this model like this:

$this->Tab->find
    (
        'all',
        array
        (
            'fields' => array
                (
                    'userDisplayName',
                    'username'
                )
        )
    );

Assuming your data looks like this:

full_name nickname username
Augustus Fink-Nottle Gussie gus
null Jeeves jeeves
null null wooster

Your results should look somewhat like this:

Array
{
    [0] => Array
    {
        [Tab] => Array
        {
            'userDisplayName'   => 'Augustus Fink-Nottle',
            'username'          => 'gus'
        }
    },
    [1] => Array
    {
        [Tab] => Array
        {
            'userDisplayName'   => 'Jeeves',
            'username'          => 'jeeves'
        }
    },
    [2] => Array
    {
        [Tab] => Array
        {
            'userDisplayName'   => 'wooster',
            'username'          => 'wooster'
        }
    }
}

If you know COALESCE does, this result is pretty self-explanatory.

The behavior is available for download here:

If you have any ideas for improvement or have any issues with the behavior, drop me a note and I'll see what I can do :-)

Happy baking!

Article comments — View · Add


No comments!