ZF-5189: Zend_Paginator_Adapter_DbSelect - no way to bind data to placeholders

Description

Given a query with named parameter placeholders, eg


SELECT * FROM TABLE WHERE STATUS = :status

there's currently no way to pass the array of bind variables to the pagination adapter for use in the getItems() or count() / setRowCount() methods.

Given that this is the recommended method of parameter passing (at least for non-string values), shouldn't it be supported by the adapter?

Comments

A Zend_Db_Statement object isn't flexible enough to be used in a similar way as a Zend_Db(_Table)_Select object. The current Zend_Db_Select adapter works by modifying the original query and converting it into a COUNT query to determine the amount of items in the result set. Zend_Db_Statement uses plain strings for the unbound query. At the moment I don't see a way to create decent support for Zend_Db_Statement. Suggestions are welcome, but I'll postpone this issue until a nice way to support this is offered. Please use Zend_Db(_Table)_Select for paginating Db results in the mean while.

I've solved this by extending Zend_Paginator_Adapter_DbSelect with the following alterations.


protected $_bind = array();

public function __construct(Zend_Db_Select $select, array $bind = array())
{
    parent::__construct($select);
    $this->setBindValues($bind);
}

public function setBindValues(array $bind)
{
    $this->_bind = $bind;
    return $this;
}

in setRowCount()


$stmt = $rowCount->getAdapter()->query($rowCount, $this->_bind);
$stmt->setFetchMode(Zend_Db::FETCH_ASSOC);
$result = $stmt->fetch();

and similarly in getItems()


$stmt = $this->_select->getAdapter()->query($this->_select, $this->_bind);
$stmt->setFetchMode($this->_select->getAdapter()->getFetchMode());
return $stmt->fetchAll();

Of course, when using the Zend_Paginator::factory() method, the bind data needs to be added after the paginator is created. Otherwise, the array can be set using the adapter's constructor.

Has been resolved with ZF-2017 and ZF-3220

Phil Brown's suggestion in the comment above is a good one.

It should be accepted into ZF in my opinion.

Here's a patch that works for me.

2010-06-03  Aaron S. Hawley   vtinfo.com>

    * Zend/Paginator/Adapter/DbSelect.php: Pass bind data values
          to Zend_Db_Select.

--- Zend/Paginator/Adapter/DbSelect.php 2010-04-29 12:37:49.475322300 -0400
+++ Zend/Paginator/Adapter/DbSelect.php 2010-06-03 16:57:27.698390900 -0400
@@ -64,6 +64,13 @@
     protected $_select = null;
 
     /**
+     * Bind values
+     *
+     * @var array
+     */
+    protected $_bind = array();
+
+    /**
      * Total item count
      *
      * @var integer
@@ -118,7 +125,8 @@
                 throw new Zend_Paginator_Exception('Row count column not found');
             }
 
-            $result = $rowCount->query(Zend_Db::FETCH_ASSOC)->fetch();
+            $result = $rowCount->query(Zend_Db::FETCH_ASSOC, $this->_bind)
+                               ->fetch();
 
             $this->_rowCount = count($result) > 0 ? $result[$rowCountColumn] : 0;
         } else if (is_integer($rowCount)) {
@@ -146,7 +154,7 @@
     {
         $this->_select->limit($itemCountPerPage, $offset);
 
-        return $this->_select->query()->fetchAll();
+        return $this->_select->bind($this->_bind)->query()->fetchAll();
     }
 
     /**
@@ -259,4 +267,15 @@
 
         return $rowCount;
     }
+
+    /**
+     * Set the data to bind to placeholders.
+     *
+     * @return Zend_Db_Select
+     */
+    public function setBindValues(array $bind)
+    {
+        $this->_bind = $bind;
+        return $this;
+    }
 }