How can I use postgis distance operator (<->) with doctrine?-CodePudding

I want to find the cards closest to a given point and calculate the distance. link postgis

However I can't execute the query in symfony:

 $results = $this->createQueryBuilder('p')
            ->orderBy('(geog <->) ST_Point(:lon, :lat)', 'DESC')
            ->setParameter('lng', $lng)
            ->setParameter('lat', $lat)
            ->setMaxResults(9)
            ->getQuery()
            ->getResult();

My error:

[Syntax Error] line 0, col 64: Error: Expected Doctrine\ORM\Query\Lexer::T_CLOSE_PARENTHESIS, got '<'

-> I understand that DQL is not supported, but I don't understand how, on symfony 6.2, to get <-> to work.

So I also tested this solution:

 $conn = $emi->getConnection();

    $sql = '
          SELECT * FROM fiche f
         ORDER BY geog <-> ST_Point(7, 49) limit 9
          ';
    $stmt = $conn->prepare($sql);
    $resultSet = $stmt->executeQuery();
    dd($resultSet->fetchAllAssociative());

I have the results in an array but I don't have the mapping of the objects of the 'Form' entity (I find this solution too brutal)

I also use the doctrine-postgis bundle. I also want in a second step to add the distance (between the lgn and lat variable) and the 9 results in a 'distance' field of my entity (which I have not mapped to the ORM)

 public ?float $distance = null;

My class:

class Poi
{
 #[ORM\Column(nullable: true)]
    private ?float $latitude = null;

    #[ORM\Column(nullable: true)]
    private ?float $longitude = null;

    #[ORM\Column(type: PostGISType::GEOGRAPHY, nullable: true)]
    private string $geog;
   
    public ?float $distance = null;

CodePudding user response：

Doctrine allows you to extend DQL with new functions. That is exactly what the doctrine postgis extension does, and you can have a look at how exactly it does so by checking for instance the files in https://github.com/jsor/doctrine-postgis/tree/main/src/Functions.

You can also extend DQL by yourself. To do this, since you use Doctrine inside Symfony, you should follow Symfony's documentation about registering custom doctrine functions: https://symfony.com/doc/current/doctrine/custom_dql_functions.html. It refers to Doctrine's own documentation about creating custom DQL functions, but the way you register them once the PHP classes have been declared is different.

Then you implement <-> as a function, so that MyNewFunction(A, B) in DQL will compile to A <-> B in postgres. You do this because (as far as I know) you cannot define custom operators in Doctrine, only custom functions. You can look at this stackoverflow question to see how another user handled a similar issue: https://github.com/semin-lev/ltree-extension-bundle/blob/master/DqlFunction/LtreeOperatorFunction.php.