Hello,
I have a model (Location) that points to a view (locations) in MySQL. The view contains address, zip code and latitude/longitude data that I use to calculate the distance between two locations (or find all locations within a given radius from a particular zip code).
In my model, I have a function that takes 3 parameters: a zip code, a classification id and a specialty id. Using those three parameters, I construct a sql string ($sql), call $this->query($sql) and return the resulting array. The reason I simply don't call the model's standard $this->find() method is because, in addition to the fields from the database view, I need to perform a huge and ugly calculation to determine the number of miles each record's zip code is from the zip code passed in to the function. I'm using this as a virtual field in the model called "Miles".
At any rate, this is mostly working but I'm noticing some strange behavior with how the resulting array is structured. Any time I add something to the WHERE clause of my sql statement and have an ORDER BY clause in the sql statement, those items are returned in an array that is separate from my expected Location array. If I don't add any fields to my WHERE clause, everything is returned as expected (just with no filters applied to the result set). Oddly, if I remove the ORDER BY clause everything works fine too. It's only when I ORDER BY my Miles virtual field AND have stuff in my WHERE clause that things start to break.
I should note too, that when I look at the actual query that is being run and copy it into MySQL, it runs fine and returns the correct results.
Here is the relavant code ($lat_lon['lat'] and $lat_lon['lon'] are from a local array and are returning the correct numbers):
-----------------------------------------------------------------
$this->virtualFields['Miles'] = 0;
$sql = "SELECT "
."Location.classification_id, "
."Location.classification_name, "
."Location.first_name, "
."Location.last_name, "
."Location.suffix, "
."Location.title, "
."Location.specialty_id, "
."Location.specialty_name, "
."Location.specialty_ame, "
."Location.specialty_pr4, "
."Location.address_id, "
."Location.address, "
."Location.city, "
."Location.state, "
."Location.zip, "
."Location.address_ame, "
."Location.address_pr4, "
."Location.zip_code, "
."Location.lat, "
."Location.lon, "
."(3956 * 2 * ATAN2(SQRT(POW(SIN((RADIANS(".$lat_lon['lat'].") - "
.'RADIANS(Location.lat)) / 2), 2) + COS(RADIANS(Location.lat)) * '
."COS(RADIANS(".$lat_lon['lat'].")) * POW(SIN((RADIANS(".$lat_lon['lon'].") - "
."RADIANS(Location.lon)) / 2), 2)), SQRT(1 - POW(SIN((RADIANS(".$lat_lon['lat'].") - "
."RADIANS(Location.lat)) / 2), 2) + COS(RADIANS(Location.lat)) * "
."COS(RADIANS(".$lat_lon['lat'].")) * POW(SIN((RADIANS(".$lat_lon['lon'].") - "
."RADIANS(Location.lon)) / 2), 2)))) AS Location__Miles "
."FROM locations AS Location "
."WHERE "
."(lat BETWEEN ROUND({$lat_lon['lat']} - (25 / 69.172), 4) "
."AND ROUND({$lat_lon['lat']} + (25 / 69.172), 4) "
."AND lon BETWEEN ROUND({$this->lon} - ABS(25 / COS({$lat_lon['lat']}) * 69.172)) "
."AND ROUND({$lat_lon['lon']} + ABS(25 / COS({$lat_lon['lat']}) * 69.172)) "
."AND 3956 * 2 * ATAN2(SQRT(POW(SIN((RADIANS({$lat_lon['lat']}) - "
."RADIANS(Location.lat)) / 2), 2) + COS(RADIANS(Location.lat)) * "
."COS(RADIANS({$lat_lon['lat']})) * POW(SIN((RADIANS({$lat_lon['lon']}) - "
."RADIANS(Location.lon)) / 2), 2)), SQRT(1 - POW(SIN((RADIANS({$lat_lon['lat']}) - "
."RADIANS(Location.lat)) / 2), 2) + COS(RADIANS(Location.lat)) * "
."COS(RADIANS({$lat_lon['lat']})) * POW(SIN((RADIANS({$lat_lon['lon']}) - "
."RADIANS(Location.lon)) / 2), 2))) <= $miles "
."AND 3956 * 2 * ATAN2(SQRT(POW(SIN((RADIANS({$lat_lon['lat']}) - "
."RADIANS(Location.lat)) / 2), 2) + COS(RADIANS(Location.lat)) * "
."COS(RADIANS({$lat_lon['lat']})) * POW(SIN((RADIANS({$lat_lon['lon']}) - "
."RADIANS(Location.lon)) / 2), 2)), SQRT(1 - POW(SIN((RADIANS({$lat_lon['lat']}) - "
."RADIANS(Location.lat)) / 2), 2) + COS(RADIANS(Location.lat)) * "
."COS(RADIANS({$lat_lon['lat']})) * POW(SIN((RADIANS({$lat_lon['lon']}) - "
."RADIANS(Location.lon)) / 2), 2))) >= 0) "
."AND (classification_id = $classification_id) ";
if(!empty($specialty_id)) {
$sql .= "AND (specialty_id = $specialty_id) ";
$criteria['Specialty'] = $specialty_name;
}
$sql .= "ORDER BY Location__Miles ASC";
$foundData = $this->query($sql);
echo pr($foundData);
-----------------------------------------------------------------
The $foundData array looks like:
Array
(
[0] => Array
(
[classifications] => Array
(
[classification_id] => 1
[classification_name] => AME
)
[Location] => Array
(
[first_name] => John
[last_name] => Doe
[suffix] =>
[title] => PHD
[specialty_ame] => 1
[specialty_pr4] => 1
[address_id] => 32
[address] => 123 Main St. Suite 100
[city] => Sacramento
[state] => CA
[zip] => 94203
[address_ame] => 1
[address_pr4] => 1
[zip_code] => 94203
[lat] => 38.5844
[lon] => -121.494
[Miles] => 0.00017830548973979952
)
.
.
.
But SHOULD look like:
Array
(
[0] => Array
(
[Location] => Array
(
[classification_id] => 1
[classification_name] => AME
[first_name] => John
[last_name] => Doe
[suffix] =>
[title] => PHD
[specialty_ame] => 1
[specialty_pr4] => 1
[address_id] => 32
[address] => 123 Main St. Suite 100
[city] => Sacramento
[state] => CA
[zip] => 94203
[address_ame] => 1
[address_pr4] => 1
[zip_code] => 94203
[lat] => 38.5844
[lon] => -121.494
[Miles] => 0.00017830548973979952
)
.
.
.
If I comment out the line that reads: ."AND (classification_id = $classification_id) "; it works fine (no classification_id filter, but the array is structured correctly). Also, if I comment out the $sql .= "ORDER BY Location__Miles ASC"; line it works fine (filters are all in place and things are structured correctly, but I need the results ordered by distance). I just can't use them together.
It seems like this should just work...what am I missing here? Any help is greatly appreciated!!!
-- Like Us on FaceBook https://www.facebook.com/CakePHP
Find us on Twitter http://twitter.com/CakePHP
---
You received this message because you are subscribed to the Google Groups "CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com.
To unsubscribe from this group, send email to cake-php+unsubscribe@googlegroups.com.
Visit this group at http://groups.google.com/group/cake-php?hl=en.
No comments:
Post a Comment