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

mysql INTERVAL DAY support

code:

$report = StatReport::findFirst([
    'columns' => 'sum(if(report_at = DATE_ADD(report_at, INTERVAL(-WEEKDAY(report_at)) DAY), reg_num,0)) as w1',
    'conditions' => 'report_at >= "2018-09-01"',
]);
var_dump($report);exit;

exception:

Syntax error, unexpected token IDENTIFIER(DAY), near to '), reg_num,0)) as w1 FROM [StatReport] WHERE report_at >= "2018-09-01" LIMIT :APL0:', when parsing: SELECT sum(if(report_at = DATE_ADD(report_at, INTERVAL(-WEEKDAY(report_at)) DAY), reg_num,0)) as w1 FROM [StatReport] WHERE report_at >= "2018-09-01" LIMIT :APL0: (162)<br><pre>#0 [internal function]: Phalcon\Mvc\Model\Query->parse()

sql run in mysql is normal, but cant execute in phalcon:

select sum(if(report_at = DATE_ADD(report_at, INTERVAL(-WEEKDAY(report_at)) DAY), reg_num, 0)) as w1
from stat_report
where report_at >= '2018-09-01';
edited Sep '18

That is SQL specific to MySQL/MariaDB, and not supported directly by PHQL which aims for to support a range of RDBMS out there.

For any specific SQL like Interval, you need to use either raw SQL (PDO), either Query Builder in Phalcon.