We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. Database

Syntax error when try to use order by with GROUP_CONCAT

Dears,

I am try to run a my sql query using phalcon custom query.

Here is the query

$query = new Phalcon\Mvc\Model\Query\Builder();
$query->columns(array('parent_id', "activity_user" => "GROUP_CONCAT(CONCAT(module,'|','element_id') ORDER   BY created_at )"));
$query->from('Feeds');
$query->groupBy(array( 'module', 'parent_id'));
$query->orderBy("created_at DESC");
$query->limit($perPage,$startValue);

Once I try to use the Ordering with Group by I got the following snytax error

Syntax error, unexpected token ORDER, near to ' BY created_at )

I tried to run the query thorugh mysql and it's worked with no error.

So why this error comes and how I can fix it.

Thanks



33.8k

Maybe are the extra spaces between ORDER and BY.



6.2k

Dear RompePC,

No it's not the space.

Thanks



7.9k


33.8k

Maybe your column definition has a bad syntax. Try:

"GROUP_CONCAT(CONCAT(module, '|', element_id) ORDER BY created_at) AS activity_user"


6.2k

Dear RompePC,

I tried it but it's not working too. as I told you I tried it in MySQL and it working which means that the problem not in my code the problem mainly with phalcon itself.

Thanks for your immediate response

On Sat, Sep 27, 2014 at 7:17 PM, RompePC [email protected] wrote:



33.8k

Then maybe the problem is when Phalcon is sending the SQL. Create a SQL log and see what happens ( https://docs.phalcon.io/en/latest/reference/db.html#logging-sql-statements )



8.1k
edited Sep '14

Even better, if you turn on logging in Mysql.

And tail /var/lib/queries.log

There you will find the truth



98.9k

This part:

$query->columns(array('parent_id', "activity_user" => "GROUP_CONCAT(CONCAT(module,'|','element_id') ORDER    BY created_at )"));

It's intended to specify the columns part of the SELECT statement: 

SELECT GROUP_CONCAT(CONCAT(module,'|','element_id') ORDER    BY created_at AS activity_user  FROM Feeds

This is totally not a valid SQL statement



6.2k

Dear Phalcon,

it's a true query, here is a screenshot of query success using phpmyadmin.

You got error because you missed the end braces for group concat



98.9k

PHQL does not support that syntax, remember that you're creating PHQL statements not raw SQL: https://docs.phalcon.io/es/latest/reference/phql.html

If you want to use raw SQL you can use the raw database component: