zend paginator problem

Question

I want to use Zend Paginator in my project with a raw sql query(not Dbselect). I can't use Dbselect, because I have several subqueries in the main sql query. From the manual I found that the only way is to use pagination on resulting array like this

$paginator = Zend_Paginator::factory($array);

but I have a lot of records in the query and it looks like a bad idea to me. Is there any way to solve this problem?

Answer 1

You can use subqueries in a db select like this:

    $select = $this->_db->select();
    $select->from( 'customer', array( 'id', 'first_name', 'family_name', 'company', 'phone', 'email' ) );
    $select->joinLeft( 'sale', 'customer.id = sale.customer_id and sale.id in (select max( id ) from sale group by customer_id)', array( 'sale_id' => 'id', 'billing_phone', 'shipping_phone', 'billing_city', 'billing_country', 'created_on' ) );

(The query above returns the customer name, plus contact details from their most recent purchase)

Answer 2

If your query is really that complex that you cannot use Zend_Db_Select you can resort to writing your own Zend_Paginator_Adapter which is not as complicated as it might sound.

Your appropriate class only must implement Zend_Paginator_Adapter_Interface to allow you to pass the class into the Zend_Paginator constructor.

The following is a simple example - you can take it as a starting-point...

class App_Model_ListOfItems implements Zend_Paginator_Adapter_Interface
{
    /**
     * @var Zend_Db_Adapter_Abstract
     */
    protected $_db;

    /**
     * @param Zend_Db_Adapter_Abstract $db
     */
    public function __construct(Zend_Db_Adapter_Abstract $db)
    {
        $this->_db = $db;
    }

    /**
     * @implements Zend_Paginator_Adapter_Interface
     * @return integer
     */
    public function count()
    {
        return (int)$this->_db->fetchOne('SELECT COUNT(*) FROM <<add your query to determine the total row count here>>');
    }

    /**
     * @implements Zend_Paginator_Adapter_Interface
     * @param  integer $offset
     * @param  integer $itemCountPerPage
     * @return array
     */
    public function getItems($offset, $itemCountPerPage)
    {
        $sql = 'SELECT <<add your columns, tables, joins and subqueries as needed>> LIMIT ' . 
            (int)$offset . ', ' . (int)$itemCountPerPage;
        return $this->_db->fetchAll($sql);
    }
}

$paginator = new Zend_Paginator(new App_Model_ListOfItems($db));