Working with virtual fields in Cake1.3

In earlier versions of CakePHP we had to use Behaviors like "MultipleDisplayFields" or whatever.
With $Model->virtualFields it is now possible to natively use find() methods combined with virtual fields.
The nice thing about it is, that the pagination can work with those fields, too. You can even sort on them in paginated views. A huge advantage over earlier Cake versions.

An example (User model):

$virtualFields = array('fullname'=>'CONCAT(User.firstname, " ", User.lastname)'); // "john doe"

It has several downsides, though.
Virtual fields do not work across models. They cannot be used in combination with "fields" in the find() options.
More on it can be found in the official cookbook.

I tried to come up with a solution that fixes some of those incapabilities.

Introducing the custom model method

$fields = am(array('username', 'email'), $this->User->virtualFields('fullname'));
$user = $this->User->find('first', array('fields'=>$fields, 'conditions'=>...));

This allows us to only fetch specific "fields" while including the virtualField "fullname"

$fields = $this->User->virtualFields(array('fullname', 'OtherUser.fullname'));
$fields = am(array('User.id', 'OtherUser.id'), $fields);
$user = $this->User->find('first', array('contain'=>array('OtherUser'), 'fields'=>$fields, ...));

Virtual fields of other models are also no problem. In this case I simply joined the model User to itself (hasOne OtherUser).
The result would be sth like:

array (
  'User' => 
  array (
    'id' => '2',
    'fullname' => 'First+Last',
  ),
  'Test' => 
  array (
    'id' => '2',
    'fullname' => 'First-Last',
  ),
);

Note the difference in the join part (+/-). It shows that the query works (OtherUser has – and User has + as joining piece).

A little bit trickier were the temporary virtual fields. Those could be passed along and would be used only for this query (TODO! right now they are added to virtualFields but not removed anymore).

$fields = $this->User->virtualFields(array('fullname', 'othername'=>'CONCAT('.$this->User->alias.'.firstname, "-", '.$this->User->alias.'.lastname)', 'OtherUser.othername'=>'CONCAT('.$this->OtherUser->alias.'.firstname, "*", '.$this->OtherUser->alias.'.lastname)'));
$fields = am(array('User.id', 'OtherUser.id'), $fields);
$user = $this->User->find('first', array('contain'=>array('Test'), 'fields'=>$fields, 'order'=>array()));

The result again – as expected:

array (
  'User' => 
  array (
    'id' => '2',
    'fullname' => 'First+Last',
    'othername' => 'First-Last',
  ),
  'OtherUser' => 
  array (
    'id' => '2',
    'othername' => 'First*Last',
  ),
)

Even plugin models should work (not tried yet) with

$this->User->virtualFields(array('Plugin.PluginModel.fullname'));

Although the "Plugin" part usually can be omitted if the relation from User to PluginModel is already established (and the User model can access the model property without initializing the foreign model).

Code

The code to be placed in your AppModel:

/**
 * combine virtual fields with fields values of find()
 * USAGE:
 * $this->Model->find('all', array('fields' => $this->Model->virtualFields('full_name')));
 * Also adds the field to the virtualFields array of the model (for correct result)
 * TODO: adding of fields only temperory!
 * @param array $virtualFields to include
 * 2011-10-13 ms
 */
public function virtualFields($fields = array()) {
    $res = array();
    foreach ((array)$fields as $field => $sql) {
        if (is_int($field)) {
            $field = $sql;
            $sql = null;
        }
        $plugin = $model = null;
        if (($pos = strrpos($field, '.')) !== false) {
            $model = substr($field, 0, $pos);
            $field = substr($field, $pos+1);
    
            if (($pos = strrpos($model, '.')) !== false) {
                list($plugin, $model) = pluginSplit($model);
            }
        }
        if (empty($model)) {
            $model = $this->alias;
            if ($sql === null) {
                $sql = $this->virtualFields[$field];
            } else {
                $this->virtualFields[$field] = $sql;
            }
        } else {
            if (!isset($this->$model)) {
                $fullModelName = ($plugin ? $plugin.'.' : '') . $model;
                $this->$model = ClassRegistry::init($fullModelName);
            }
            if ($sql === null) {
                $sql = $this->$model->virtualFields[$field];
            } else {
                $this->$model->virtualFields[$field] = $sql;
            }
        }
        $res[] = $sql.' AS '.$model.'__'.$field;
    }
    return $res;
}

If anyone sees a cleaner approach etc, let me know 🙂