Virtual query fields in CakePHP

This basically compliments the article about virtual entity fields.
Note that those entity ones are not creating custom SQL queries, but work/operate on the existing entity data.

Now, the query ones, however, usually involve custom SQL snippets to fetch the data exactly the way needed for the query itself, or the following code using it.

Why query instead of entity?

Entity based operations have limits. They can only use the existing data inside each entity. If you need to order or filter multiple result sets based on this, then such a virtual field is not useful.

Often you want to use "concat" (concatenation) or calculation operations here that are needed to prepare the data for the proper order and filtering that you want to apply.
Custom functions explains how to build such a custom field for your select().

Let’s use concat() as a demo for now. We create a ->func()->concat() expression and assign this to a key "filter". That one we also use to order descending.
We can also through in a custom DATE() SQL modifier for fun (that one we would also do as pure entity field since we are not sorting on it).

$query = $this->Countries->find();
$myFilterConcat = $query->func()->concat([
    'iso2' => 'identifier',
    '-',
    'iso3' => 'identifier',
    '-',
    'name' => 'identifier',
]);

$countries = $query
    ->select([
        'id',
        'my_filter' => $myFilterConcat,
        'modified_date' => 'DATE(modified)',
    ])
    ->orderDesc('name');

// Let's just get the first one of this collection
$country = $countries->firstOrFail();

This would return as the entity hydrated with the actual field "id", and the two virtual fields.

App\Model\Entity\Country {
  #_fields: array:3 [▶]
  id: 245
  name: "ZW-ZWE-Simbabwe"
  published_date: "2010-06-06"
}

No need for any code/config inside your entity, since those will automatically be available now.
From entity perspective, this is not a virtual field, but a real field.

If you format this using $country->toArray(), those are usually visible right away, if you use * => true as default accessibility:

array [
  "id" => 245
  "name" => "ZW-ZWE-Simbabwe"
  "modified_date" => "2010-06-06"
]

IDE Support

Since those new virtual query fields will not be known to the IDE for now, we need to manually add them to the entity:

/**
 * @property int $id
 * @property string $name
 * ...
 * @property string|null $my_filter
 * @property string|null $modified_date
 */
class Country extends Entity {
}

I made them nullable as they usually do not exist unless you specifically add those into the query.
If you make sure all your queries contain them, you can omit the nullable part here.

Now the IDE can autocomplete those, too. And PHPStan/tooling will be able to understand them and their type for static analysis even.

$this->doSomething($country->my_filter, $country->modified_date);

Summary

Virtual query fields can often be much slower than entity ones, especially if not needed a lot – or in all cases.
Entity ones are calculated only if needed (called), query ones always – to be included in the result set directly and patched into the entity right away.
So use entity virtual fields if the existing data is enough and you do not need custom SQL changes to your data for filtering/sorting.
Once you see the need for this, use virtual query fields.
As soon as you involve group(), having(), distinct() or any multi-row SQL function like max(), average(), count(), sum(), you will most certainly need the query ones.