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. ORM

query builder with 'group' causes error

public static function getByDeviceArray($devices)
{
    return self::find([
        'columns' => 'DATE_FORMAT(start_time,"%Y-%m-%d %H:%i") AS hour, COUNT(*) AS count, AVG(duration) as avg_duration',
        'group' => 'DATE_FORMAT(start_time,"%Y-%m-%d %H:%i")'
    ]);
}

If the method call fails

Phalcon\Mvc\Model\Exception: Scanning error before 'DATE_FORMAT(star...' when parsing: SELECT DATE_FORMAT(start_time,"%Y-%m-%d %H:%i") AS hour, COUNT(*) AS count, AVG(duration) as avg_duration FROM [Model\Session] GROUP BY [DATE_FORMAT(start_time,"%Y-%m-%d %H:%i")] (178)

I think it's because of a frame group in []. How to avoid it?



34.6k
Accepted
answer

Change it by:

public static function getByDeviceArray($devices)
{
    return self::find([
        'columns' => 'DATE_FORMAT(start_time,"%Y-%m-%d %H:%i") AS hour, COUNT(*) AS count, AVG(duration) as avg_duration',
        'group' => 'hour'
    ]);
}


3.6k

thanks, it works!