Cakephp virtualFields model not working through

Question

I have a model called User that has a Virtual field called full_name . When I access my User model in a find () query, I can set the conditions in my virtual field without any problems:

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

The above query will successfully return me the data for user Bruce Thomas. But the problem occurs when I try to use my User model through another model through Containable, for example:

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

(The example above assumes MyOtherModel has belongsTo relationship to my MyOtherModel model)

In the above query, the following error occurs:

Warning (512): SQL Error: 1054: Unknown column "User.full_name" in the "on" section [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 above), this virtual field limitation is what it says :

The implementation of virtualFields has several limitations. At first, you cannot use virtualFields for related models for conditions, order, or field arrays. This will result in an SQL error, because the fields are not replaced by ORM. This is due to the fact that it is difficult to estimate the depth at which the corresponding model can be found. A common way to solve this problem is to copy virtual fields from one model to another at run time, 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 plan to implement your options (which look fine to me), you should take a look at the section "Virtual fields and model aliases "to avoid clashes with field names (i.e. if two models have a field called full_name and try to issue a query that uses both options, you will get an ambiguous SQL error field that is avoided with aliases).

Answer 2

In your model, you need to create a virtual field using the following code:

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

);

Now you just need to call the code below in your controller state array:

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