Virtual fields¶

Creating virtual fields¶

Creating virtual fields is easy. In each model you can define a $virtualFields property that contains an array of field => expressions. An example of a virtual field definition using MySQL would be:

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

And with PostgreSQL:

public $virtualFields = array(
    'name' => "User.first_name || \' \' || User.last_name"
);

In subsequent find operations, your User results would contain a name key with the result of the concatenation. It is not advisable to create virtual fields with the same names as columns on the database, this can cause SQL errors.

It is not always useful to have User.first_name fully qualified. If you do not follow the convention (i.e. you have multiple relations to other tables) this would result in an error. In this case it may be better to just use first_name || \' \' || last_name without the Model Name.

Using virtual fields¶

Creating virtual fields is straightforward and easy, interacting with virtual fields can be done through a few different methods.

// Will return false, as there is no concrete field called name
$this->User->hasField('name');
// Will return true as there is a virtual field called name
$this->User->hasField('name', true);

$this->User->isVirtualField('name'); //true
$this->User->isVirtualField('first_name'); //false

//returns 'CONCAT(User.first_name, ' ', User.last_name)'
$this->User->getVirtualField('name');

$results = $this->User->find('first');

// results contains the following
array(
    'User' => array(
        'first_name' => 'Mark',
        'last_name' => 'Story',
        'name' => 'Mark Story',
        //more fields.
    )
);

Virtual fields and model aliases¶

When you are using virtualFields and models with aliases that are not the same as their name, you can run into problems as virtualFields do not update to reflect the bound alias. If you are using virtualFields in models that have more than one alias it is best to define the virtualFields in your model’s constructor:

public function __construct($id = false, $table = null, $ds = null) {
    parent::__construct($id, $table, $ds);
    $this->virtualFields['name'] = sprintf(
        'CONCAT(%s.first_name, " ", %s.last_name)', $this->alias, $this->alias
    );
}

This will allow your virtualFields to work for any alias you give a model.

Pagination and Virtual fields set in controller with JOINS¶

The following example allows you to have a counter of a hasMany association and enables you to use virtual fields. For example if you had the following sort link in your template:

// Create a sort link for a virtual field
$this->Paginator->sort('ProductsItems.Total','Items Total');

You could then use the following pagination setup in your controller:

$this->Products->recursive = -1;

// Products hasMany associations ProductsItems
$this->Products->ProductsItems->virtualFields['Total'] = 'count(ProductsItems.products_id)';

// Where ORM
$where = array(
    'fields' => array(
        'Products.*',
        'count(ProductsItems.products_id) AS ProductsItems__Total',
    ),
    'joins' => array(
        array(
            'table' => 'products_items',
            'alias' => 'ProductsItems',
            'type' => 'LEFT',
            'conditions' => array(
                'ProductsItems.products_id = Products.id',
            )
        )
    ),
    'group' => 'ProductsItems.products_id'
);

// Set conditions Paginator
$this->paginate = $where;

// Get data
$data = $this->Paginator->paginate();

would return something like this:

Array
(
    [0] => Array
        (
            [Products] => Array
                (
                    [id] => 1234,
                    [description] => 'Text bla bla...',
                )
             [ProductsItems] => Array
                 (
                     [Total] => 25
                 )
        )
     [1] => Array
        (
            [Products] => Array
                (
                    [id] => 4321,
                    [description] => 'Text 2 bla bla...',
                )
             [ProductsItems] => Array
                 (
                     [Total] => 50
                 )
        )
 )

Virtual fields in SQL queries¶

Using functions in direct SQL queries will prevent data from being returned in the same array as your model’s data. For example this:

$this->Timelog->query(
    "SELECT
        project_id, SUM(id) as TotalHours
    FROM
        timelogs
    AS
        Timelog
    GROUP BY
        project_id;"
);

would return something like this:

Array
(
    [0] => Array
        (
            [Timelog] => Array
                (
                    [project_id] => 1234
                )
             [0] => Array
                 (
                     [TotalHours] => 25.5
                 )
        )
 )

If we want to group TotalHours into our Timelog array we should specify a virtual field for our aggregate column. We can add this new virtual field on the fly rather than permanently declaring it in the model. We will provide a default value of 0 in case another query attempts to use this virtual field. If that were to occur, 0 would be returned in the TotalHours column:

$this->Timelog->virtualFields['TotalHours'] = 0;

In addition to adding the virtual field we also need to alias our column using the form of MyModel__MyField like this:

$this->Timelog->query(
    "SELECT
        project_id, SUM(id) as Timelog__TotalHours
    FROM
        timelogs
    AS
        Timelog
    GROUP BY
        project_id;"
);

Running the query again after specifying the virtual field should result in a cleaner grouping of values:

Array
(
    [0] => Array
        (
            [Timelog] => Array
                (
                    [project_id] => 1234
                    [TotalHours] => 25.5
                )
        )
)

Limitations of virtualFields¶

The implementation of virtualFields has a few limitations. First you cannot use virtualFields on associated models for conditions, order, or fields arrays. Doing so will generally result in an SQL error as the fields are not replaced by the ORM. This is because it difficult to estimate the depth at which an associated model might be found.

A common workaround for this implementation issue is to copy virtualFields from one model to another at runtime when you need to access them:

$this->virtualFields['name'] = $this->Author->virtualFields['name'];

or:

$this->virtualFields += $this->Author->virtualFields;