CODEWITHSUNDEEP
api-platform.com
Jan Stefanides
Jan Stefanides

Reputation: 91

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

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:

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

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?

Upvotes: 1

Views: 2785

Answers (1)

Johnny
Johnny

Reputation: 1116

You need to add a custom filter:

  1. Calculate distance in meters
  2. Filter based on radius (max distance)
  3. 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

Upvotes: 1

Related Questions