mysql - Select in select query cakephp

Question

I have 2 table

   1. feeds => id,name
   2. feed_locations => id, feed_id, latitude,longitude,location_name

This is my cakephp code

    $radious    =   '10';
    $this->paginate = array(
        'joins' => array(
            array('table' => 'feed_locations',
                'alias' => 'FeedLocation',
                'type' => 'LEFT',
                'conditions' => array(
                    'FeedLocation.feed_id = Feed.id',
                )
            )
        ),
        'limit'      => 10,             
        'fields' => array('id','name','(3959 * acos (cos ( radians('.$lat.') ) * cos( radians( FeedLocation.latitude ) ) * cos( radians( FeedLocation.longitude ) - radians('.$lng.') ) + sin ( radians('.$lat.') ) * sin( radians( FeedLocation.latitude )))) AS `distance`'),
        'recursive' => -1,
        'order'    =>  'distance ASC',
        'group' => 'Feed.id HAVING distance <'.$radious          
    );

It give me output query as

SELECT 
    `Feed`.`id`, 
    `Feed`.`name`, 
    (3959 * acos (cos ( radians(40.7127837) ) * cos( radians( FeedLocation.latitude ) ) * cos( radians( FeedLocation.longitude ) - radians(-74.00594130000002) ) + sin ( radians(40.7127837) ) * sin( radians( FeedLocation.latitude )))) AS `distance`, 
    (Select COUNT(id) FROM feed_locations WHERE feed_id = `Feed`.`id`) AS `location_count` FROM `feeds` AS `Feed` 
    LEFT JOIN `feed_locations` AS `FeedLocation` ON (`FeedLocation`.`feed_id` = `Feed`.`id`) 
    GROUP BY `Feed`.`id` HAVING distance < 10 
    ORDER BY `distance` ASC

My query is working but issue it that :

Like if a single feed have 10 location like 1m,2m,3m,4m,5m,10m,100m distance. and i want to find 5m distance all feed then it works but it shows me that this feed have 5m. distance from me but result should be 1m distance.
I want to implement this mysql query in cakephp syntax

SELECT 
    a.id,
    a.name,
    a.distance,
    a.location_count
    FROM
      (SELECT
        `Feed`.`id`, 
        `Feed`.`name`, 
        (3959 * acos (cos ( radians(40.7127837) ) * 
        cos( radians( FeedLocation.latitude ) ) * 
        cos( radians( FeedLocation.longitude ) -
        radians(-74.00594130000002) ) + sin ( radians(40.7127837) ) * 
        sin( radians( FeedLocation.latitude )))) AS `distance`, 
          (Select COUNT(id) 
             FROM feed_locations 
             WHERE feed_id = `Feed`.`id`) AS `location_count` 
             FROM `feeds` AS `Feed` 
             LEFT JOIN `feed_locations` AS `FeedLocation` ON (`FeedLocation`.`feed_id` = `Feed`.`id`) 
        ORDER BY `distance` ASC) AS a
        GROUP BY a.id HAVING distance < 10;

This query give me exact result. please let me know how can i use this query in cakephp

Answer 1

In CakePHP you can also use prepared statements, in which you can use SQL to create your queries, check out the docs: http://book.cakephp.org/2.0/en/models/retrieving-your-data.html#prepared-statements