RSS
 
13. Oct. 2011

Working with virtual fields in Cake1.3

13 Oct

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 :)

Working with virtual fields in Cake1.3
0 votes, 0.00 avg. rating (0% score)
 
1 Comment

Posted by Mark in CakePHP

 

Tags: ,

Leave a Reply

Tip:
If you need to post a piece of code use {code type=php}...{/code}.
Allowed types are "php", "mysql", "html", "js", "css".

Please do not escape your post (leave all ", <, > and & as they are!). If you have encoded characters and need to reverse ("decode") it, you can do that here!
 

 
  1. IT::fsinn » [KW41] Themen der Woche

    October 17, 2011 at 18:59

    [...] CakePHP 2.0 ist erschienen. Für 1.3 gibt&#8217;s noch gute Tipps, um mit virtuellen Feldern zu arbeiten. KategorienCakePHP, ePetition, Gedanken, Gesellschaft, IT::lastig, Mac, Piraten, Pol:t:k, [...]