Wednesday, October 17, 2012

Re: Odd array structure when using Model->query()

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 <bmce...@gmail.com> 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 <bmce...@gmail.com> 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 cake...@googlegroups.com.
> To unsubscribe from this group, send email to
> cake-php+u...@googlegroups.com.
> 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 cake...@googlegroups.com.
To unsubscribe from this group, send email to cake-php+u...@googlegroups.com.
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 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.
 
 
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)