CakePHP puts quotes around the function name in the WHERE clause
I am using various functions from the earthdistance module in PostgreSQL, one of which is ll_to_earth
. Given the latitude / longitude combination, I am trying to get the closest 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'),
));
The SQL output (formatted for readability) ends 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
You can see where my problem is: CakePHP is quoting my search terms including the function name ll_to_earth
. I just need to manually query the Model method query
, or is there a way to tell Cake what ll_to_earth
is a DB function and not quote it?
source to share
You should do it like this:
$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'),
));
But it also comes without all the sanitation that Cake does with the entrance, so you'll have to take care of that yourself.
source to share