SQL DATES

Question

SQL DATES

Hudson Nicoletti Sep '16

Created Sep '16	Last Reply Sep '16	Replies 4	Views 865	Votes 0

Hudson Nicoletti 37.4k

Sep '16

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 :?

Nikolay Mihaylov · Answer 1 · 2016-09-29T06:45:59-07:00

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.

Lajos Bencz · Answer 2 · 2016-09-29T06:58:43-07:00

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

Hudson Nicoletti · Answer 3 · 2016-09-29T07:40:09-07:00

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

Lajos Bencz · Answer 4 · 2016-09-29T07:49:15-07:00

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

Hudson Nicoletti · Answer 5 · 2016-09-29T09:41:53-07:00

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

Wojciech Ślawski · Answer 6 · 2016-09-29T09:52:47-07:00

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.