Database agnostic virtual fields in CakePHP

Question

In CakePHP 1.3 there is a feature for virtual fields but it's coupled with the database that you are using. For example:

var $virtualFields = array(
  'full_name' => 'CONCAT(User.first_name, " ", User.last_name)'
);

This would work for MySQL but not for MS SqlServer. Is there a way to make this database agnostic?

I'm still in the middle of developing an application and still not sure what database we'll be using in production. That's why I want to keep all the database access as agnostic as possible.

Answer 1

You could dimension your Model::virtualFields property such that it had rules for each database:

var $virtualFields = array(
    'mysql' => array(
        'display_name' => 'CONCAT(User.name, " (", User.team, ")")',
    ),
    'postgres' => array(
        'display_name' => 'PgConcatStuff(...)',
    ),
    'mssql' => array(
        'display_name' => 'MsConcatStuff(...)',
    ),
);

The trick then is to catch the above property and manipulate it on-the-fly so Cake never realises:

class AppModel extends Model {

    function beforeFind($queryData) {
        $ds = $this->getDataSource();
        $db = $ds->config['driver'];
        $this->virtualFields = $this->virtualFields[$db];
        return parent::beforeFind($queryData);
    }

The above code was tested with a simple find on a single model. More testing may be needed to check for edge-cases involving related models. The finished functionality belongs in a behavior. :)