How to include associations in find() with fieldlist

Question

Hello. I want to receive model data by find(all), but the user should get only a restricted set of table fields. That's easy:

$ret = $this->find('all',array('fields'=>array(
    'Employee.id','Employee.address_id'
)));

But this model (Employees model) also has a belongsTo association:

var $belongsTo = array(
    'Address' => array(
        'className' => 'Address',
        'foreignKey' => 'address_id',
        'fields' => array('Address.full_name')
    )
);

I want the Address.full_name field to appear in my fetched data too. But it doesn't work with the find() call above, and it throws an error (SQL Error: 1054: Unknown column 'Address.full_name' in 'field list') when trying this:

'fields'=>array('Employee.id','Employee.address_id','Address.full_name')

Anyone knows how to solve this?

EDIT: I totally forgot that Address.full_name is a virtual field. Looking at the Cakephp-produced SQL, it's obvious why it doesn't work:

SELECT
    `Employee`.`id`, `Employee`.`address_id`, `Address`.`full_name`
FROM
    `employees` AS `Employee`
    LEFT JOIN `addresses` AS `Address`
        ON (`Employee`.`address_id` = `Address`.`id`)
WHERE 1 = 1

In the address model, full_name is defined like this:

var $virtualFields = array(
    'full_name' => 'CONCAT_WS(" ", Address.firstname, Address.surname)'
);

So then, the question is: Is it a CakePHP bug that it's not able to include (foreign model's) virtual fields within a fieldlist supplied to find()?

Answer 1

I would use the Containable behavior in this case.

Make sure you have the Containable behavior loaded in your Employee model first:

var $actsAs = array('Containable');

Then, when you're trying to get your data, do it like this:

$params = array('fields' => array('Employee.id', 'Employee.address_id'), 
    'contain' => array('Address' => array('fields' => array('Address.full_name')));
$ret = $this->find('all', $params);

More on the containable behavior here: http://book.cakephp.org/view/1323/Containable

Answer 2

SQL Error: 1054: Unknown column 'Address.full_name' in 'field list')

This error gives you a clue that something is amiss with either your column name call (could it be fullname rather than full_name) or, more likely your Model definition. Employee belongsTo an Address but does the Address haveOne or haveMany Employees?

Answer 3

Unfortunately, you cannot use virtual fields the way you wish to. From Limitations of Virtual Fields in the Cake documentation:

The implementation of virtualFields in 1.3 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.

It looks like you'll have to use the Containable behaviour.