Access multiple foreign fields in a Virtual Field?

Question

Two tables, with the relationship set up properly in Cakephp:

Property (..., postcode_id,...) and Postcode (id, postcode, latitude, longitude).

A Virtual Field created in Property beforeFind() calculates the distance between a search postcode and the record postcode. Therefore, to get the lat/long, I need to retrieve a record from the Postcode table by the foreign key in the Property record, all within the virtual field declaration.

How can I do this? Multiple SELECTs nested within? Nested find(), which would probably be worse? I thought just using RelatedModel.fieldName would work - it doesn't.

EDIT: Property belongsTo Postcode, Postcode hasMany Property

(Using cakephp v2.4.4)

The Virtual Field in Property beforeFind():

$this->virtualFields['distance'] = 6371 . ' * ACOS(
    (SIN(RADIANS(Postcode.latitude)) * ' . $searchPostcode['latitudeSin'] . ') +
    (COS(RADIANS(Postcode.latitude)) * ' . $searchPostcode['latitudeCos'] . ') *
     COS(RADIANS(Postcode.longitude) - ' . $searchPostcode['longitude'] . ')
)';

This does not answer your question directly, but did you look into the bakery at this behavior that you may be able to leverage for solving distances with zip codes — AgRizzo
Thanks, but I'm not sure how much work would be needed for CakePHP 2 compatibility. Either way, I'm hoping there's something more general that I can use here for any calculated field with this issue, not just geographic ones. — StringsOnFire

Kai Kai · Accepted Answer · 2014-01-30T18:24:04

You'll need to do a manual join between the table you're searching and the one that holds the lng/lat, for example, $options['joins'] = array(array('table' => 'model2', 'alias' => 'Model2', 'conditions' => array('Model2.model1_id = Model1.id'))); and then in your virtual field, I think you should be able to use Model2.lat and Model2.lng and have it work.

Access multiple foreign fields in a Virtual Field?

2 Answers