Phalcon datetime in model query error

Question

I am trying to execute a query like this:

Select * from table where created_at > DATE_SUB(NOW(), INTERVAL 1 DAY) in phalcon model query form. But i keep getting the following error:

Syntax error, unexpected token INTEGER(1), near to ' DAY)',

By query building is like below

$donations = Donations::query()
        ->where('created_at > DATE_SUB(NOW(), INTERVAL 1 DAY)')
        ->execute();

The above code gives me that error. Now i have tried like below

$donations = Donations::query()
        ->where('created_at > :holder:')
        ->bind(["holder" => 'DATE_SUB(NOW(), INTERVAL 1 DAY)'])
        ->execute();

Although this binding does not give me an error, it gives me a 0 result but i have a few rows inserted into the table to check this and when i execute the query in phpmyadmin it works correctly, So i assumed there might be a datetime mix up in the phalcon library setup of mine but when i changed from 1 DAY to 1 MONTH there is still not result. Can someone guide me on this.

I haven't used the phalcon framework but based on others I think it will be something to do with the fact the where function needs to know that it's native sql functionality. Try something like ->where(new RawValue('created_at > DATE_SUB(NOW(), INTERVAL 1 DAY)')) — Simon R, Dec 25 '17 at 21:54

Nikolay Mihaylov · Accepted Answer · 2017-12-25T22:03:21.067

INTERVAL, DATE_SUB, NOW() and other similar are MySQL only features and are not supported by PHQL.

You have two options:

1) Rewrite your WHERE condition by using PHP date:

$date = date('Y-m-d', strtotime('-1 DAY')); // Modify according to your date format
$donations = Donations::query()
    ->where('created_at > :holder:')
    ->bind(["holder" => $date)
    ->execute();

2) Extend MySQL with a dialect class:

$di->set('db', function() use ($config) {
    return new \Phalcon\Db\Adapter\Pdo\Mysql(array(
        "host"         => $config->database->host,
        "username"     => $config->database->username,
        "password"     => $config->database->password,
        "dbname"       => $config->database->name,
        "dialectClass" => '\Phalcon\Db\Dialect\MysqlExtended'
    ));
});

Phalcon datetime in model query error

1 Answers1