Date sub - interval mysql

Hello. I am trying to search some records based on an interval:

        $rates = ExchangeRate::find(array(
            "exchange_date BETWEEN DATE_SUB(DATE(:date:),INTERVAL 3 DAY) AND :date:",
            "bind" => array(
                'date' => $date
            ),
            "cache" => array(
                "key" => "exchange_rate_$date",
                "lifetime" => 3600 * 24
            )
        ));

This will end up with the following error:

"Syntax error, unexpected token INTEGER(3), near to ' DAY) AND :date:', when parsing: SELECT [App\\Core\\Models\\Exchange\\ExchangeRate].* FROM [App\\Core\\Models\\Exchange\\ExchangeRate] WHERE exchange_date BETWEEN DATE_SUB(DATE(':date:'),INTERVAL 3 DAY) AND :date: (172

I have tried to bind the entire mysql function as \Phalcon\Db\RawValue(), but also without success:

new \Phalcon\Db\RawValue("DATE_SUB(DATE($date),INTERVAL 3 DAY)");
  • I know how how to add and susbtract days via PHP, please comment strictly related to the ORM.

Thanks you.



84.5k

Interval is not supported since it's a MySQL only feature



34.0k
edited Mar '14

Ok. So I should relay on raw sql or php in this case, correct ?

I'd +1 a request for getting this functionality added. In my opinion MySQL is by far the most commonly used DBMS for web applications, so some effort should be expended to match functionality provided by MySQL.

@Calin - It doesn't look like you NEED to use the MySQL function. Can't you just do the date math in PHP?:

$date2 = strtotime('-3 days',$date)

 $rates = ExchangeRate::find(array(
            "exchange_date BETWEEN :date2: AND :date:",
            "bind" => array(
                'date' => $date
                'date2'=> $date2
            ),
            "cache" => array(
                "key" => "exchange_rate_$date",
                "lifetime" => 3600 * 24
            )
        ));


34.0k

@quasipickle , after Phalcon's answer, i am using php. But in general, it would be nice to be able to use more mysql built-in functions. On the other hand, if your app will grow and you will need to switch from Mysql, it is a goot approach NOT TO relay / use the built in functions. I am also not sure why if i am using Phalcon\Db\Adapter\Pdo\Mysql i can't use mysql at it's full power. Some answers on this "issues" would be appreciated :)

edited Oct '14

I consider the "what if you change your RDMS" problem to be a non-issue. How many times have you changed the database back-end on one of your apps? If you do need to switch from MySQL to, say, Oracle because your app suddenly has 1 million+ users, you're obviously going to be revisiting the code anyway.

It seems like the adapter still has to parse PHQL statements into statements MySQL will be able to read. I agree though - if a separate adapter exists for a particular brand of SQL, why not provide all the functionality for that particular brand.



84.5k
edited Mar '14

I have added an extended MySQL dialect to the incubator that introduces handling of some specific PHQL functions that add support for INTERVAL alike expressions and fulltext searchs:

Changing the default dialect:

$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'
    ));
});

Using the custom functions:

// SELECT `customers`.`created_at` - INTERVAL 7 DAY FROM `customers`
$data = $this->modelsManager->executeQuery(
    'SELECT created_at - DATE_INTERVAL(7, "DAY") FROM App\Models\Customers'
);

// SELECT `customers`.`id`, `customers`.`name` FROM `customers` WHERE MATCH(`customers`.`name`, `customers`.`description`) AGAINST ("+CEO")
$data = $this->modelsManager->executeQuery(
    'SELECT id, name FROM App\Models\Customers WHERE FULLTEXT_MATCH(name, description, "+CEO")'
);

// SELECT `customers`.`id`, `customers`.`name` FROM `customers` WHERE MATCH(`customers`.`name`, `customers`.`description`) AGAINST ("+CEO" IN BOOLEAN MODE)
$data = $this->modelsManager->executeQuery(
    'SELECT id, name FROM App\Models\Customers WHERE FULLTEXT_MATCH_BMODE(name, description, "+CEO")'
);

https://github.com/phalcon/incubator/tree/master/Library/Phalcon/Db#dialectmysqlextended



268

@quasipickle: the issue, when using PHP's date, is that you don't rely on the MySQL server's date. If the 2 servers are not in sync, you're running into a lot of trouble. You must use the MySQL's server's date. Even if the 2 are on the same server, NOW() is not exactly the same between your PHP statement and the effective request on MySQL.

@phalcon: thanks for implementing this.



268

@phalcon: you can't implement this on phalcon 2.0.0, as the getSqlExpression is «final». Is there any other workaround?



2.1k

When I add this line

"dialectClass" => '\Phalcon\Db\Dialect\MysqlExtended'

PHP return the error:

Fatal error: Class 'Phalcon\Db\Dialect\MysqlExtended' not found

How can I fix this issue?