Phalcon PHQL GROUP BY错误

Question

When adding GROUP BY to my query, Phalcon is giving me an error - Phalcon\Mvc\Model\Exception: Syntax error, unexpected token GROUP, near to ' BY...

Here's my code;

$phql = "
    SELECT $dist $ads_model.id AS id, $ads_model.title AS title, $ads_model.description AS description, $ads_model.country AS country, $ads_model.city AS city, $ads_model.latitude AS latitude, $ads_model.longitude AS longitude, $image_model.file AS image
    FROM $ads_model
    LEFT JOIN $image_model ON $image_model.ad_id = $ads_model.id
    WHERE $where
    GROUP BY $ads_model.id
    $order
    ";
    $ads = $this->modelsManager->executeQuery($phql);

Here's the query being generated;

Phalcon\Mvc\Model\Manager->executeQuery(
 SELECT (3959 * acos( cos( radians( xxxx ) ) * cos( radians( \Baseapp\Models\ClassifiedsAds.latitude ) ) * cos( radians( \Baseapp\Models\ClassifiedsAds.longitude ) - radians( xxxx ) ) + sin( radians( xxxx ) ) * sin( radians( \Baseapp\Models\ClassifiedsAds.latitude ) ) ) ) AS distance, 
\Baseapp\Models\ClassifiedsAds.id AS id, \Baseapp\Models\ClassifiedsAds.title AS title, \Baseapp\Models\ClassifiedsAds.description AS description, \Baseapp\Models\ClassifiedsAds.country AS country, \Baseapp\Models\ClassifiedsAds.city AS city, \Baseapp\Models\ClassifiedsAds.latitude AS latitude, \Baseapp\Models\ClassifiedsAds.longitude AS longitude, \Baseapp\Models\ClassifiedsImages.file AS image
 
FROM \Baseapp\Models\ClassifiedsAds
 
LEFT JOIN \Baseapp\Models\ClassifiedsImages ON \Baseapp\Models\ClassifiedsImages.ad_id = \Baseapp\Models\ClassifiedsAds.id
 
WHERE \Baseapp\Models\ClassifiedsAds.status = 'active' Having distance <= 100
 
GROUP BY \Baseapp\Models\ClassifiedsAds.id
 
ORDER BY distance ASC
 )

Running a simplified version of the query in MySQL works as expected. Why is Phalcon screwing up on the GROUP BY clause? The query runs if GROUP BY is left out. How do I get it to work in PHQL?

Also as a completely related question: I am using PHQL for this as Phalcon's pagination doesn't seem to work with raw MySQL. Is there a way to get Phalcon pagination to work with raw (with out making a custom pagination)?

Answer 1

You have a mistake in the order of your clauses.

You must declare the HAVING clause after your GROUP BY.

[GROUP BY {col_name | expr | position}
  [ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
  [ASC | DESC], ...]

Refer to the mysql docs for a complete example.