Use raw SQL in Custom Api Platform Filter

I got a custom function in my PostgreSQL name town_radius(idTown, radiusTown) who return all the town(id, name, geo_shape) of my table towns in the radius of the given town.
So I need to implement this custom PostgreSQL function in my Api Platform filter. But when I do something like this :


    $sql = 'SELECT * FROM ref_town rt WHERE rt IN (SELECT town_radius(?1 , ?2));';
    $stmt = $this->getEntityManager()->getConnection()->prepare($sql);
    $stmt->bindValue('1', $idTown);
    $stmt->bindValue('2', $radiusTown);

in my filter property function of my Custom Api Platform it doesn’t work. So I registered my custom function in DQL following the official documentation (https://www.doctrine-project.org/projects/doctrine-orm/en/2.7/cookbook/dql-user-defined-functions.html#registering-your-own-dql-functions) and I used it like this in my Custom Filter :


    $queryBuilder->getEntityManager()->getConfiguration()->addCustomStringFunction('town_radius',
                TownRadiusFunction::class);
            $queryBuilder->select('town_radius(:idTown, :radiusTown)')
                ->setParameter('idTown', $idTown)
                ->setParameter('radiusTown', $radiusTown);

it also doesn’t work because he return my result a multiple time.

Have you got a solution ? Some tips ? What do I do wrong ?

Source: Symfony Questions

Was this helpful?

0 / 0

Leave a Reply 0

Your email address will not be published. Required fields are marked *