I'm using this:
$query = $em->createQuery('select cp from Temp\TempAppBundle\Entity\ClinicProfile cp INNER JOIN cp.users u left JOIN cp.specializations s where cp.latitude is not null and cp.city = ?1 order by (sqrt(pow((radians(cp.longitude) - radians(?2))*cos(radians(cp.latitude)+radians(?3)), 2) + pow((radians(cp.latitude) - radians(?4)), 2))*6367)');
But I'm getting error
[Syntax Error] line 0, col 172: Error: Expected end of string, got 'sqrt'
How do i use sql functions in dql?
Doctrine dql does support the SQRT function but only in SELECT, WHERE and HAVING clauses so it won't work in your query where it appears in the ORDER BY clause. The other functions, POW, RADIANS and COS are not explicitly supported by dql.
There is a method for adding your own functions to the dql language (the documentation gives an example for the MySQL FLOOR function - see section 13.5.5 of the DQL Documentation) However it is unclear from the documentation whether these are also restricted to the SELECT, WHERE and HAVING clauses.
An alternative in this case is to use Doctrine Native SQL.