Issues

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

Issue Type: Bug Created: 2008-12-27T20:15:10.000+0000 Last Updated: 2010-06-18T15:17:21.000+0000 Status: Resolved Fix version(s): Reporter: Nikolay Goldschmidt (ccppprogrammer) Assignee: Ralph Schindler (ralph) Tags: - Zend_Db

Related issues: Attachments:

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:

<pre class="highlight">
--- 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

Posted by Nikolay Goldschmidt (ccppprogrammer) on 2009-05-15T01:54:51.000+0000

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

Posted by Laurent GAUTHIER (laurent) on 2009-06-12T08:52:46.000+0000

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.

Posted by Phil Brown (philbrown) on 2009-11-08T17:42:57.000+0000

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

Posted by Phil Brown (philbrown) on 2009-11-08T17:49:21.000+0000

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

<pre class="highlight">
$select->order(new Zend_Db_Expr('MY_COL ASC NULLS LAST'));

Posted by Ralph Schindler (ralph) on 2010-06-18T15:17:21.000+0000

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.

Have you found an issue?

See the Overview section for more details.

Copyright

© 2006-2016 by Zend, a Rogue Wave Company. Made with by awesome contributors.

This website is built using zend-expressive and it runs on PHP 7.

Contacts