Date sub - interval mysql

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

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
            )
        ));

@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 :)

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.

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

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

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

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?

Hi,

you have to use library from incubator: https://github.com/phalcon/incubator

that one: https://github.com/phalcon/incubator/blob/master/Library/Phalcon/Db/Dialect/MysqlExtended.php

good luck

This thread is 3 years old. If you have a new question, please start a new thread.