Zend_Paginator

The place for general PHP questions and hints for PHP on IBM i
Post Reply
rodn8274
Posts: 12
Joined: Mon Dec 29, 2014 5:22 pm

Zend_Paginator

Post by rodn8274 » Fri Jan 09, 2015 5:23 pm

anyone notice that when using the Zend_Paginator and passing it Zend_Paginator_Adapter_DbTableSelect object with a valid dbTable query that has ORDER BY, the ORDER BY is dropped/removed when paginating through the result set? I'm using ZF1.12 on iSeries with DB2i. I'm finding this specific to the iSeries on DB2i. new Zend_Paginator(new Zend_Paginator_Adapter_DbTableSelect($query)) $query is a dbTableSelect object, works perfectly on MySQL and Postgresql. The See the code below. The $results object is a valid paginator and I have no issues navigating through the pages in a view its just the order by is always ignored when navigating to any other page other than page 1.

It seems people have encountered this issue before but no one posted a solution or insights. http://forums.zendcom/viewtopic.php?f=69&t=9004

Code: Select all

$query->from(array('d' => 'documentp'),
                   array('d.DOCUMENT_DESC', 'd.SCAN_DATE', '
                         'd.DOCUMENT_DATE', 'd.DOCUMENT_SERVER',
                         'd.DOCUMENT_PATH', 'd.DOCUMENT_NAME',
                         'd.LOCATION', 'd.CONTRACT',
                         'd.CUSTNAME', 'd.CUSTNUM', 'd.USERNAME'
                   )
                      );
$query->order(array('scan_date DESC'));

$results = new Zend_Paginator(new Zend_Paginator_Adapter_DbTableSelect($query));


mark397
Posts: 87
Joined: Thu May 30, 2013 6:12 pm

Re: Zend_Paginator

Post by mark397 » Fri Jan 09, 2015 6:02 pm

ZF1 doesn't support the ORDER, see http://forums.zend.com/viewtopic.php?f=68&t=115428. You can make your own start/end around an SQL call like this:

Code: Select all

    SELECT code, name, address
    FROM (
      SELECT row_number() OVER ( ORDER BY code ) AS rid, code, name, address
      FROM contacts
      WHERE name LIKE '%Bob%'
      ) AS t
    WHERE t.rid BETWEEN 20 AND 25;

rodn8274
Posts: 12
Joined: Mon Dec 29, 2014 5:22 pm

Re: Zend_Paginator

Post by rodn8274 » Fri Jan 09, 2015 10:54 pm

Thanks Mark!

I'm curious as to how you implemented a paginator with that SQL. Did you end up extending ZF paginator and add the SQL below or did you just code your own and not use Zend_Paginator(new Zend_Paginator_Adapter_DbTableSelect())

aseiden
Posts: 875
Joined: Thu Apr 09, 2009 5:45 pm

Re: Zend_Paginator

Post by aseiden » Sun Jan 11, 2015 7:36 pm

This issue was corrected in ZF2, incidentally.

mark397
Posts: 87
Joined: Thu May 30, 2013 6:12 pm

Re: Zend_Paginator

Post by mark397 » Mon Jan 12, 2015 7:05 pm

We wrote our own paginator in-house. The best way to implement is to store your main SQL in a variable, then do something like this (assume your SQL is stored in a PHP var called $sql and it contains the ROWNUMBER OVER function):

$sqlCount = "SELECT COUNT(*) AS c FROM ($sql) AS t";
$sqlResults = "SELECT t.* FROM ($sql) as t WHERE t.rid BETWEEN $start AND $end";

Just execute those and you'll have the row count to calculate the number of pages of results based on the number of results per page, and the result set for the current page.

Post Reply