ZF-5359: order() function doesn't accept postgresql's [NULLS { FIRST | LAST }]

Description

hi,

version: zend framework 1.7.2 os: linux rhel 5.2 x64 php: PHP 5.1.6 (cli) (built: Jul 16 2008 19:52:52)

order() function doesn't accept postgresql's [NULLS { FIRST | LAST }]. [NULLS { FIRST | LAST }] is new future of postgresql 8.3.x. more info about syntax: http://postgresql.org/docs/8.3/…

Patch:


--- Select.php- 2008-12-28 07:11:52.000000000 +0300
+++ Select.php  2008-12-28 08:54:25.000000000 +0300
@@ -80,6 +80,11 @@
     const SQL_ON         = 'ON';
     const SQL_ASC        = 'ASC';
     const SQL_DESC       = 'DESC';
+    const SQL_IS         = 'IS';
+    const SQL_NOT        = 'NOT';
+    const SQL_NULL       = 'NULL';
+    const SQL_NULLS_FIRST = 'NULLS FIRST';
+    const SQL_NULLS_LAST = 'NULLS LAST';

     /**
      * Zend_Db_Adapter_Abstract object.
@@ -554,13 +559,10 @@
                 if (empty($val)) {
                     continue;
                 }
-                $direction = self::SQL_ASC;
-                if (preg_match('/(.*\W)(' . self::SQL_ASC . '|' . self::SQL_DESC . ')\b/si', $val, $matches)) {
+                if (preg_match('/^(.*?|)\s*(' . self::SQL_IS . '|)\s*(' . self::SQL_NOT . '|)\s*(' . self::SQL_NULL . '|)\s*(' . self::SQL_ASC . '|' . self::SQL_DESC . '|)\s*(' . self::SQL_NULLS_FIRST . '|' . self::SQL_NULLS_LAST . '|)$/i', $val, $matches)) {
                     $val = trim($matches[1]);
-                    $direction = $matches[2];
-                }
-                if (preg_match('/\(.*\)/', $val)) {
-                    $val = new Zend_Db_Expr($val);
+                    $direction = ($matches[2] ? " " . $matches[2] : "") . ($matches[3] ? " " . $matches[3] : "") . ($matches[4] ? " " . $matches[4] : "") . ($matches[5] ? " " . $matches[5] : " ASC") . ($matches[6] ? " " . $matches[6] : "");
+                    $direction = trim($direction);
                 }
                 $this->_parts[self::ORDER][] = array($val, $direction);
             }

Comments

Zend Framework 1.8.1 still have this issue. Please commit my patch.

Hi all!

First of all thanks for the patch!

Just to share my own experience: this patch is good to fix the issue known but sadly it creates an other one. After the patch is applied we can't use the RANDOM() order.

The SQL query looks like this: ...ORDER BY "RANDOM()"...

but should be like this: ...ORDER BY RANDOM()...

Do you know how to fix the new problem and keep the [NULLS { FIRST | LAST }] active?

Thanks in advance.

If you needed any further incentive to fix this bug, Oracle also supports the same "NULLS {FIRST|LAST}" order-by syntax.

Forgot to add, you can achieve the desired result using a Zend_Db_Expr, for example


$select->order(new Zend_Db_Expr('MY_COL ASC NULLS LAST'));

NULL (first | last) is not standard sql. This is a database specific feature. Since you can use Zend_Db_Expr to inject database specific SQL, that would be the ideal route to go for you if you would like to use that feature set. Zend_Db_Select, et. al. need to stay as vendor neutral with regards to SQL query generation as possible.