Add custom SQL to orderBy statment in Doctrine2

Question

I want to sort results in a query by the distance to the point's location (lat/long).

I am using Doctrine2 and Symfony2.8 and this is my query:

public function findByPoint(Point $point)
{
    $sql = sprintf(
        '(6371 * acos(cos(radians(%s)) * cos(radians(X(s.point))) * cos(radians(Y(s.point)) -
        radians(%s)) + sin(radians(%s)) * sin(radians(X(s.point)))))', $point->getLatitude(), $point->getLongitude(), $point->getLatitude());
    $qb = $this->createQueryBuilder('s');
    return $qb
        ->orderBy($sql, 'ASC')
        ->setMaxResults(5)
        ->getQuery()
        ->getResult();
}

But this does not work, it throws the exception [Syntax Error] line 0, col 60: Error: Expected end of string, got '6371'.

Is there any way to inject custom SQL to this or a DQL way to create a custom orderby?

Answer 1

You need to use custom function with Doctrine2 for geographic coordinates.

Try to use this custom DistanceFunction: https://gist.github.com/Koc/3016704

Or this bundle: https://github.com/craue/CraueGeoBundle

Here is a list of all provided by Doctrine functions: http://docs.doctrine-project.org/en/latest/reference/dql-doctrine-query-language.html#dql-functions

IDENTITY(single_association_path_expression [, fieldMapping]) - Retrieve the foreign key column of association of the owning side
ABS(arithmetic_expression)
CONCAT(str1, str2)
CURRENT_DATE() - Return the current date
CURRENT_TIME() - Returns the current time
CURRENT_TIMESTAMP() - Returns a timestamp of the current date and time.
LENGTH(str) - Returns the length of the given string
LOCATE(needle, haystack [, offset]) - Locate the first occurrence of the substring in the string.
LOWER(str) - returns the string lowercased.
MOD(a, b) - Return a MOD b.
SIZE(collection) - Return the number of elements in the specified collection
SQRT(q) - Return the square-root of q.
SUBSTRING(str, start [, length]) - Return substring of given string.
TRIM([LEADING | TRAILING | BOTH] [‘trchar’ FROM] str) - Trim the string by the given trim char, defaults to whitespaces.
UPPER(str) - Return the upper-case of the given string.
DATE_ADD(date, days, unit) - Add the number of days to a given date. (Supported units are DAY, MONTH)
DATE_SUB(date, days, unit) - Substract the number of days from a given date. (Supported units are DAY, MONTH)
DATE_DIFF(date1, date2) - Calculate the difference in days between date1-date2.

acos, cos, radians are not supported by default (without custom extension).