cakephp - how to structure this query

Question

I'm using cakephp 2.3 and trying to paginate a query which retrieves all my 'jobs' within a certain mile radius. Luckily I found the query I think I need, but I'm having problems getting it to get the right data.

EDIT So each 'job' record has a lat and lng field and another user performs a search by entering their own lat and lng values as well as a mile radius in a search form. In the code below the php variables $lat $lng and $miles in my paginator below are the user entered search parameters.

The query, which I found here https://developers.google.com/maps/articles/phpsqlsearch_v3?hl=el-GR - should look like:

    SELECT id, 
           ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance 
      FROM markers 
    HAVING distance < 25 
  ORDER BY distance LIMIT 0 , 20;

and I've got that going through my paginate like so:

$this->Paginator->settings = array(
                                'fields'=>array('id','( 3959 * acos( cos( radians(37) ) * cos(radians( '.$lat.' ) ) * cos( radians( '.$lng.' ) - radians(-122) ) + sin( radians(37) ) * sin(radians( '.$lat.' ) ) ) ) AS distance '),
                                'limit' => 20,
                                'group'=>'distance having distance < '.$miles
                                        );
$jobs = $this->Paginator->paginate('Job');

and the sql that produces looks like:

SELECT `Job`.`id`, ( 3959 * acos( cos( radians(37) ) * cos(radians( 54.59728500000001 ) ) * cos( radians( -5.930119999999988 ) - radians(-122) ) + sin( radians(37) ) * sin(radians( 54.59728500000001 ) ) ) ) AS distance FROM `trainnation`.`jobs` AS `Job` LEFT JOIN `trainnation`.`users` AS `User` ON (`Job`.`user_id` = `User`.`id`) LEFT JOIN `trainnation`.`courses` AS `Course` ON (`Job`.`course_id` = `Course`.`id`) WHERE 1 = 1 GROUP BY distance having distance < 100 LIMIT 20

which looks right to me but doesn't pull any info at all. There should be at least 9 records which are well within 100 miles of that location but $jobs is always empty.

Can anyone spot what I'm doing wrong?

Have you tried running that created query directly from your MySQL workbench or PhpMyAdmin or cli? If so, does it say anything? — Jelmer

C Travel C Travel · Accepted Answer · 2015-04-24T09:53:01

Try this, i had the same problem. I removed some ( & ) in the select part.

SELECT id, 3959 * acos(cos(radians(37)) * cos(radians(lat)) * cos(radians(lng) - radians(-122)) + sin(radians(37)) * sin(radians(lat))) AS distance 
FROM markers 
HAVING distance < 25 
ORDER BY distance LIMIT 0 , 20;

cakephp - how to structure this query

2 Answers