Created by Pushnell, 29th Mar 2012. (originally Lighthouse ticket #35):
SHORT VERSION:
The postgresql 'cube' data type comparison operator '@>' is not recognized by Cake, and it therefore incorrectly inserts '=' into the where clause and breaks it.
Example:
'conditions' => array("earth_box(ll_to_earth(43.0367907637681, -89.4037345224754), 8047) @>" => 'earthloc')
Resulting SQL:
... WHERE earth_box(ll_to_earth(43.0367907637681, -89.4037345224754), 8047) @> = 'earthloc'
Expected SQL:
... WHERE earth_box(ll_to_earth(43.0367907637681, -89.4037345224754), 8047) @> 'earthloc'
Error:
SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "WHERE" LINE 1
DETAILS:
I'm using the earthdistance module with Postgresql to search by distance from a location. ( http://www.postgresql.org/docs/9.1/static/earthdistance.html ) This uses the 'cube' data type to store earth locations ( http://www.postgresql.org/docs/9.1/static/cube.html ).
One of the cube comparison operators is '@>', which means 'is contained by'. So 'A @> B' means cube B is contained by cube A. This operator is not properly recognized in a Model::find() conditions statement, so cake adds '=' and breaks the query.
In case you were wondering, those are the lat/lng coordinates for the capitol building in Madison, WI, and the query is searching for entries within 5 miles (8047 meters) of that location. Performing this with ::query() works as expected.
Created by Pushnell, 29th Mar 2012. (originally Lighthouse ticket #35):
SHORT VERSION:
The postgresql 'cube' data type comparison operator '@>' is not recognized by Cake, and it therefore incorrectly inserts '=' into the where clause and breaks it.
Example:
Resulting SQL:
Expected SQL:
Error:
DETAILS:
I'm using the earthdistance module with Postgresql to search by distance from a location. ( http://www.postgresql.org/docs/9.1/static/earthdistance.html ) This uses the 'cube' data type to store earth locations ( http://www.postgresql.org/docs/9.1/static/cube.html ).
One of the cube comparison operators is '@>', which means 'is contained by'. So 'A @> B' means cube B is contained by cube A. This operator is not properly recognized in a Model::find() conditions statement, so cake adds '=' and breaks the query.
In case you were wondering, those are the lat/lng coordinates for the capitol building in Madison, WI, and the query is searching for entries within 5 miles (8047 meters) of that location. Performing this with ::query() works as expected.