APIPlatform OrderFilter with distance using latitude and longitude columns on PostgreSQL database.

OrderFilterLatLng.php

<?php

declare(strict_types=1);

namespace App\Filter\API;

use ApiPlatform\Core\Bridge\Doctrine\Common\Filter\OrderFilterTrait;
use ApiPlatform\Core\Bridge\Doctrine\Orm\Filter\OrderFilter as BaseOrderFilter;
use ApiPlatform\Core\Bridge\Doctrine\Orm\Util\QueryNameGeneratorInterface;
use Doctrine\Common\Persistence\ManagerRegistry;
use Doctrine\ORM\Query\Expr\Join;
use Doctrine\ORM\QueryBuilder;
use Psr\Log\LoggerInterface;
use Symfony\Component\HttpFoundation\RequestStack;
use Symfony\Component\Serializer\NameConverter\NameConverterInterface;

/**
 * {@inheritdoc}
 *
 * Ordering by distance, e.g.:
 * Request: `GET /places?order[distance]=ASC:lat,long`
 * Set in the Entity: `ApiFilter(OrderFilter::class, properties={"distance"={"lat_property"="latitude", "lng_property"="longitude"}})`
 */
final class OrderFilter extends BaseOrderFilter
{
    use OrderFilterTrait;

    private const DISTANCE_PROPERTY_NAME = 'distance';

    private const COORDINATES_REGEX = '/^(?P<latitude>[-+]?(?:[1-8]?\d(?:\.\d+)?|90(?:\.0+)?)),(?P<longitude>[-+]?(?:180(\.0+)?|(?:(?:1[0-7]\d)|(?:[1-9]?\d))(?:\.\d+)?))$/';

    /**
     * @var string
     */
    private $latProperty;

    /**
     * @var string
     */
    private $lngProperty;

    public function __construct(ManagerRegistry $managerRegistry, ?RequestStack $requestStack = null, string $orderParameterName = 'order', LoggerInterface $logger = null, array $properties = null, NameConverterInterface $nameConverter = null)
    {
        if (isset($properties[self::DISTANCE_PROPERTY_NAME])) {
            $this->latProperty = $properties[self::DISTANCE_PROPERTY_NAME]['lat_property'] ?? null;
            $this->lngProperty = $properties[self::DISTANCE_PROPERTY_NAME]['lng_property'] ?? null;

            if (!\is_string($this->latProperty)) {
                throw new \InvalidArgumentException(sprintf('The "lat_property" value must be set for "%s".', self::DISTANCE_PROPERTY_NAME));
            }
            unset($properties[self::DISTANCE_PROPERTY_NAME]['lat_property']);

            if (!\is_string($this->lngProperty)) {
                throw new \InvalidArgumentException(sprintf('The "lng_property" value must be set for "%s".', self::DISTANCE_PROPERTY_NAME));
            }
            unset($properties[self::DISTANCE_PROPERTY_NAME]['lng_property']);
        }

        parent::__construct($managerRegistry, $requestStack, $orderParameterName, $logger, $properties, $nameConverter);
    }

    protected function filterProperty(string $property, $value, QueryBuilder $queryBuilder, QueryNameGeneratorInterface $queryNameGenerator, string $resourceClass, string $operationName = null): void
    {
        if (!\in_array($property, [
            self::DISTANCE_PROPERTY_NAME,
        ], true)) {
            parent::filterProperty($property, $value, $queryBuilder, $queryNameGenerator, $resourceClass, $operationName);

            return;
        }

        if (self::DISTANCE_PROPERTY_NAME === $property) {
            [$direction, $center] = explode(':', $value);

            $direction = $this->normalizeValue($direction, $property);
            if (null === $direction) {
                return;
            }

            if (!preg_match(self::COORDINATES_REGEX, $center, $matches)) {
                return;
            }

            $alias = $queryBuilder->getRootAliases()[0];
            $latField = $this->latProperty;
            $lngField = $this->lngProperty;

            if ($this->isPropertyNested($this->latProperty, $resourceClass)) {
                [$alias, $latField] = $this->addJoinsForNestedProperty($this->latProperty, $alias, $queryBuilder, $queryNameGenerator, $resourceClass, Join::LEFT_JOIN);
                [$nestedEntity, $lngField] = explode(".", $this->lngProperty);
            }

            if (null !== $nullsComparison = $this->properties[$property]['nulls_comparison'] ?? null) {
                $nullsDirection = self::NULLS_DIRECTION_MAP[$nullsComparison][$direction];

                // Lat
                $nullRankHiddenField = sprintf('_%s_%s_null_rank', $alias, $latField);

                $queryBuilder->addSelect(sprintf('CASE WHEN %s.%s IS NULL THEN 0 ELSE 1 END AS HIDDEN %s', $alias, $latField, $nullRankHiddenField));
                $queryBuilder->addOrderBy($nullRankHiddenField, $nullsDirection);


                // Lng
                $nullRankHiddenField = sprintf('_%s_%s_null_rank', $alias, $lngField);

                $queryBuilder->addSelect(sprintf('CASE WHEN %s.%s IS NULL THEN 0 ELSE 1 END AS HIDDEN %s', $alias, $lngField, $nullRankHiddenField));
                $queryBuilder->addOrderBy($nullRankHiddenField, $nullsDirection);
            }

            $latitudeParam = $queryNameGenerator->generateParameterName('latitude');
            $longitudeParam = $queryNameGenerator->generateParameterName('longitude');

            $queryBuilder->addSelect(sprintf(<<<SQL
ST_Distance(ST_Point(:%s, :%s), ST_Point(%s.%s, %s.%s)) AS HIDDEN distance
SQL
                , $longitudeParam, $latitudeParam, $alias, $lngField, $alias, $latField));
            $queryBuilder->addOrderBy('distance', $direction);
            $queryBuilder->setParameter($latitudeParam, $matches['latitude']);
            $queryBuilder->setParameter($longitudeParam, $matches['longitude']);
        }
    }

    // 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 = [];
        $hasNoSpatialProperty = false;

        foreach ($this->properties as $property => $strategy) {

            // Get the parent description (default order)
            if ($property !== self::DISTANCE_PROPERTY_NAME) {
                $hasNoSpatialProperty = true;

                continue;
            }

            $name = sprintf('%s[%s]', $this->orderParameterName, $property);
            $desc = "Example: ASC:lat,long";

            $description[$name] = [
                'property' => $property,
                'type' => 'string',
                'required' => false,
                'description' => $desc,
                'schema' => [
                    'type' => 'string',
                    'description' => $desc,
                ],
                'swagger' => [
                    'description' => $desc,
                    'name' => $name,
                    'type' => 'string',
                ],
            ];
        }

        if ($hasNoSpatialProperty) {
            $description = array_merge($description, parent::getDescription($resourceClass));
        }

        return $description;
    }
}