how to use zend paginate without loading all the results of a database

Question

So the way I see zend paginate work is that you do:

$paginator = Zend_Paginator::factory($results);
$paginator->setItemCountPerPage($itemCount);
$paginator->setPageRange($pageRange);

Where $results is the result of loading a bunch of items from a database in the form of

"SELECT * FROM table WHERE etc"

Then zend paginate will count the number of results and generate the page ranges automatically...

But if you do this then you're going to have to fetch all results of a database which I see as a waste since only one page is displayed at a time and therefore you only need to fetch the items for that page...

how do you make zend paginate to be able to calculate the correct page ranges and numbers without resorting to fetching all the results of an entire table?

It's things like this that remind me of why I don't use Zend... — ircmaxell, Nov 11 '10 at 17:43
@ircmaxell Zend is fine, but it takes some work to figure out its intricacies. You can actually make very elegant code without resorting to many class extensions and overrides. — Ankit Aggarwal, Nov 19 '10 at 20:39

score 4 · Accepted Answer · answered Nov 11 '10 at 17:46

Using the factory method you can send an instance of Zend_Db_Select or Zend_Db_Table_Select. If you're class extends Zend_Db_Table_Abstract you can simply build a select query from it and then send this. If not you can create an instance and send it, see the example from the docs:

    $adapter = new Zend_Paginator_Adapter_DbSelect($db->select()->from('posts'));
    $adapter->setRowCount(
        $db->select()
           ->from(
                'item_counts',
                array(
                   Zend_Paginator_Adapter_DbSelect::ROW_COUNT_COLUMN => 'post_count'
                )
             )
    );

$paginator = new Zend_Paginator($adapter)

http://framework.zend.com/manual/en/zend.paginator.usage.html#zend.paginator.usage.dbselect

score 0 · Answer 2 · answered Nov 11 '10 at 17:41

You can't do this with Zend_Paginator you must do this yourself but this is very simple, change the query to this form: SELECT * FROM table WHERE etc LIMIT 0, 30 then for example in this case you will get first 30 rows, if you will be get next then use SELECT * FROM table WHERE etc LIMIT 30, 60

In other way remember, function who will you get framework is always not optimal and working with simplest to implement complexity because for fastest solutions you must use some tighter in use code (i mean you can't use this code in many cases not like in worst complexity code), solutions is more dedicated to the problem, like this, you dedicating to mysql, Paginator don't work on database layer then he working only based on input where input is all rows, this code can't do this faster.

LIMIT 30, 60 is incorrect. You want it to read LIMIT 30, 30. The first argument is the offset, the second is the rowcount. The way you currently have it, it would return 60 rows starting at offset 30. — thetaiko, Nov 11 '10 at 17:46

score 0 · Answer 3 · answered Nov 11 '10 at 17:44

You must use zend paginator differently. Please look at the documentation for the function. Go down to the section 'The DbSelect and DbTableSelect adapter', it provides an example on how to select the count of records, instead of fetching all the records.

score 0 · Answer 4 · answered Nov 12 '10 at 04:56

It depends on what adapter that you use. If you use Zend_Paginator_Adapter_Array, zend paginator will calculate page range and so on by array length. But if you use Zend_Paginator_Adapter_DbSelect, zend paginator will automatically create query select count(*) ...... as zend_paginator_row_count based on your query to calculate rows count and it will not load all of your database results.

score 0 · Answer 5 · answered Nov 19 '10 at 21:01

Zend_Db_Select and Zend_Db_Table_Select are great ways of resolving your issue, an issue I also had to deal with only six months ago.

Let's assume I have a table of Books in my database and I defined a search function to allow me to do full-text searches throughout the table's fields:

Default_Model_DbTable_Books.php:

public class Default_Model_DbTable_Books extends Zend_Db_Table_Abstract {

    // Other variables and methods here...

    public function search($query) {
        // Initialize Zend_Db_Table_Select object
        $select = $this->select(true);

        if (isset($query) && is_string($query)) {
            // Logic for search (don't actually do this, it's horrible for performance)
            $db = $this->getAdapter();
            $where = $db->quoteInto('isbn LIKE ? OR name LIKE ? OR author LIKE ?', $query);
            $search->where($where)
                   ->order('date_published DESC')
                   ->group('author');
        }

        return $select;
    }
}

Default_Model_Mapper_Book.php:

public class Default_Model_Mapper_Book {

    // Defined functions as per Zend Quickstart Mapper classes...

    public function search($query) {
        // Returns Zend_Db_Table_Select object
        return $this->getDbTable()->search($query);
    }
}

Default_BooksController.php:

public function listAction() {
    $mapper = new Default_Model_Mapper_Book();
    $select = $mapper->search($this->_getParam("query"));
    $paginator = Zend_Paginator::factory($select);
}

Why would you add another level of abstraction here (Mapper) since it's always calling a method and returning it's result with no additional logic? — jkulak, Jul 27 '12 at 23:41

how to use zend paginate without loading all the results of a database

5 Answers5

Linked