Doctrine register custom DQL functions

Question

I want to create a method that returns all ads within a radius, so I'm trying to add functions: earth_distance_operator, earth_box, ll_to_earth the postgress.

config.yml

orm:
        auto_generate_proxy_classes: "%kernel.debug%"
        auto_mapping: true
        dql:
            numeric_functions:
                earth_box: Delivve\WebBundle\DQL\EarthBox
                ll_to_earth: Delivve\WebBundle\DQL\LLToEarth
                earth_distance_operator: Delivve\WebBundle\DQL\EarthDistanceOperator

EarthBox.php

class EarthBox extends FunctionNode
{
    private $earth;
    private $radius;

    public function getSql(SqlWalker $sqlWalker)
    {
        return 'earth_box(' . $this->earth->dispatch($sqlWalker) . ', ' . $this->radius->dispatch($sqlWalker) . ')';
    }

    public function parse(Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);

        $this->earth = $parser->StringPrimary();
        $parser->match(Lexer::T_COMMA);

        $this->radius = $parser->ArithmeticPrimary();
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }
} 

EarthDistanceOperator.php

class EarthDistanceOperator extends FunctionNode
{
    private $operator;
    private $first;
    private $second;

    /**
     * @param SqlWalker $sqlWalker
     *
     * @return string
     */
    public function getSql(SqlWalker $sqlWalker)
    {
        return sprintf("%s %s %s",
            $this->first->dispatch($sqlWalker),
            $this->operator->value,
            $this->second->dispatch($sqlWalker)
        );
    }
    /**
     * @param Parser $parser
     *
     * @return void
     */
    public function parse(Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);

        $this->first = $parser->StringPrimary();
        $parser->match(Lexer::T_COMMA);

        $this->operator = $parser->StringExpression();
        $parser->match(Lexer::T_COMMA);

        $this->second = $parser->StringPrimary();
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }
} 

LLToEath.php

class LLToEarth extends FunctionNode
{
    private $latitude;
    private $longitude;

    public function getSql(SqlWalker $sqlWalker)
    {
        return 'll_to_earth(' . $this->latitude->dispatch($sqlWalker) . ', ' . $this->longitude->dispatch($sqlWalker) . ')';
    }

    public function parse(Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);

        $this->latitude = $parser->ArithmeticPrimary();
        $parser->match(Lexer::T_COMMA);

        $this->longitude = $parser->ArithmeticPrimary();
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }
} 

My problem is that when I create the function in the repository he's not giving function ll_to_eath as indefinite

AdRepository.php

    public function findByExceptOwner($paramFetcher)
    {
        $departureOriginal = $paramFetcher['departure'];
        $departureArray = preg_split('/ - /', $departureOriginal);
        $departureCityArray = preg_split('/,/', $departureArray[count($departureArray) - 2]);
        $departureUFArray = preg_split('/,/', $departureArray[count($departureArray) - 1]);
        $departure = $departureCityArray[count($departureCityArray) -1].' - '.$departureUFArray[0];

        $destinationOriginal = $paramFetcher['destination'];
        $destinationArray = preg_split('/ - /', $destinationOriginal);
        $destinationCityArray = preg_split('/,/', $destinationArray[count($destinationArray) - 2]);
        $destinationUFArray = preg_split('/,/', $destinationArray[count($destinationArray) - 1]);
        $destination = $destinationCityArray[count($destinationCityArray) -1].' - '.$destinationUFArray[0];

        dump($departure);
        dump($destination);

        $query = $this->getEntityManager()->createQueryBuilder('ad');

        // parâmetros iniciais da query: status, departure de destination
        $query->select('ad')
            ->from('DelivveWebBundle:'.$paramFetcher['type'], 'ad')
            ->where('ad.owner <> :ownerId')
            ->andWhere($query->expr()->eq('ad.status', ':status'))
            ->andWhere($query->expr()->like('ad.departure', ':departure'))
            ->andWhere($query->expr()->like('ad.destination', ':destination'))
            ->andWhere('ad.dateFinal <= :today')
            ->setParameter('ownerId', $paramFetcher['ownerId'])
            ->setParameter('status', 'new')
            ->setParameter('departure', "%{$departure}%")
            ->setParameter('destination', "%{$destination}%")
            ->setParameter('today', new \DateTime("now"));

        // raio de busca com centro nas coordenadas de departure
        if ($paramFetcher['departure_latitude'] && $paramFetcher['departure_longitude'] && $paramFetcher['radius']) {
            $query
                ->andWhere('earth_distance_operator(earth_box(ll_to_earth(:departure_lat, :departure_lon), :radius), \'@>\', ll_to_earth(ad.departureLatitude, ad.departureLongitude)) = true')
                ->setParameter('departure_lat', floatval($paramFetcher['departure_latitude']), \Doctrine\DBAL\Types\Type::FLOAT)
                ->setParameter('departure_lon', floatval($paramFetcher['departure_longitude']), \Doctrine\DBAL\Types\Type::FLOAT)
                ->setParameter('radius', floatval($paramFetcher['radius']) / 1.609, \Doctrine\DBAL\Types\Type::FLOAT);
        }

        // raio de busca com centro nas coordenadas de destionation
        if ($paramFetcher['destination_latitude'] && $paramFetcher['destination_longitude'] && $paramFetcher['radius']) {
            $query
                ->andWhere('earth_distance_operator(earth_box(ll_to_earth(:destination_lat, :destination_lon), :radius), \'@>\', ll_to_earth(ad.destinationLatitude, ad.destinationLongitude)) = true')
                ->setParameter('destination_lat', floatval($paramFetcher['destination_latitude']), \Doctrine\DBAL\Types\Type::FLOAT)
                ->setParameter('destination_lon', floatval($paramFetcher['destination_longitude']), \Doctrine\DBAL\Types\Type::FLOAT)
                ->setParameter('radius', floatval($paramFetcher['radius']) / 1.609, \Doctrine\DBAL\Types\Type::FLOAT);
        }

        dump($query->getQuery());
        dump($query->getQuery()->getResult());
        die();
        return ;
    }

This is the mistake that this giving, someone help me?

SQLSTATE[42883]: Undefined function: 7 ERROR: function ll_to_earth(unknown, unknown) does not exist

`

[2/2] DBALException: An exception occurred while executing 'SELECT m0_.created_at AS created_at0, m0_.updated_at AS updated_at1, m0_.id AS id2, m0_.departure AS departure3, m0_.departure_latitude AS departure_latitude4, m0_.departure_longitude AS departure_longitude5, m0_.destination AS destination6, m0_.destination_latitude AS destination_latitude7, m0_.destination_longitude AS destination_longitude8, m0_.landmark AS landmark9, m0_.transportation AS transportation10, m0_.date AS date11, m0_.price AS price12, m0_.status AS status13, m0_.responsible AS responsible14, m0_.package_type AS package_type15, m0_.date_final AS date_final16, m0_.type AS type17, m0_.user_id AS user_id18 FROM Sender s1_ INNER JOIN my_ad m0_ ON s1_.id = m0_.id WHERE m0_.user_id <> ? AND m0_.status = ? AND m0_.departure LIKE ? AND m0_.destination LIKE ? AND m0_.date_final <= ? AND earth_box(ll_to_earth(?, ?), ?) @> ll_to_earth(m0_.departure_latitude, m0_.departure_longitude) = true AND earth_box(ll_to_earth(?, ?), ?) @> ll_to_earth(m0_.destination_latitude, m0_.destination_longitude) = true' with params [2, "new", "% S\u00e3o Carlos - SP%", "%S\u00e3o Carlos - SP%", "2015-09-22 10:10:04", "-22.0087082", "-47.89092629999999", 18.645121193288, "-22.0087082", "-47.89092629999999", 18.645121193288]:

SQLSTATE[42883]: Undefined function: 7 ERROR: function ll_to_earth(unknown, unknown) does not exist
LINE 1: ...on LIKE $4 AND m0_.date_final <= $5 AND earth_box(ll_to_eart...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.

Answer 1

in fact the error was not in code but in cofiguração of my machine. My bank had not installed earthdistance the module;

to install the module:

CREATE EXTENSION cube;
CREATE EXTENSION earthdistance;