[#ZF-5189] Zend_Paginator_Adapter_DbSelect - no way to bind data to placeholders - Zend Framework Issue Tracker

Details

Type: Improvement
Status: Resolved
Priority: Major
Resolution: Fixed
Affects Version/s: 1.7.1
Fix Version/s: None
Component/s: Zend_Paginator
Labels:
None

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?

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Jurrien Stutterheim added a comment - 23/Dec/08 11:02 AM

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.

Show

Jurrien Stutterheim added a comment - 23/Dec/08 11:02 AM 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.

Hide

Permalink

Phil Brown added a comment - 04/Jan/09 4:35 PM

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.

Show

Phil Brown added a comment - 04/Jan/09 4:35 PM 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.

Hide

Permalink

Jurrien Stutterheim added a comment - 30/Mar/09 6:05 PM

Has been resolved with ~~ZF-2017~~ and ~~ZF-3220~~

Show

Jurrien Stutterheim added a comment - 30/Mar/09 6:05 PM Has been resolved with ~~ZF-2017~~ and ~~ZF-3220~~

Hide

Permalink

Aaron S. Hawley added a comment - 07/Jun/10 12:34 PM

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  <Aaron.Hawley <at> 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;
+    }
 }

Show

Aaron S. Hawley added a comment - 07/Jun/10 12:34 PM 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  <Aaron.Hawley <at> 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;
+    }
 }

People

Assignee:

Jurrien Stutterheim

Reporter:

Phil Brown

Vote (0)

Watch (1)

Dates

Created:

08/Dec/08 8:11 PM

Updated:

07/Jun/10 12:34 PM

Resolved:

30/Mar/09 6:05 PM