Symfony Api Platform filtering entities after they are retrieved from persistance layer

Question

I have a situation where I need to extract objects from persistent layer after applying some filters and then do some maths on object data and filter base of another query parameter.

Use case: Get all the location which are within the radius of 10km of given latitude and longitude.

Which can be translated into an api endpoint as:

https://api.testdomain.com/api/location?latitude=10&longitude=20&distance=10

I have location entity with:

 * @ApiFilter(SearchFilter::class, properties={
 *      "longitude": "start",
 *      "latitude":"start",
 *      "city":"partial",
 *      "postal_code":"partial",
 *      "address":"partial",
 *    }
 * )
 class Location
 {
  ... 

   public function withinDistance($latitude, $longitude, $distance):?bool
   {
      $location_distance=$this->distanceFrom($latitude,$longitude);
      return $location_distance<=$distance;
   }

 }

Since latitude and longitude are entity attributes search will be applied and sql query filter is applied, while distance is not an attribute we have to apply this kind of filter after all object are retrieved from db, which is mystery for me.

I am looking to put following code somewhere after query result is returned :

 public function getCollection($collection){
  //return after search filtered applied on location.longitute and location.latitude
  $all_locations_of_lat_long=$collection;
  $locations_within_distance=[];
  $query = $this->requestStack->getCurrentRequest()->query;
  $lat= $query->get('latitude',0);
  $lng= $query->get('longitude',0);
  $distance= $query->get('distance',null);

  if($distance==null){
    return $all_locations_of_lat_long;
  }

  for($all_locations_of_lat_long as $location){
    if($location->withinDistance($lat,$lng,$distance))
       $locations_within_distance[]=$location;
  }

  return $locations_within_distance; 
 }

What is correct why to apply such filter on entity object collections returned ? I don't think ORM filter will be helpful in this case.

Answer 1

I found that its easy to filter entities by writing a custom controller action and filtering entities after they are retrieved from persistence layer. This could mean I had to fetch all records and then filter which is very costly.

Alternate option was, as suggested by qdequippe, was simply write a custom filter to find distance as follow:

Define a distance filter:

src/Filter/DistanceFilter

<?php
namespace App\Filter;


use ApiPlatform\Core\Bridge\Doctrine\Orm\Filter\AbstractContextAwareFilter;
use ApiPlatform\Core\Bridge\Doctrine\Orm\Util\QueryNameGeneratorInterface;
use Doctrine\ORM\QueryBuilder;

final class DistanceFilter extends AbstractContextAwareFilter
{

    const DISTANCE=10.0;
    const LAT='latitude';
    const LON='longitude';

    private $appliedAlready=false;

    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 ($this->appliedAlready && !$this->isPropertyEnabled($property, $resourceClass) ) {
            return;
        }

        //make sure latitude and longitude are part of specs    
        if(!($this->isPropertyMapped(self::LAT, $resourceClass) && $this->isPropertyMapped(self::LON, $resourceClass)) ){
            return ;
        }

        $query=$this->requestStack->getCurrentRequest()->query;

        $values=[];
        foreach($this->properties as $prop=>$val){
            $this->properties[$prop]=$query->get($prop,null);
        }

        //distance is optional 
        if($this->properties[self::LAT]!=null && $this->properties[self::LON]!=null){
            if($this->properties['distance']==null)
                $this->properties['distance']=self::DISTANCE;
        }else{
            //may be we should raise exception 
            return;
        }

        $this->appliedAlready=True;

        // Generate a unique parameter name to avoid collisions with other filters
        $latParam = $queryNameGenerator->generateParameterName(self::LAT);
        $lonParam = $queryNameGenerator->generateParameterName(self::LON);
        $distParam = $queryNameGenerator->generateParameterName('distance');


        $locationWithinXKmDistance="(
            6371.0 * acos (
                cos ( radians(:$latParam) )
                * cos( radians(o.latitude) )
                * cos( radians(o.longitude) - radians(:$lonParam) )
                + sin ( radians(:$latParam) )
                * sin( radians(o.latitude) )
           )
        )<=:$distParam";

        $queryBuilder
            ->andWhere($locationWithinXKmDistance)
            ->setParameter($latParam, $this->properties[self::LAT])
            ->setParameter($lonParam, $this->properties[self::LON])
            ->setParameter($distParam, $this->properties['distance']);
    }

    // This function is only used to hook in documentation generators (supported by Swagger and Hydra)
    public function getDescription(string $resourceClass): array
    {
        if (!$this->properties) {
            return [];
        }

        $description = [];
        foreach ($this->properties as $property => $strategy) {
            $description["distance_$property"] = [
                'property' => $property,
                'type' => 'string',
                'required' => false,
                'swagger' => [
                    'description' => 'Find locations within given radius',
                    'name' => 'distance_filter',
                    'type' => 'filter',
                ],
            ];
        }

        return $description;
    }
}

The idea is we are expecting latitude, longitude and optionally distance parameters in query string. If on one of required param is missing filter is not invoked. If distance is missing we will assume default distance 10km.

Since we have to add DQL functions for acos, cos,sin and radians , instead we use doctrine extensions as follow:

Install doctrine extensions:

composer require beberlei/doctrineextensions

src/config/packages/doctrine_extensions.yaml

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

src/config/services.yaml

services:
    ....
    App\Filter\DistanceFilter:
      arguments: [ '@doctrine', '@request_stack', '@?logger', {latitude: ~, longitude: ~, distance: ~} ]
      tags:
          - { name: 'api_platform.filter', id: 'location.distance_filter' }
      autowire: false
      autoconfigure: false

    app.location.search_filter:
        parent:        'api_platform.doctrine.orm.search_filter'
        arguments:     [ {"city":"partial","postal_code":"partial","address":"partial"}]
        tags:          [ { name: 'api_platform.filter', id: 'location.search_filter' } ]
        autowire:  false
        autoconfigure: false

Configure api filters on location entity:

namespace App\Entity;

use App\Dto\LocationOutput;
use Doctrine\ORM\Mapping as ORM;
use ApiPlatform\Core\Annotation\ApiResource;
use ApiPlatform\Core\Annotation\ApiFilter;

/**
 * Location
 * 
 * @ApiResource(
 *      collectionOperations={
 *          "get"={
 *              "path"="/getLocationList", 
 *               "filters"={
 *                      "location.distance_filter",
 *                       "location.search_filter"
 *                }
 *           }
 *      },
 *      itemOperations={"get"},
 *      output=LocationOutput::class
 * )

Answer 2

You could apply the condition in your SQL, for example in your entity repository.

class YourEntityRepository {

    public function findByLongLatDist(float lat, float long, float dist) {
        // create your query builder here and return results
    }

}

Also check https://gis.stackexchange.com/questions/31628/find-points-within-a-distance-using-mysql to retrieve points using MySQL query. And this repository https://github.com/beberlei/DoctrineExtensions to use specific MySQL functions.