Doctrinebundle: DQL aliases not wrapped in quotes

[Sjeijoet]

Hello,

I'm working on a API endpoint that can filter points of interest on a certain distance from the user. As far as I've been able to track this functionality has nothing to do to my problem.

To calculate the distance I'm declaring an alias property that does not exist in the database. I basically programmed a lot of abstraction, blah blah, but the DQL eventually comes to this:

$query = $this->getDoctrine()->getManager()->createQuery('
        SELECT p, ((3959 * acos(cos(radians(:lat)) * cos(radians(p.lat)) * cos(radians(p.lng) - radians(:lng)) + sin(radians(:lat)) * sin(radians(p.lat)))) * 1.609344) AS distance 
        FROM Poi p 
        WHERE distance = :distance 
        ORDER BY p.name ASC
    ')->setParameters(array('lat' => 50.5, 'lng' => 4.5, 'distance' => 10));

        return $query->getResult();

When I run this query, this is the SQL I get in return:

SELECT p0_.id AS id0, p0_.name AS name1, p0_.address AS address2, p0_.number AS number3, 
    p0_.city AS city4, p0_.country AS country5, p0_.zipcode AS zipcode6, p0_.telephone AS telephone7, 
    p0_.email AS email8, p0_.website AS website9, p0_.description AS description10, 
    p0_.start_date AS start_date11, p0_.end_date AS end_date12, p0_.company AS company13,
    p0_.lat AS lat14, p0_.lng AS lng15, p0_.created AS created16, p0_.modified AS modified17, 
    3959 * ACOS(COS(RADIANS(?)) * COS(RADIANS(p0_.lat)) * COS(RADIANS(p0_.lng) - RADIANS(?)) + SIN(RADIANS(?)) * SIN(RADIANS(p0_.lat))) * 1.609344 AS sclr18,
    p0_.poi_type_id AS poi_type_id19, p0_.poi_category_id AS poi_category_id20 
FROM poi p0_ 
WHERE sclr18 = ? 
ORDER BY p0_.name ASC

As you can see 'distance' (named 'sclr18' in SQL) is not wrapped in quotes, which causes it to be handled as a column. I receive this error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'sclr18' in 'where clause'

It seems selection aliases do not get wrapped in "quotes". Is there a reason for that?

I'm sorry if I've posted this in the wrong repository, not sure what component is causing the error...

[stof]

Selection aliases should not be wrapped in quotes. It makes no sense. It is an identifier, not a string.

This is not a DoctrineBundle issue (DoctrineBundle only integrates Doctrine DBAL and ORM into Symfony).

And as your issue comes from the DB, please tell us which RDBMS you are using.

[Sjeijoet]

My apologies, had a brainfart there. I'm using MySQL, I'd still really appreciate a solution if anyone might know what caused this error.

[FabioBatSilva]

Hi @hProjects ,

please try HAVING instead of WHERE

FAIL :

SELECT
    1 + 1 AS sclr0
FROM 
    mysql.user
WHERE 
    sclr0 = 2
LIMIT 1;

OK :

SELECT 
    1 + 1 AS sclr0
FROM 
    mysql.user
HAVING 
    sclr0 = 2
LIMIT 1;

[Sjeijoet]

Ah, of course... Thanks a lot!