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?

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')
));