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

SQL DATES

So i'm trying to get from sql query all invoices that has the due date greater than 30 days ( 1 month ) compared to the current date and i ge the following errors

SELECT * from Manager\Models\ProjectInvoices where recurring = 1 and  NOW() >= DATEADD('month',1,due)
SQLSTATE[42000]: Syntax error or access violation: 1305 FUNCTION rhino.DATEADD does not exist

SELECT * from Manager\Models\ProjectInvoices where recurring = 1 and  NOW() >= due + INTERVAL 1 MONTH
Syntax error, unexpected token INTEGER(1), near to ' MONTH', when parsing: SELECT * from Manager\Models\ProjectInvoices where recurring = 1 and NOW() >= due + INTERVAL 1 MONTH (101)

SELECT * from Manager\Models\ProjectInvoices where recurring = 1 and  NOW() >= due + INTERVAL +1 MONTH
Syntax error, unexpected EOF

can anyone help out :?

edited Sep '16

You have to use RawValue if you want to use MySQL built in functions: https://docs.phalcon.io/en/latest/api/Phalcon_Db_RawValue.html

Are you craeting your query with QueryBuilder or with ORM ?

Other option is just to use simple $this->db->query() and write plain sql query without using model namespaces.

Phalcon ORM only supports standard SQL syntax by default, and INTERVAL is specific to MySQL.

The common solution is to use a db dialect adapter, here's an example: https://github.com/phalcon/incubator/blob/master/Library/Phalcon/Db/Dialect/MysqlExtended.php

So how do i use it ? , i have composer autoloading all my classses , yet the code given in the documentation says that's not valid

Phalcon ORM only supports standard SQL syntax by default, and INTERVAL is specific to MySQL.

The common solution is to use a db dialect adapter, here's an example: https://github.com/phalcon/incubator/blob/master/Library/Phalcon/Db/Dialect/MysqlExtended.php

edited Sep '16

That's just an example, you'll have to place the class in your namespace and use INTERVAL as a function!


    public function getSqlExpression(array $expression, $escapeChar = null, $bindCounts = NULL)
    {
        if ($expression["type"] == 'functionCall') {
            switch ($expression["name"]) {
                case 'DATE_INTERVAL':
                    if (count($expression["arguments"]) != 2) {
                        throw new \Exception('DATE_INTERVAL requires 2 parameters');
                    }
                    $unit = trim($expression["arguments"][1]['value'],"'");
                    switch($unit) {
                        case 'YEAR':
                        case 'MONTH':
                        case 'DAY':
                        case 'HOUR':
                        case 'MINUTE':
                        case 'SECOND':
                            return 'INTERVAL ' . $this->getSqlExpression($expression["arguments"][0]) . ' '.$unit;
                            break;
                    }
                    break;
            }
        }
    }

Usage: SELECT DATE_INTERVAL(2,"YEAR")

Thanks , so i can just input this in my ControllerBase ?

That's just an example, you'll have to place the class in your namespace and use INTERVAL as a function!


   public function getSqlExpression(array $expression, $escapeChar = null, $bindCounts = NULL)
   {
       if ($expression["type"] == 'functionCall') {
           switch ($expression["name"]) {
               case 'DATE_INTERVAL':
                   if (count($expression["arguments"]) != 2) {
                       throw new \Exception('DATE_INTERVAL requires 2 parameters');
                   }
                   $unit = trim($expression["arguments"][1]['value'],"'");
                   switch($unit) {
                       case 'YEAR':
                       case 'MONTH':
                       case 'DAY':
                       case 'HOUR':
                       case 'MINUTE':
                       case 'SECOND':
                           return 'INTERVAL ' . $this->getSqlExpression($expression["arguments"][0]) . ' '.$unit;
                           break;
                   }
                   break;
          }
      }
  }

Usage: SELECT DATE_INTERVAL(2,"YEAR")

edited Sep '16

No obsiously. You need to extend proper class with this method. I guess it's mysql dialect or adapter and use this dialect/adapter in your app. Another option is add extension to dialect.