0

In my symfony project, I search data in the database with Doctrine QueryBuilder :

public function search($name)
{
    return $this->createQueryBuilder('Town')
        ->andWhere('Town.name LIKE :name')
        ->setParameter('name', '%' . $name . '%')
        ->getQuery()
        ->execute();
}

Town table have 3 fields: name, latitude and longitude.

The result gives me a list of towns having a certain name. Now I want to go one step farther by adding coordinates in my query. The SQL version of the query would be :

SELECT name, SQRT(POWER(48.562370 - latitude, 2) + POWER(7.761280 - longitude, 2)) as radius from Town
HAVING radius is not null AND radius < 0.01
ORDER by radius limit 50

But I have no idea how to use SQRT function with QueryBuilder.

Toms River
  • 196
  • 1
  • 4
  • 13

1 Answers1

0

You can try:

        $rsm = new ResultSetMapping();
        $rsm->addScalarResult('radius', '0.01');

        $query = $this->getEntityManager()->createNativeQuery(
            "SELECT name, SQRT(POWER(48.562370 - latitude, 2) + POWER(7.761280 - longitude, 2)) as radius
            FROM Town
            HAVING radius is not null AND radius < :radius
            ORDER by radius limit 50",
            $rsm
        )
        ->getArrayResult();

https://www.doctrine-project.org/projects/doctrine-orm/en/2.7/reference/native-sql.html

poppies
  • 142
  • 6