Well, I tried a couple of different things.
First, I tried going the virtual field route and got the same results
(classification id and name appearing outside of the main array):
------------------------------------------------------
//create the virtual field
$this->virtualFields['miles'] = "(3956 * 2 * ATAN2(SQRT(POW(SIN......
//filter on classification id
array_push($conditions, array('classification_id' => $classification_id));
//filter on specialty id (if needed)
if(!empty($specialty_id)) {
array_push($conditions, array('specialty_id' => $specialty_id, 'specialty_'
. $classification_name => 1));
}
//perform query
$results= $this->Find('all', array('conditions'=>$conditions,
'order'=>'miles')); //I also tried 'order'=>'Location.miles' and
'order'=>'Location__miles'
------------------------------------------------------
Then, I decided to try adding the fields that I need to filter on
(classification and specialty) as virtual fields too, and this DID work:
------------------------------------------------------
//create virtual fields
$this->virtualFields['miles'] = 0;
$this->virtualFields['classification_id'] = 0;
$this->virtualFields['classification_name'] = '';
//build out the sql string
$sql = "SELECT (3956 * 2 * ATAN2(SQRT(POW(SIN((RADIANS(".$lat_lon....
//if we need to filter on specialty, add another virtual field at this time
and append to the sql string
if(!empty($specialty_id)) {
$this->virtualFields['specialty_id'] = 0;
$this->virtualFields['specialty_name'] = '';
$sql .= "AND (specialty_id = $specialty_id) ";
}
//finish off the sql string by adding the ORDER BY clause
$sql .= "ORDER BY Location.miles ASC";
//perform query
$results= $this->query($sql);
------------------------------------------------------
So, while I'm happy that the 2nd scenario works, I'm completely baffled as
to WHY I would need to do it that way. It seems like a lot of hoops to jump
through to do something that *seems* like it should be fairly
straightforward. I have a feeling that there's something really obvious
that I'm doing wrong, but I'm at a loss as to what it might be.
On Wednesday, October 17, 2012 1:58:39 AM UTC-7, euromark wrote:
>
> why not using virtual fields?
> then there won't be any odd format returned by find()
>
> Am Mittwoch, 17. Oktober 2012 07:37:15 UTC+2 schrieb Andras Kende:
>>
>> Here is similar query, maybe helpful:
>>
>> $unit = ($unit == 'km') ? 1.609344 : 1 ;
>>
>> $results = $this->Location->find('all', array(
>> 'limit' => 100,
>> 'order' => array(
>> 'distance' => 'ASC',
>> 'Location.name' => 'ASC',
>> ),
>> 'recursive' => -1,
>> 'conditions' => '1 = 1 HAVING `distance` <= 50' ,
>> 'fields' => "*, TRUNCATE((3958 * 3.1415926 * SQRT((`lat` - {$lat}) *
>> (`lat` - {$lat}) + COS(`lat` / 57.29578) * COS({$lat} / 57.29578) * (`lng`
>> - {$lng}) * (`lng` - {$lng})) / 180) * 1.609344, 5) AS `distance`"
>> ));
>>
>>
>> Or you could foreach your results and move from ,classifications to
>> Locations...
>>
>>
>> Andras Kende
>> http://www.kende.com
>>
>> On Oct 16, 2012, at 6:43 PM, bmcelhany <[email protected]> wrote:
>>
>> 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]> 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].
>>> > To unsubscribe from this group, send email to
>>> > [email protected].
>>> > 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.
>>
>>
>>
>>
>>
--
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.
