访问虚拟字段中的多个外部字段？ [英] Access multiple foreign fields in a Virtual Field?

问题描述

两个表格，在Cakephp中正确设置关系：

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

属性（...， postcode_id ，...）

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

在属性beforeFind（）中创建的虚拟字段计算（ id 搜索邮政编码和记录邮政编码之间的距离。因此，要获取lat / long，我需要通过属性记录中的外键从所有在虚拟字段声明中的Postcode表中检索一个记录。

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.

我如何做到这一点？嵌套多个SELECT？嵌套find（），这可能会更糟？我认为只是使用RelatedModel.fieldName将工作 - 它不工作。

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.

编辑：属性属于邮政编码，邮政编码hasMany属性

Property belongsTo Postcode, Postcode hasMany Property

（使用cakephp v2.4.4）

(Using cakephp v2.4.4)

属性beforeFind（）中的虚拟字段：

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

推荐答案

我有它的工作， SELECT在每个外部调用中，这不能是最好的解决方案。例如

I have it working, but I had to nest another SELECT in each foreign call, which can't be the best solution. For example,

RADIANS((SELECT latitude FROM postcodes WHERE id = Property.postcode_id))

这也意味着相同的值，纬度，被检索两次，所以不是很大。

This also means that the same value, latitude, is being retrieved twice, so that's not great.

如果你有一个更好的解决方案，请分享！

Please share a better solution if you have one!

这篇关于访问虚拟字段中的多个外部字段？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！