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] <javascript:>>
> 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]<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.
