Using virtual fields in cakePHP 2.x

Question

I've read through the documentation and struggled to understand what to do. Also, I've read through the questions here on stackoverflow, and nothing that I tried helped.

I've got a drop down that I want to list all employees in the company. The list should be displayed like this:

Name Surname (Job Title)

In my Model, I have this piece of code:

public $virtualFields = array(
    'fullname' => 'CONCAT(HrEmployee.name, " ", HrEmployee.surname, " (", HrEmployee.jobTitle, ")")'
);

And in my controller, I have this:

$hrEmployees = $this->User->HrEmployee->find('fullname',
    array(
        'fields' => array('HrEmployee.name','HrEmployee.surname','HrEmployee.jobTitle'),
        'order' => array('HrEmployee.name'=>'ASC','HrEmployee.surname'=>'ASC')
));

But I get this error:

Error: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS `User__fullname` FROM `intraweb_db`.`users` AS `User` WHERE `User`.`hr_emp' at line 1

What must I change? I can see that it's building the query, but it's changing it horribly bad...

Can anyone assist?

Dualpurpose

Answer 1

Cool so I fixed it. Partially thanks to Brandon for pointing me in the right direction.

Because of the virtual fields limitation, I had to do the workaround.

So, in my HrEmployee model I did this:

public $virtualFields = array(
    'fullname' => 'CONCAT(HrEmployee.name, " ", HrEmployee.surname, " (", HrEmployee.jobTitle, ")")'
);

And in my User model, I changed it to this:

class User extends AppModel {
public function __construct($id = false, $table = null, $ds = null) {
    parent::__construct($id, $table, $ds);
    $this->virtualFields['fullname'] = $this->HrEmployee->virtualFields['fullname'];
}

And lastly, in my UsersController, I just changed it a bit:

$hrEmployees = $this->User->HrEmployee->find('list',
    array(
        'fields' => array("id","fullname"),
        'order' => array('HrEmployee.name ASC','HrEmployee.surname ASC')
));

Dualpurpose

Answer 2

The bottom of the cake document specifies some limits of virtual fields..

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.

http://book.cakephp.org/2.0/en/models/virtual-fields.html#limitations-of-virtualfields

Bice

Thanks. This answer pointed me in the right direction. See my answer. – Dualpurpose

Glad I could partially help, and thanks for posting your solution! – Bice