CakePHP在WHERE子句中的函数名称引用引号 [英] CakePHP putting quotes around function name in WHERE clause
问题描述
我使用PostgreSQL中的earthdistance模块中的各种函数,其中之一是 ll_to_earth
。给定一个纬度/经度组合,我试图通过CakePHP从我的数据库检索最近的点1.2.5稳定。
I'm using various functions from the earthdistance module in PostgreSQL, one of those being ll_to_earth
. Given a latitude/longitude combination, I'm trying to retrieve the nearest point from my database via CakePHP 1.2.5 Stable.
// set dummy data
$latitude = 30.4393696;
$longitude = -97.6200043;
// create a simple bounding box in the WHERE conditions, sort the points by location, and retrieve the nearest point
$location = $this->Location->find('first', array(
'fields' => array(
'id',
'coords',
"earth_distance(coords, ll_to_earth($latitude, $longitude)) AS distance",
),
'conditions' => array(
'coords >=' => 'll_to_earth(' . ($latitude - 0.5) . ', ' . ($longitude - 0.5) . ')',
'coords <=' => 'll_to_earth(' . ($latitude + 0.5) . ', ' . ($longitude + 0.5) . ')',
),
'order' => array('distance ASC'),
));
SQL输出(为了更容易阅读而格式化)看起来像这样:
The SQL output (formatted for the sake of easier reading) ends up looking like this:
SELECT
"Location"."id" AS "Location__id",
"Location"."coords" AS "Location__coords",
earth_distance(coords, ll_to_earth(30.4393696, -97.6200043)) AS distance FROM "locations" AS "Location"
WHERE
"coords" >= 'll_to_earth(29.9393696, -97.1200043)' AND
"coords" <= 'll_to_earth(30.9393696, -96.1200043)'
ORDER BY
"distance" ASC
LIMIT 1
你可以看到我的问题在哪里:CakePHP引用我的查找条件,包括函数名 ll_to_earth
。我只需要通过模型的查询
方法手动查询,或者有一种方法告诉Cake, ll_to_earth
是一个数据库函数,而不是引用它?
You can see where my problem lies: CakePHP is quoting my find conditions, including the function name ll_to_earth
. Will I just need to manually query via the Model's query
method, or is there a way to tell Cake that ll_to_earth
is a DB function and to not quote it?
推荐答案
你应该可以这样做:
$location = $this->Location->find('first', array(
'fields' => array(
'id',
'coords',
"earth_distance(coords, ll_to_earth($latitude, $longitude)) AS distance",
),
'conditions' => array(
'coords >= ll_to_earth(' . ($latitude - 0.5) . ', ' . ($longitude - 0.5) . ')
and coords <= ll_to_earth(' . ($latitude + 0.5) . ', ' . ($longitude + 0.5) . ')'),
'order' => array('distance ASC'),
));
但这也会绕过Cake对输入做的所有清理,所以你必须照顾自己。
But that will also bypass all the sanitizing that Cake is doing with the input so you will have to take care of that yourself.
这篇关于CakePHP在WHERE子句中的函数名称引用引号的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!