Select in select query cakephp <p>I have 2 table </p> <pre> 1. feeds => id,name 2. feed_locations => id, feed_id, latitude,longitude,location_name </pre> <p>This is my cakephp code</p> <pre><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 &lt;'.$radious ); </code> </pre> <p>It give me output query as</p> <pre><code>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 &lt; 10 ORDER BY `distance` ASC </code></pre> <p>My query is working but issue it that :</p> <p>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. <br/> I want to implement this mysql query in cakephp syntax</p> <pre><code>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 &lt; 10; </code></pre> <p>This query give me exact result. please let me know how can i use this query in cakephp</p>