Search code examples
mysqlcakephpormcakephp-3.0sql-function

Is it possible to define virtual fields (SQL function calls) in entities?

Is their way, we can add MySQL custom function in Entity

protected $_virtual = ['check_tenant' => '(check_tenant(Tenants.id))'];

I would like to call following query in with find() method

//SELECT id, first_name, check_tenant(Tenants.id) FROM tenants AS Tenants

$this->Tenants->find()->all();

If I can define custom MySQL function in the virtual field then it would automatically return in the result set

I am able to pass the new field in select() method

$this->Tenants->find()
->select(['id', 'check_tenant' => '(check_tenant(Tenants.id))'])->all();

But i would like to define globally, so the new field don't need to pass in every find call

Solution

  • Virtual properties in CakePHP 3.x are not the same as virtual fields in CakePHP 2.x, the latter were used in SQL queries, and the former are being used on PHP level, usually with data already present in the entity.

    If you want your custom field to be present in all queries, then you could for example use the Model.beforeFind() event to modify the queries accordingly:

    // in TenantsTable class

public function beforeFind(\Cake\Event\Event $event, \Cake\ORM\Query $query, array $options)
{
    return $query
        // select custom fields (functions builder usage not required, but advised)
        ->select(function (\Cake\ORM\Query $query) {
            return ['check_tenant' => $query->func()->check_tenant([
                'Tenants.id' => 'identifier'
            ])];
        })
        // ensure that the tables default fields are being selected too
        ->enableAutoFields(true); // autoFields() before CakePHP 3.4
}

    Another less invasive option would be custom finders, that you explicitly use where you need them:

    // in TenantsTable class

public function findWithTenantCheck(\Cake\ORM\Query $query, array $options)
{
    return $query
        ->select(/* ... */)
        ->enableAutoFields(true);
}

    // query data

$query = $this->Tenants->find('withTenantCheck');

    See also