Unfortunately, that didn't change anything. On Tuesday, October 16, 2012 5:10:30 PM UTC-7, cricket wrote: > > Try adding the model alias: > > AND (Location.classification_id = $classification_id) > > On Tue, Oct 16, 2012 at 7:42 PM, bmcelhany <[email protected]<javascript:>> > wrote: > > 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]<javascript:>. > > > To unsubscribe from this group, send email to > > [email protected] <javascript:>. > > Visit this group at http://groups.google.com/group/cake-php?hl=en. > > > > >
-- 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.
