Re: CakePHP 3.0 orm query question

Hi Jose,

I got it working as :

$query = $this->find('all', array(
'limit' => 200,
'order' => array(
'distance' => 'ASC',
'name' => 'ASC',
),
'conditions' => $conditions,
'fields' => ["name", "slug", "lat", "lng", "address", "city", "state", "postal_code", "phone", "distance" => "TRUNCATE((3958 * 3.1415926 * SQRT((`lat` - {$lat}) * (`lat` - {$lat}) + COS(`lat` / 57.29578) * COS({$lat} / 57.29578) * (`lng` - {$lng}) * (`lng` - {$lng})) / 180) * {$unit}, 5)"],
'having' => "distance <= $distance",
));

Just needed to use associative array, in the fields..


Thanks,

Andras

On Aug 26, 2014, at 1:10 AM, José Lorenzo <jose.zap@gmail.com> wrote:

> $query = $table->find()
> $query->select(['distance' => $query->newExpr($rawSQL)])->having(['distance <=' => $distance])
>
> On Monday, August 25, 2014 2:59:01 AM UTC+2, Andras Kende wrote:
> Hello,
>
> Im trying to convert this geolocator query from 2.5 to 3.0 orm but no luck so far.
>
> CakePHP 2.5, this works fine:
>
> $results = $this->find('all', array(
> 'limit' => 200,
> 'order' => array(
> 'distance' => 'ASC',
> 'name' => 'ASC',
> ),
> 'recursive' => -1,
> 'conditions' => "HAVING distance < $distance",
> 'fields' => "*, TRUNCATE((3958 * 3.1415926 * SQRT((`lat` - {$lat}) * (`lat` - {$lat}) + COS(`lat` / 57.29578) * COS({$lat} / 57.29578) * (`lng` - {$lng}) * (`lng` - {$lng})) / 180) * {$unit}, 5) AS `distance`"
> ));
>
> generated sql statement:
>
> SELECT *, TRUNCATE((3958 * 3.1415926 * SQRT((`lat` - 34.0522) * (`lat` - 34.0522) + COS(`lat` / 57.29578) * COS(34.0522 / 57.29578) * (`lng` - -118.244) * (`lng` - -118.244)) / 180) * 1, 5) AS `distance` FROM `hockeypickup`.`locations` AS `Location` HAVING distance < 100 ORDER BY `distance` ASC, `name` ASC LIMIT 200
>
>
> on CakePHP 3.0 this is far as I got :
>
> $query = $this->find('all', array(
> 'limit' => 200,
> 'order' => array(
> // 'distance' => 'ASC',
> 'name' => 'ASC',
> ),
> 'conditions' => $conditions,
> 'select' => "*, TRUNCATE((3958 * 3.1415926 * SQRT((`lat` - {$lat}) * (`lat` - {$lat}) + COS(`lat` / 57.29578) * COS({$lat} / 57.29578) * (`lng` - {$lng}) * (`lng` - {$lng})) / 180) * {$unit}, 5) AS Locations.distance",
> // 'having' => "`distance` <= $distance",
> ));
>
> distance is not getting recognized so far...
>
>
> Thank you,
>
> Andras
>
> --
> 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 unsubscribe from this group and stop receiving emails from it, send an email to cake-php+unsubscribe@googlegroups.com.
> To post to this group, send email to cake-php@googlegroups.com.
> Visit this group at http://groups.google.com/group/cake-php.
> For more options, visit https://groups.google.com/d/optout.

--
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 unsubscribe from this group and stop receiving emails from it, send an email to cake-php+unsubscribe@googlegroups.com.
To post to this group, send email to cake-php@googlegroups.com.
Visit this group at http://groups.google.com/group/cake-php.
For more options, visit https://groups.google.com/d/optout.