Doctrine Query Builder ACOS function cannot be found

Question

I want to calculate distance in my custom repository class. The problem is that Doctrine throws exception while processing ACOS function:

[Syntax Error] line 0, col 70: Error: Expected known function, got 'ACOS'

Here is the query:

public function findLocation($latitude, $longitude)
{
    $em = $this->getEntityManager();
    return $em->createQueryBuilder()
                    ->select('((ACOS(SIN('.$latitude.' * PI() / 180) * SIN(p.latitude * PI() / 180) + COS('.$latitude.' * PI() / 180) * COS(p.latitude * PI() / 180) * COS(('.$longitude.' – p.longitude) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance')
                    ->from('StrictPlaceBundle:Poi', 'p')
                    ->add('orderBy', 's.distance ASC')
                    ->getQuery()->getResult();    
}

What can be wrong?

Answer 1

ACOS is not supported by DQL. You could add it yourself of course (see Adding your own functions to the DQL language).

Also, MySQL implementations of trigonometric functions you need are part of DoctrineExtensions:

• the test
• functions