Syntax Error with PostgreSQL

[Tilotiti]

Hi everyone,

I'm trying to implement the bundle, but I've an error :

An exception occurred while executing 'SELECT COUNT(s0_.id) AS sclr_0, 12742 * ASIN(SQRT(POWER(SIN(((SELECT `lat` FROM `craue_geo_postalcode` WHERE `country` = ? AND `postal_code` = ?) - (SELECT `lat` FROM `craue_geo_postalcode` WHERE `country` = ? AND `postal_code` = s0_.zipcode)) * PI()/360), 2) + COS((SELECT `lat` FROM `craue_geo_postalcode` WHERE `country` = ? AND `postal_code` = ?) * PI()/180) * COS((SELECT `lat` FROM `craue_geo_postalcode` WHERE `country` = ? AND `postal_code` = s0_.zipcode) * PI()/180) * POWER(SIN(((SELECT `lng` FROM `craue_geo_postalcode` WHERE `country` = ? AND `postal_code` = ?) - (SELECT `lng` FROM `craue_geo_postalcode` WHERE `country` = ? AND `postal_code` = s0_.zipcode)) * PI()/360), 2))) AS sclr_1 FROM sell s0_ WHERE sclr_1 < ?' with params ["fr", "75004", "fr", "fr", "75004", "fr", "fr", "75004", "fr", "50"]:

SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "`"
LINE 1: ..., 12742 * ASIN(SQRT(POWER(SIN(((SELECT `lat` FROM `craue_geo...

My QueryBuilder is :

$dql = $this->createQueryBuilder('sell');
$dql->select('COUNT(sell)');
$dql->addSelect('DISTANCE_ZIPCODE(:country, :zipcode, :country, sell.zipcode) AS HIDDEN distance');
$dql->andWhere('distance < :distance');
$dql->setParameter('distance', $distance);
$dql->setParameter('zipcode', $zipcode);
$dql->setParameter('country', 'fr');

return $dql->getQuery()->getSingleScalarResult();

I'm using Symfony 3.2.6, Doctrine 2.5 and PostgreSQL 9.6.

Have you got any idea what causes this ? It seems that the "`" sign is not recognize by Postgresql. Anyone tested on this SGBD ?

Thank you.

[Tilotiti]

Ok, so i've modified the query to remove the "`", no more syntax error ... But now, postgresql throw an error, as the "distance" (or "radius") alias can't be found.

After a littler research, postgresql doesn't allow ALIASES in WHERE clause ... So It can't work ...

[Tilotiti]

Found it ! We just have to use the function directly in the WHERE Clause !