cakephp model virtualFields not working via contain

Question

I have a model called User which has a Virtual field called full_name. When I access my model User in a find() query, I can set conditions on my Virtual field with no problem like this:

$user = $this->User->find('first', array(
    'recursive' => -1, 
    'conditions' => array(
        'User.full_name' => 'Bruce Thomas'
    )
));

The query above will successfully return me the data for the user called Bruce Thomas. But the problem arises when I try to use my model User through another model via the Containable behavior like this:

$user = $this->MyOtherModel->find('first', array(
    'contain' => array('User'),
    'conditions' => array(
        'MyOtherModel.id' => $my_other_model_id
        'User.full_name' => 'Bruce Thomas'
    )
));

(This example above assumes that MyOtherModel has a belongsTo relationship with my model MyOtherModel)

The query above gives me the following error:

Warning (512): SQL Error: 1054: Unknown column 'User.full_name' in 'on clause' [CORE\cake\libs\model\datasources\dbo_source.php, line 681]

Any help on how I can do this please? Thank you

Answer 1

According to the latest CakePHP documentation (for v2 and up), that is a limitation of virtual fields - this is what it says:

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;

More details here: http://book.cakephp.org/2.0/en/models/virtual-fields.html - if you're planning on implementing the options they suggest (which look quite alright to me) you should take a look at the "Virtual fields and model aliases" section to avoid field name clashes (i.e. if you have a field called full_name in two models and try issuing a query that uses both, you'll get an ambiguous field SQL error; that is avoided with aliases).

Answer 2

In your model you have to create virtual field using below code :

public $virtualFields = array(
    'full_name' => 'CONCAT(YourModelName.first_name, " ", YourModelName.last_name)'

);

Now You simply need to call the below code inside your controller's condition array :

$condition=array($this->YourModelName->virtualFields['your_virtual_field_name'].' LIKE "%'.$YourSearchKeyword.'%"');