/**
* @param \Doctrine\ORM\Query\SqlWalker $sqlWalker
*
* @return string
*/
public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
{
// use second parameter if parsed
if (null !== $this->fmt) {
return sprintf('TRUNC(%s, %s)', $sqlWalker->walkArithmeticPrimary($this->date), $sqlWalker->walkArithmeticPrimary($this->fmt));
}
return sprintf('TRUNC(%s)', $sqlWalker->walkArithmeticPrimary($this->date));
}
public function getSql(SqlWalker $sqlWalker)
{
$p = $sqlWalker->getConnection()->getDatabasePlatform();
$pi = $p->getPiExpression();
// %F * ASIN(SQRT(POWER(SIN((%s - %s) * PI()/360), 2) + COS(%s * PI()/180) * COS(%s * PI()/180) * POWER(SIN((%s - %s) * PI()/360), 2)))
$formula = "%F * ({$pi} / 2 - " . $p->getAcosExpression($p->getSqrtExpression('(' . $p->getSinExpression("(%s - %s) * {$pi} / 360") . ' * ' . $p->getSinExpression("(%s - %s) * {$pi} / 360") . ') + (' . $p->getCosExpression("%s * {$pi} / 180") . ' * ' . $p->getCosExpression("%s * {$pi} / 180") . ' * ' . $p->getSinExpression("(%s - %s) * {$pi} / 360") . ' * ' . $p->getSinExpression("(%s - %s) * {$pi} / 360") . ')')) . ')';
return sprintf($formula, Measure::EARTH_RADIUS_KM * 2.0, $sqlWalker->walkArithmeticPrimary($this->latOrigin), $sqlWalker->walkArithmeticPrimary($this->latPoint), $sqlWalker->walkArithmeticPrimary($this->latOrigin), $sqlWalker->walkArithmeticPrimary($this->latPoint), $sqlWalker->walkArithmeticPrimary($this->latOrigin), $sqlWalker->walkArithmeticPrimary($this->latPoint), $sqlWalker->walkArithmeticPrimary($this->lonOrigin), $sqlWalker->walkArithmeticPrimary($this->lonPoint), $sqlWalker->walkArithmeticPrimary($this->lonOrigin), $sqlWalker->walkArithmeticPrimary($this->lonPoint));
}
public function getSql(SqlWalker $sqlWalker)
{
$args = array_map(function ($value) use($sqlWalker) {
return $sqlWalker->walkArithmeticPrimary($value);
}, $this->values);
return 'GREATEST(' . implode(', ', $args) . ')';
}
public function getSql(SqlWalker $sqlWalker)
{
$args = array_map(function ($value) use($sqlWalker) {
$nodeSql = $sqlWalker->walkArithmeticPrimary($value);
return $this->notEmpty ? sprintf("NULLIF(%s, '')", $nodeSql) : $nodeSql;
}, $this->values);
return 'CONCAT_WS(' . implode(', ', $args) . ')';
}
/**
* @override
*/
public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
{
$sql = "WEEK(" . $sqlWalker->walkArithmeticPrimary($this->date);
if ($this->mode != null) {
$sql .= ", " . $sqlWalker->walkLiteral($this->mode);
}
$sql .= ")";
return $sql;
}
/**
* Get sql
*
* @param \Doctrine\ORM\Query\SqlWalker $sqlWalker
* @return string
*/
public function getSql(SqlWalker $sqlWalker)
{
/*
* Giving each argument only once and using %1$s, %2$s, ... doesn't work. Would result in:
* SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens
*/
// formula adapted from http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL
// originally returns distance in miles: 3956 * 2 * ASIN(SQRT(POWER(SIN((orig.lat - dest.lat) * PI()/180 / 2), 2) + COS(orig.lat * PI()/180) * COS(dest.lat * PI()/180) * POWER(SIN((orig.lon - dest.lon) * PI()/180 / 2), 2)))
return sprintf('ASIN(SQRT(POWER(SIN((%s - %s) * PI()/360), 2) + COS(%s * PI()/180) * COS(%s * PI()/180) * POWER(SIN((%s - %s) * PI()/360), 2))) * (%s *2)', $sqlWalker->walkArithmeticPrimary($this->latitudeOrigin), $sqlWalker->walkArithmeticPrimary($this->latitudeDestination), $sqlWalker->walkArithmeticPrimary($this->latitudeOrigin), $sqlWalker->walkArithmeticPrimary($this->latitudeDestination), $sqlWalker->walkArithmeticPrimary($this->longitudeOrigin), $sqlWalker->walkArithmeticPrimary($this->longitudeDestination), $sqlWalker->walkArithmeticPrimary($this->radius));
}
/**
* {@inheritdoc}
*/
public function getSql(SqlWalker $sqlWalker)
{
/*
* formula adapted from http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL
* originally returns distance in miles: 3956 * 2 * ASIN(SQRT(POWER(SIN((orig.lat - dest.lat) * PI()/180 / 2), 2) + COS(orig.lat * PI()/180) * COS(dest.lat * PI()/180) * POWER(SIN((orig.lon - dest.lon) * PI()/180 / 2), 2)))
*/
return sprintf('12756 * ASIN(SQRT(POWER(SIN((%s::numeric - %s::numeric) * PI()/360), 2) + COS(%s::numeric * PI()/180) * COS(%s::numeric * PI()/180) * POWER(SIN((%s::numeric - %s::numeric) * PI()/360), 2)))', $sqlWalker->walkArithmeticPrimary($this->parameters['latOrigin']), $sqlWalker->walkArithmeticPrimary($this->parameters['latDestination']), $sqlWalker->walkArithmeticPrimary($this->parameters['latOrigin']), $sqlWalker->walkArithmeticPrimary($this->parameters['latDestination']), $sqlWalker->walkArithmeticPrimary($this->parameters['lngOrigin']), $sqlWalker->walkArithmeticPrimary($this->parameters['lngDestination']));
}
public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
{
$separator = array_shift($this->values)->simpleArithmeticExpression->value;
// Create an array to hold the query elements.
$queryBuilder = array('(');
// Iterate over the captured expressions and add them to the query.
for ($i = 0; $i < count($this->values); $i++) {
if ($i > 0) {
$queryBuilder[] = " || '{$separator}' || ";
}
// Dispatch the walker on the current node.
$queryBuilder[] = sprintf("IFNULL(%s, '')", $sqlWalker->walkArithmeticPrimary($this->values[$i]));
}
// Close the query.
$queryBuilder[] = ')';
// Return the joined query.
return implode('', $queryBuilder);
}
public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
{
// Create an array to hold the query elements.
$queryBuilder = array('CONCAT_WS(');
// Iterate over the captured expressions and add them to the query.
for ($i = 0; $i < count($this->values); $i++) {
if ($i > 0) {
$queryBuilder[] = ', ';
}
// Dispatch the walker on the current node.
$nodeSql = $sqlWalker->walkArithmeticPrimary($this->values[$i]);
if ($this->notEmpty) {
// Exclude empty strings from the concatenation.
$nodeSql = sprintf("NULLIF(%s, '')", $nodeSql);
}
$queryBuilder[] = $nodeSql;
}
// Close the query.
$queryBuilder[] = ')';
// Return the joined query.
return implode('', $queryBuilder);
}
public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
{
return 'HOUR(' . $sqlWalker->walkArithmeticPrimary($this->date) . ')';
}
public function getSql(SqlWalker $sqlWalker)
{
return sprintf('IF(%s, %s, %s)', $sqlWalker->walkConditionalExpression($this->expr[0]), $sqlWalker->walkArithmeticPrimary($this->expr[1]), $sqlWalker->walkArithmeticPrimary($this->expr[2]));
}
public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
{
return sprintf('EXTRACT(YEAR FROM %s)', $sqlWalker->walkArithmeticPrimary($this->date));
}
/**
* @param \Doctrine\ORM\Query\SqlWalker $sqlWalker
*
* @return string
*/
public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
{
return 'SUBTIME(' . $sqlWalker->walkArithmeticPrimary($this->timeExpression) . ',
' . $sqlWalker->walkArithmeticPrimary($this->timeExpression2) . ')';
}
/**
* @param SqlWalker $sqlWalker
* @return string
*/
public function getSql(SqlWalker $sqlWalker)
{
return "MONTH(" . $sqlWalker->walkArithmeticPrimary($this->date) . ")";
}
public function getSql(SqlWalker $sqlWalker)
{
return sprintf('%s * ASIN(SQRT(POWER(SIN((%s - %s) * PI()/360), 2) + COS(%s * PI()/180) * COS(%s * PI()/180) * POWER(SIN((%s - %s) * PI()/360), 2)))', self::EARTH_DIAMETER, $sqlWalker->walkArithmeticPrimary($this->latitudeOrigin), $sqlWalker->walkArithmeticPrimary($this->latitudeDestination), $sqlWalker->walkArithmeticPrimary($this->latitudeOrigin), $sqlWalker->walkArithmeticPrimary($this->latitudeDestination), $sqlWalker->walkArithmeticPrimary($this->longitudeOrigin), $sqlWalker->walkArithmeticPrimary($this->longitudeDestination));
}
protected function getSqlWithLatitude(SqlWalker $sqlWalker)
{
$p = $sqlWalker->getConnection()->getDatabasePlatform();
return sprintf($p->getBetweenExpression('%s', '%s - %s / ABS(' . $p->getCosExpression('%s') . ' * %F)', '%s + %s / ABS(' . $p->getCosExpression('%s') . ' * %F)'), $sqlWalker->walkArithmeticPrimary($this->longitude), $sqlWalker->walkArithmeticPrimary($this->center), $sqlWalker->walkArithmeticPrimary($this->distance), $sqlWalker->walkArithmeticPrimary($this->latitude), Measure::RADIANS_TO_KM, $sqlWalker->walkArithmeticPrimary($this->center), $sqlWalker->walkArithmeticPrimary($this->distance), $sqlWalker->walkArithmeticPrimary($this->latitude), Measure::RADIANS_TO_KM);
}
public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
{
return "DATEDIFF(" . $sqlWalker->walkArithmeticPrimary($this->firstDateExpression) . ", " . $sqlWalker->walkArithmeticPrimary($this->secondDateExpression) . ")";
}
/**
* @inheritdoc
*/
public function getSql(SqlWalker $sqlWalker)
{
return $sqlWalker->walkArithmeticPrimary($this->dividend) . ' DIV ' . $sqlWalker->walkArithmeticPrimary($this->divisor);
}
/**
* @override
*/
public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
{
return 'CHAR_LENGTH(' . $sqlWalker->walkArithmeticPrimary($this->expr1) . ')';
}
/**
* {@inheritDoc}
*/
public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
{
return sprintf('TO_NUMBER(%s)', $sqlWalker->walkArithmeticPrimary($this->expr1));
}
/**
* @override
*/
public function getSql(SqlWalker $sqlWalker)
{
return 'MONTH(' . $sqlWalker->walkArithmeticPrimary($this->date) . ')';
}
/**
* Return the SQL
*
* @param SqlWalker $sqlWalker
*
* @return string
*/
public function getSql(SqlWalker $sqlWalker)
{
return sprintf('IF(%s, %s, %s)', $sqlWalker->walkConditionalExpression($this->condition), $sqlWalker->walkArithmeticPrimary($this->firstResult), $sqlWalker->walkArithmeticPrimary($this->secondResult));
}
/**
* @param \Doctrine\ORM\Query\SqlWalker $sqlWalker
* @return string
*/
public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
{
return 'STRFTIME(' . $sqlWalker->walkArithmeticPrimary($this->format) . ', ' . $sqlWalker->walkArithmeticPrimary($this->date) . ')';
}
public function getSql(SqlWalker $sqlWalker)
{
$parts = array($sqlWalker->walkArithmeticPrimary($this->dateExpression), $sqlWalker->walkStringPrimary($this->dateFormat));
return sprintf('DATE_FORMAT(%s)', implode(', ', $parts));
}
public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
{
return sprintf('TO_CHAR(%s, %s)', $sqlWalker->walkArithmeticPrimary($this->datetime), $sqlWalker->walkArithmeticPrimary($this->fmt));
}
/**
* {@inheritDoc}
*/
public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
{
return sprintf('TRANSLATE(%s, %s, %s)', $sqlWalker->walkArithmeticPrimary($this->expr), $sqlWalker->walkArithmeticPrimary($this->fromString), $sqlWalker->walkArithmeticPrimary($this->toString));
}
/**
* @override
*/
public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
{
return "YEAR(" . $sqlWalker->walkArithmeticPrimary($this->date) . ")";
}
/**
* {@inheritDoc}
*/
public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
{
return sprintf('NULLIF(%s, %s)', $sqlWalker->walkArithmeticPrimary($this->expr1), $sqlWalker->walkArithmeticPrimary($this->expr2));
}
public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
{
return 'IFNULL(' . $sqlWalker->walkArithmeticPrimary($this->expr1) . ', ' . $sqlWalker->walkArithmeticPrimary($this->expr2) . ')';
}
public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
{
return sprintf("COUNT(CASE %s WHEN %s THEN %s END)", $sqlWalker->walkArithmeticPrimary($this->expr1), $sqlWalker->walkArithmeticPrimary($this->expr2), !$this->inverse ? '1 ELSE NULL' : 'NULL ELSE 1');
}
