How do I use custom-operators in Doctrine DQL?

1
votes

I am trying to use PostGIS functions in my queries. I am using jsor/doctrine-postgis package to add relevant functionality to Doctrine. I am not exactly sure if I have configured it incorrectly or if I am doing something wrong.

For example:

->where('ST_Intersects(st.coords,ST_MakeEnvelope(-1,-1,1,1,4326))')

However this does not work because syntax checker wants a comparison operator. I get the following error:

Error: Expected =, <, <=, <>, >, >=, !=, got end of string

I managed to avoid this error by checking true/false. While this is undesirable, it works.

->where('ST_Intersects(st.coords,ST_MakeEnvelope(-1,-1,1,1,4326))=true')

But I want to use the && operator. I am not sure exactly how can I manage that? What I mean is something like this:

->where('st.coords && ST_MakeEnvelope(-1,-1,1,1,4326))')

But this returns an error:

Error: Expected =, <, <=, <>, >, >=, !=, got '&'

Am I doing something wrong? This feels over-complicated for some reason?

2
doctrine-ormpostgisdql

2 Answers

1
votes

It's a known bug. There is nothing you can do. From jsor, the author

AFAICT, implementing custom operators isn't possible with Doctrine :( I don't think i can do much here. The only solution i'm aware of is using Doctrine's Native SQL feature. Closing for now. Feel free to reopen if you have further questions/ideas.

So your only option is ->createNativeQuery if you want to make use of a GIST index, or geometry_overlaps if you don't care.

0
votes

You should add " = true" at end of where:

     /**
     * @param string $bbox
     * @param int|null $limit
     * @return MyEntity[]
     */
    public function findByCoordinatesBoundingBox(string $bbox, int $limit = null): array
    {
        return $this->createQueryBuilder('n')
            ->where("ST_Intersects(n.coordinates, ST_MakeEnvelope({$bbox})) = true")
            ->orderBy('n.id', 'ASC')
            ->setMaxResults($limit)
            ->getQuery()
            ->getResult()
            ;
    }