Doctrine distances function

Question

I try to use a function which calculate distance between $user lat/lng and coordinates in my BDD with limited distance.

It's an SQL request and I try to use Doctrine to implement it.

Here is my code

    $config = new \Doctrine\ORM\Configuration();

    $config->addCustomNumericFunction('COS', 'DoctrineExtensions\Query\Mysql\Cos');

    $config->addCustomNumericFunction('ACOS', 'DoctrineExtensions\Query\Mysql\Acos');

    $config->addCustomNumericFunction('RADIANS', 'DoctrineExtensions\Query\Mysql\Radians');

    $config->addCustomNumericFunction('SIN', 'DoctrineExtensions\Query\Mysql\Sin');

    $maxLat = $form_citylat + rad2deg($rad / $R);
    $minLat = $form_citylat - rad2deg($rad / $R);
    $maxLng = $form_citylng + rad2deg(asin($rad / $R) / cos(deg2rad($form_citylat)));
    $minLng = $form_citylng - rad2deg(asin($rad / $R) / cos(deg2rad($form_citylat)));


    $qb = $this->createQueryBuilder('u')->select('u.lat, u.lng')
        ->addSelect('acos(sin(:lat)*sin(radians(u.Lat)) + cos(:lat)*cos(radians(u.Lat))*cos(radians(u.lng)-:lng)) * :R As D')
        ->where('lat Between :minlat And :maxlat And lng Between :minlng And :maxlng And acos(sin(:lat)*sin(radians(u.Lat)) + cos(:lat)*cos(radians(u.Lat))*cos(radians(u.Lng)-:lng)) * :R < :rad')
        ->setParameter('lat',deg2rad($form_citylat))
        ->setParameter('lng',deg2rad($form_citylng))
        ->setParameter('minlat',$minLat)
        ->setParameter('minlng',$minLng)
        ->setParameter('maxlat',$maxLat)
        ->setParameter('maxlng',$maxLng)
        ->setParameter('rad',$rad)
        ->setParameter('R',$R)
        ->orderBy('D');

    return $qb->getQuery()->getResult();`

But I got this error message :

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

I tried different options but it doesn't work.

Anyone has an answer please ?

I am literally trying to do the same thing. Did you find a solution to this? — Adambean, Oct 22 '18 at 14:17

score 0 · Answer 1 · edited Jun 03 '19 at 11:14

public function findByThemeAndDistance($theme,$distance,$user){     

    $latUser=$user->getAddress()->getLnt();
    $lngUser = $user->getAddress()->getLgt();

    return $this->createQueryBuilder('a')
    //->select('a as activity,dist('.$distance.') as distance' )
    ->join('a.author','u')
    ->join('u.address','add')
    ->andWhere('a.theme=:val')
    ->andWhere( '(6378 * acos(cos(radians( add.lnt)) * cos(radians(' . $latUser . ')) * cos(radians(' . $lngUser . ') - radians(add.lgt)) + sin(radians(add.lnt )) * sin(radians(' . $latUser . '))))< :distance')
    ->setParameter('distance', $distance)
    ->setParameter('val',$theme)
    ->orderBy( 'a.author','ASC') 
    ->getQuery()
    ->getResult();
}

Please [edit] your answer and explain how does your code solve the issue. — barbsan, Jun 03 '19 at 11:16

Doctrine distances function

1 Answers1