title: Symfony2, Doctrine search and order by distance
body: <p>I have a database of places which I store the latitude and longitude for. I want to query the database to find all places within a radius (<code>$requestedDistance</code>) of a specific latitude (<code>$latitude</code>) and longitude (<code>$longitude</code>).</p>
  
  <p>The below query works and returns only those places within this radius, however how would I order them by distance so the closest is first? In the past, using raw SQL I have done the calculation within the SELECT statement and set it as 'distance' and then used <code>HAVING distance &lt; $requestedDistance ORDER BY distance</code>, however I'm not sure how to add the calculation to the SELECT query using Doctrine Repository.   </p>
  
  <pre><code>$d = $this-&gt;getDoctrine()-&gt;getRepository('XXXWebsiteBundle:Locations')-&gt;createQueryBuilder('l');
          $d
              -&gt;add('where','l.enabled = 1')
              -&gt;andWhere('( 3959 * acos( cos( radians('.$latitude.') )
                  * cos( radians( l.latitude ) )
                  * cos( radians( l.longitude )
                  - radians('.$longitude.') )
                  + sin( radians('.$latitude.') )
                  * sin( radians( l.latitude ) ) ) ) &lt; '.$requestedDistance);
  
         $result= $d-&gt;getQuery();
  </code></pre>
  
  <p><strong>UPDATE</strong></p>
  
  <p>I've tried the following query thanks to @Lazy Ants:</p>
  
  <pre><code>$d = $this-&gt;getDoctrine()-&gt;getRepository('XXXWebsiteBundle:Locations')-&gt;createQueryBuilder('l');
      $d
          -&gt;select('l')
          -&gt;addSelect(
              '( 3959 * acos(cos(radians(' . $latitude . '))' .
                  '* cos( radians( l.latitude ) )' .
                  '* cos( radians( l.longitude )' .
                  '- radians(' . $longitude . ') )' .
                  '+ sin( radians(' . $latitude . ') )' .
                  '* sin( radians( l.latitude ) ) ) ) as distance'
          )
          -&gt;andWhere('l.enabled = :enabled')
          -&gt;setParameter('enabled', 1)
          -&gt;andWhere('distance &lt; :distance')
          -&gt;setParameter('distance', $requestedDistance)
          -&gt;orderBy('distance', 'ASC');
  </code></pre>
  
  <p>However, it returns the following error:</p>
  
  <pre><code>`An exception occurred while executing 'SELECT COUNT(*) AS dctrn_count FROM 
  (SELECT DISTINCT il0 FROM (SELECT l0_.id AS il0, l0_.name AS name2, l0_.address1 
  AS address14, l0_.address2 AS address25, l0_.postcode AS postcode6, l0_.town AS 
  town7, l0_.county AS county8, l0_.enabled AS enabled11, l0_.date_created AS 
  date_created12, l0_.date_modified AS date_modified13, l0_.latitude AS 
  latitude19, l0_.longitude AS longitude20, 3959 * ACOS(COS(RADIANS(53.51331889999999)) 
  * COS(RADIANS(l0_.latitude)) * COS(RADIANS(l0_.longitude) - 
  RADIANS(-2.935331099999985)) + SIN(RADIANS(53.51331889999999)) * 
  SIN(RADIANS(l0_.latitude))) AS sclr21 FROM locations l0_ WHERE l0_.enabled = ? 
  AND sclr21 &lt; ? ORDER BY sclr21 ASC) dctrn_result) dctrn_table' with params 
  {"1":1,"2":30}:
  
  SQLSTATE[42S22]: Column not found: 1054 Unknown column 'sclr21' in 'where clause'`
  </code></pre>