Re: Problem with ordering by a calculated field

I would start the find on Postcode and contain the User

Would do a $locations = find('all' ….) as its easier to debug($locations) , once thats working correctly then change it to paginate...

try order : 'order' => 'distance ASC'

Andras Kende

On Jan 29, 2012, at 3:35 PM, Daniel wrote:

> I am trying to sort some query results using a distance field which is
> calculated from longitude and latitude fields as follows:
>
> $this->paginate = array(
> 'User' => array(
> 'recursive' => -1,
> 'limit' => 10,
> 'conditions' => $this->User->parseCriteria($this->passedArgs),
> 'contain' => array(
> 'Postcode' => array(
> 'fields' => array(
> 'latitude',
> 'longitude',
> '(3963.0*ACOS(SIN('.$lat.'*PI()/180)*SIN(latitude*PI()/180)+COS('.
> $lat.'*PI()/180)*
> COS(latitude*PI()/180)*COS((longitude*PI()/180)-('.$lon.'*PI()/
> 180))
> )) AS distance'
> )
> )
> ),
> 'order' => array('distance' => 'asc'),
> )
> );
>
> "latitude" and "longitude" are fields on the Postcodes table. $lon
> and $lat are variables. The problem is that the order by clause is
> ignored, it does not appear on the SQL outputted in the debugger.
> Also if I try and output the 'distance' field in the view using either
> $user['User']['distance'] or $user['Postcode']['distance'] or
> $user['distance'] I get an error:
>
> Undefined index: distance [APP\View\Users\search.ctp, line 30]
>
> Any ideas as how to solve this, a different approach perhaps?
>
> Thanks.
>
> --
> Our newest site for the community: CakePHP Video Tutorials http://tv.cakephp.org
> Check out the new CakePHP Questions site http://ask.cakephp.org and help others with their CakePHP related questions.
>
>
> To unsubscribe from this group, send email to
> cake-php+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php

--
Our newest site for the community: CakePHP Video Tutorials http://tv.cakephp.org
Check out the new CakePHP Questions site http://ask.cakephp.org and help others with their CakePHP related questions.

To unsubscribe from this group, send email to
cake-php+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php