Api Platform filter with calculated column (i.e. geo distance)

Question

I have done already several different custom filters for Api Platform, however now I need another one and I am missing the right way how to do that. Basically the task is to sort items by geo distance from a given point and show the distance. This is quite easy in pure SQL (see e.g. https://mysqlserverteam.com/mysql-5-7-and-gis-an-example/), but it seems quite complicated inside Api Platform. Basically there are several tasks to do:

add a custom filter to column with gps value, easy
define starting location. Can be set as order[gps] value, easy, usually you do not need the opposite direction
optionally filter all rows with max. distance, also easy SQL WHERE with variable. This one is described in comments here https://github.com/api-platform/api-platform/issues/382, https://gist.github.com/vincentchalamon/6afb137e8c2d1dec1d5a1978b45b64ec
add new SELECT column with calculated distance. You can add calculated columns inside php entity or serializer or custom controller, but how to add it inside Filter::filterProperty()? If I try $queryBuilder->addSelect(), then query works, but it breaks the Serializer:

Notice: Undefined index: _links
in vendor/api-platform/core/src/Hal/Serializer/CollectionNormalizer.php (line 85)
                $data['_links']['item'][] = $item['_links']['self'];

finally, if you have extra column in SQL, you can addOrderBy() by its value.
then the question would be, how to get the extra column into the entity, if it is not always present in the query? (it is present only when sorting by distance).

Probably I can split it into two parts - keep $queryBuilder to make the sort only and calculate the distance for extra column once again in php serializer/controller, but this is quite inefficient for large datasets. (Anyway even doctrine would be calculating it three times again and again - count(), DISTINCT IDs a detail query)

Anyone used that already or has any idea?

Johnny Johnny · Accepted Answer · 2020-02-04T11:21:12

You need to add a custom filter:

Calculate distance in meters
Filter based on radius (max distance)
Order by closest

As follows:

protected function filterProperty(string $property, $value, QueryBuilder $queryBuilder, QueryNameGeneratorInterface $queryNameGenerator, string $resourceClass, string $operationName = null)
{
    // otherwise filter is applied to order and page as well
    if ($property !== 'distance') {
        return;
    }

    [$lat, $lng, $radius] = explode(',', $value);

    // add distance in DQL based on filter location point
    $rootAlias = $queryBuilder->getRootAliases()[0];
    $queryBuilder
        ->addSelect('(6371000 * acos(cos(radians(' . $lat . ')) * cos(radians('.$rootAlias.'.lat)) * cos(radians('.$rootAlias.'.lng) - radians(' . $lng . ')) + sin(radians(' . $lat . ')) * sin(radians('.$rootAlias.'.lat)))) AS distance')
        ->having('distance <= :radius')
        ->setParameter('radius', $radius)
        ->orderBy('distance')
    ;
}

For this to work you also need to add custom DQL functions for acos, cos, radians, sin:

Here's a link for Doctrine Extensions https://github.com/beberlei/DoctrineExtensions

doctrine:
    orm:
        dql:
            numeric_functions:
                acos: DoctrineExtensions\Query\Mysql\Acos
                cos: DoctrineExtensions\Query\Mysql\Cos
                radians: DoctrineExtensions\Query\Mysql\Radians
                sin: DoctrineExtensions\Query\Mysql\Sin

Api Platform filter with calculated column (i.e. geo distance)

1 Answers