Skip to content

Instantly share code, notes, and snippets.

@migueleliasweb
Created June 2, 2014 20:03
Show Gist options
  • Save migueleliasweb/85e7a3e99a4abb9de28b to your computer and use it in GitHub Desktop.
Save migueleliasweb/85e7a3e99a4abb9de28b to your computer and use it in GitHub Desktop.
Distance DQL/UDF
<?php
namespace ChefsClub\ApiBundle\Component\Doctrine\DQL;
use Doctrine\ORM\Query\AST\Functions\FunctionNode;
use Doctrine\ORM\Query\AST\InputParameter;
use Doctrine\ORM\Query\Lexer;
use Doctrine\ORM\Query\Parser;
use Doctrine\ORM\Query\SqlWalker;
/**
* Calculates distance between two points in meters
* Ps: lat and lng MUST BE in signed degrees !
* @example DISTANCE(lat, lng, lat_param, lng_param).
* @see https://github.com/beberlei/DoctrineExtensions
*/
class Distance extends FunctionNode
{
/**
* @var InputParameter
*/
private $latitude_field_value;
/**
* @var InputParameter
*/
private $longitude_field_value;
/**
* @var InputParameter
*/
private $lat_value;
/**
* @var InputParameter
*/
private $lng_value;
/**
* DISTANCE(lat_field(1), lng_field(2), lat(3), lng(4))
* @param Parser $Parser
*/
public function parse(Parser $Parser)
{
$Parser->match(Lexer::T_IDENTIFIER);
$Parser->match(Lexer::T_OPEN_PARENTHESIS);
// $this->latitude_field_value = $Parser->InputParameter();
// $Parser->match(Lexer::T_COMMA);
//
// $this->longitude_field_value = $Parser->InputParameter();
// $Parser->match(Lexer::T_COMMA);
$this->lat_value = $Parser->InputParameter();
$Parser->match(Lexer::T_COMMA);
$this->lng_value = $Parser->InputParameter();
$Parser->match(Lexer::T_CLOSE_PARENTHESIS);
}
public function getSql(SqlWalker $sqlWalker)
{
return
'ROUND(
ACOS(
SIN(
RADIANS(lat)) *
SIN(RADIANS('.$this->lat_value->dispatch($sqlWalker).')
)
+
COS(RADIANS(lat)) *
COS(RADIANS('.$this->lat_value->dispatch($sqlWalker).')) *
COS(
RADIANS(lng) -
RADIANS('.$this->lng_value->dispatch($sqlWalker).')
)
) * 6371000
)'
;
}
}
$QueryBuilder->addSelect('DISTANCE(:Lat, :Lng) as distance')->setParameters([
'Lat' => $ArrayParams['Lat'],
'Lng' => $ArrayParams['Lng']
]);
@migueleliasweb
Copy link
Author

//Se o php das columas no parse() for reescrito assim, a query fica perfeita, mas quando o doctrine vai //injetar os parâmetros da erro, pois na verdade, ficam parâmetros à menos na query....

$this->latitude_field_value = $Parser->Literal();
$Parser->match(Lexer::T_COMMA);

$this->longitude_field_value = $Parser->Literal();
$Parser->match(Lexer::T_COMMA);

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment