Virtual Fields

In addition to accessing the fields existing in the database, a Model can be used to create virtual fields for consistent formatting of data that you don't want to store redundantly in your database or have to recreate each time you want to use it. Common examples are concatenated fields, such as a full name made up of a first and last name, as we've already created in Database and Model, or item identifiers that are combinations of unique ids and descriptive names.

Basic Virtual Fields

Virtual Fields are defined by the Entity, so you add the accessor functions into the Entity file. For our Users table we added the accessor function to the /src/Model/Entity/Users.php file. The function name convention is simply _getFieldName().

Once the accessor is added, the virtual field can be used for display anywhere in the application where the model is accessible.

// full_name virtual field
protected function _getFullName()
{
  return $this->first_name . ' ' . $this->last_name;
}

Then in our User Views, such as /templates/Users/index.php we can include the following to display our new field:

<?php echo $user->full_name; ?>

Calculated Virtual Fields

A useful and commonly used function is calculating ages from a date of birth. There is no reason to store both a date of birth and an age in a database when an age is easily calculated from the date of birth.

To store the Date of Birth in the database as a date, we need to update our table. First we'll create a migration with the change:

$ bin/cake bake migration AddDobToUsers

Add the column to your migration in /config/Migrations/##############_AddDobToUsers.php

public function change(): void
{
  $table = $this->table('users');
  $table->addColumn('dob', 'date');
  $table->update();
}

Run your migration to update the table:

$ bin/cake migrations migrate

Update your Model's Table validation at /src/Model/Table/UsersTable.php

public function validationDefault(Validator $validator): Validator
{
  ...
  $validator
    ->date('dob')
    ->allowEmptyDate('dob');
  ...

Now we can add the date to our records so we should update our User add and edit Views /src/templates/Users/add.php and /src/templates/Users/edit.php

...
echo $this->Form->control('dob', ['label' => 'Date of Birth']);
...

Update your Entity /src/Model/Entity/User.php to allow assigning values to the field and add the virtual field accessor:

class User extends Entity
{
  protected array $_accessible = [
    'dob' => true,
    ...
  ];
  ...
  protected function _getAge()
  {
    return is_null($this->dob) ? 0 : $this->dob->diff(\Cake\I18n\Date::now())->format('%y');
  }
}

Now you can easily access the age field using $user->age so lets add it to our Users view /src/templates/Users/view.php

  ...
  <tr>
    <th><?php echo __('Date of Birth (age)') ?></th>
    <td><?php echo h($user->dob) . " ($user->age)" ?></td>
  </tr>
  ...

Virtual Field Queries

In addition to concatenating existing fields and calculating values, virtual fields can be used to perform advanced queries on your data. It's generally not recommended to run database queries directly within an entity. Entities are intended to represent individual records and hold data, not perform database operations. However, cases exist where the data for a record must include advanced querying of other data.

The following example is used to calculate a sum of values by date.

First, add the following import to your Entity file:

use Cake\ORM\TableRegistry;

Then build your query in your function. This example grabs data from an annexations table and sums the data in the sq_miles field by effective_date:

// total_sq_miles virtual field
protected function _getTotalSqMiles()
{
  $annexations = TableRegistry::getTableLocator()->get('Annexations');
  $query = $annexations->find();
  $sum = $query->func()->sum('sq_miles');
  $results = $query->select(['total' => $sum])
    ->where([
      'effective_date <=' => $this->effective_date,
    ])
    ->first();
  return $results->total;
}