How to add ST_DISTANCE_SPHERE function to Doctrine?-CodePudding

I need to make some changes to the code, which was written by another developer. One of them is to use the ST_DISTANCE_SPHERE function in a query. I added this function to the database following this link

But I realized that it's not enough, cause the application is using Doctrine. I'm not using Doctrine in my usual applications, so I'm not pretty sure what should I do.

Until now I ran composer require creof/doctrine2-spatial in console

And I added to config/package/doctrine.yaml below code

doctrine:
    dbal:
        url: '%env(resolve:DATABASE_URL)%'
        types:   
            geometry:   CrEOF\Spatial\DBAL\Types\GeometryType
            point: CrEOF\Spatial\DBAL\Types\Geometry\PointType

What should I do more to be able to use this function in my Repository? Error, which I'm getting is:

Doctrine\ORM\Query\QueryException:
[Syntax Error] line 0, col 70: Error: Expected known function, got 'ST_DISTANCE_SPHERE'

at vendor\doctrine\orm\lib\Doctrine\ORM\Query\QueryException.php:54

CodePudding user response：

Based on the error message, you may need something like:

doctrine:
    dbal:
        url: '%env(resolve:DATABASE_URL)%'
        types:   
            geometry:   CrEOF\Spatial\DBAL\Types\GeometryType
            point: CrEOF\Spatial\DBAL\Types\Geometry\PointType

    orm:
        dql:
            numeric_functions:
                # for postgresql
                stdistance:              CrEOF\Spatial\ORM\Query\AST\Functions\PostgreSql\STDistance

CodePudding user response：

Thanks to @Nicodemuz answer I finally find the right way:

orm:
      dql:
            numeric_functions:
                stdistance: CrEOF\Spatial\ORM\Query\AST\Functions\Mysql\STDistance
                stdistancesphere: CrEOF\Spatial\ORM\Query\AST\Functions\Mysql\STDistanceSphere
                distance:               CrEOF\Spatial\ORM\Query\AST\Functions\MySql\Distance
                geometrytype:           CrEOF\Spatial\ORM\Query\AST\Functions\MySql\GeometryType
                point:                  CrEOF\Spatial\ORM\Query\AST\Functions\MySql\Point

The second important thing was that I had to use in my query STDistanceSphere name instead of ST_DISTANCE_SPHERE to make it work fine.

Third thing, it may be helpful for MySql users like me to see this. It looks like this function is available for MySql, but it's not merged, so you should add to package some files from here .