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 [email protected].
To unsubscribe from this group, send email to
[email protected].
Visit this group at http://groups.google.com/group/cake-php?hl=en.
