Phalcon PHQL GROUP BY error

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(\n 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\n 
FROM \Baseapp\Models\ClassifiedsAds\n 
LEFT JOIN \Baseapp\Models\ClassifiedsImages ON \Baseapp\Models\ClassifiedsImages.ad_id = \Baseapp\Models\ClassifiedsAds.id\n 
WHERE \Baseapp\Models\ClassifiedsAds.status = 'active' Having distance <= 100\n 
GROUP BY \Baseapp\Models\ClassifiedsAds.id\n 
ORDER BY distance ASC\n )

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)?

What is the `$dist` in your select? `SELECT $dist $ads_model.id AS id` — Timothy, Jun 02 '16 at 19:38
@Timothy you can see what `$dist` produces if you look at the generated query, but it's not relevant to the question. — Ally, Jun 02 '16 at 20:33
I see you are using `having` before `group by`. `group by` should come before `having` — Timothy, Jun 02 '16 at 20:35
@Timothy thanks that was the problem. I had tagged the `HAVING` on to the end of the `WHERE`. I've moved it to after the `GROUP BY` now and all's ok. I could give you credit if you added it as an answer! — Ally, Jun 02 '16 at 22:12
Yes, do that, then ping us, so we can sprinkle points for a keen eye — Drew, Jun 03 '16 at 03:41

score 1 · Accepted Answer · edited Jun 03 '16 at 06:39

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.

edited Jun 03 '16 at 06:39

yergo

4,761
2
19
41

answered Jun 03 '16 at 05:36

Timothy

2,004
3
23
29

Even if this does not resolve the problem, +1 for pointing out that `HAVING` should be used between `GROUP BY` and `ORDER` – yergo Jun 03 '16 at 06:37
It turns out that `Phalcon\Pagination` doesn't seem to work with PHQL so I re did it with query builder, but now when I add the paginator, it seems to break the query when it ads `SELECT COUNT() AS rowcount` to the query. So I still haven't actually got any where, but thanks for pointing out the error of my ways. – Ally Jun 03 '16 at 12:40
Could you update your question with the code from the query builder? – Timothy Jun 03 '16 at 13:36
Or better yet, create a new question. As your new question doesn't really reflect the original title. – Timothy Jun 03 '16 at 13:37
1

@Timothy As I think you found, I added a new question. Also you have been sprinkled with fairy dust for answering this question. I hope the fairy dust is to your liking and doesn't sting when it gets into your eyes! ;) – Ally Jun 04 '16 at 00:21

Phalcon PHQL GROUP BY error

1 Answers1