|
1 <?php |
|
2 /** |
|
3 * Zend Framework |
|
4 * |
|
5 * LICENSE |
|
6 * |
|
7 * This source file is subject to the new BSD license that is bundled |
|
8 * with this package in the file LICENSE.txt. |
|
9 * It is also available through the world-wide-web at this URL: |
|
10 * http://framework.zend.com/license/new-bsd |
|
11 * If you did not receive a copy of the license and are unable to |
|
12 * obtain it through the world-wide-web, please send an email |
|
13 * to license@zend.com so we can send you a copy immediately. |
|
14 * |
|
15 * @category Zend |
|
16 * @package Zend_Paginator |
|
17 * @copyright Copyright (c) 2005-2010 Zend Technologies USA Inc. (http://www.zend.com) |
|
18 * @license http://framework.zend.com/license/new-bsd New BSD License |
|
19 * @version $Id: DbSelect.php 20096 2010-01-06 02:05:09Z bkarwin $ |
|
20 */ |
|
21 |
|
22 /** |
|
23 * @see Zend_Paginator_Adapter_Interface |
|
24 */ |
|
25 require_once 'Zend/Paginator/Adapter/Interface.php'; |
|
26 |
|
27 /** |
|
28 * @see Zend_Db |
|
29 */ |
|
30 require_once 'Zend/Db.php'; |
|
31 |
|
32 /** |
|
33 * @see Zend_Db_Select |
|
34 */ |
|
35 require_once 'Zend/Db/Select.php'; |
|
36 |
|
37 /** |
|
38 * @category Zend |
|
39 * @package Zend_Paginator |
|
40 * @copyright Copyright (c) 2005-2010 Zend Technologies USA Inc. (http://www.zend.com) |
|
41 * @license http://framework.zend.com/license/new-bsd New BSD License |
|
42 */ |
|
43 class Zend_Paginator_Adapter_DbSelect implements Zend_Paginator_Adapter_Interface |
|
44 { |
|
45 /** |
|
46 * Name of the row count column |
|
47 * |
|
48 * @var string |
|
49 */ |
|
50 const ROW_COUNT_COLUMN = 'zend_paginator_row_count'; |
|
51 |
|
52 /** |
|
53 * The COUNT query |
|
54 * |
|
55 * @var Zend_Db_Select |
|
56 */ |
|
57 protected $_countSelect = null; |
|
58 |
|
59 /** |
|
60 * Database query |
|
61 * |
|
62 * @var Zend_Db_Select |
|
63 */ |
|
64 protected $_select = null; |
|
65 |
|
66 /** |
|
67 * Total item count |
|
68 * |
|
69 * @var integer |
|
70 */ |
|
71 protected $_rowCount = null; |
|
72 |
|
73 /** |
|
74 * Constructor. |
|
75 * |
|
76 * @param Zend_Db_Select $select The select query |
|
77 */ |
|
78 public function __construct(Zend_Db_Select $select) |
|
79 { |
|
80 $this->_select = $select; |
|
81 } |
|
82 |
|
83 /** |
|
84 * Sets the total row count, either directly or through a supplied |
|
85 * query. Without setting this, {@link getPages()} selects the count |
|
86 * as a subquery (SELECT COUNT ... FROM (SELECT ...)). While this |
|
87 * yields an accurate count even with queries containing clauses like |
|
88 * LIMIT, it can be slow in some circumstances. For example, in MySQL, |
|
89 * subqueries are generally slow when using the InnoDB storage engine. |
|
90 * Users are therefore encouraged to profile their queries to find |
|
91 * the solution that best meets their needs. |
|
92 * |
|
93 * @param Zend_Db_Select|integer $totalRowCount Total row count integer |
|
94 * or query |
|
95 * @return Zend_Paginator_Adapter_DbSelect $this |
|
96 * @throws Zend_Paginator_Exception |
|
97 */ |
|
98 public function setRowCount($rowCount) |
|
99 { |
|
100 if ($rowCount instanceof Zend_Db_Select) { |
|
101 $columns = $rowCount->getPart(Zend_Db_Select::COLUMNS); |
|
102 |
|
103 $countColumnPart = $columns[0][1]; |
|
104 |
|
105 if ($countColumnPart instanceof Zend_Db_Expr) { |
|
106 $countColumnPart = $countColumnPart->__toString(); |
|
107 } |
|
108 |
|
109 $rowCountColumn = $this->_select->getAdapter()->foldCase(self::ROW_COUNT_COLUMN); |
|
110 |
|
111 // The select query can contain only one column, which should be the row count column |
|
112 if (false === strpos($countColumnPart, $rowCountColumn)) { |
|
113 /** |
|
114 * @see Zend_Paginator_Exception |
|
115 */ |
|
116 require_once 'Zend/Paginator/Exception.php'; |
|
117 |
|
118 throw new Zend_Paginator_Exception('Row count column not found'); |
|
119 } |
|
120 |
|
121 $result = $rowCount->query(Zend_Db::FETCH_ASSOC)->fetch(); |
|
122 |
|
123 $this->_rowCount = count($result) > 0 ? $result[$rowCountColumn] : 0; |
|
124 } else if (is_integer($rowCount)) { |
|
125 $this->_rowCount = $rowCount; |
|
126 } else { |
|
127 /** |
|
128 * @see Zend_Paginator_Exception |
|
129 */ |
|
130 require_once 'Zend/Paginator/Exception.php'; |
|
131 |
|
132 throw new Zend_Paginator_Exception('Invalid row count'); |
|
133 } |
|
134 |
|
135 return $this; |
|
136 } |
|
137 |
|
138 /** |
|
139 * Returns an array of items for a page. |
|
140 * |
|
141 * @param integer $offset Page offset |
|
142 * @param integer $itemCountPerPage Number of items per page |
|
143 * @return array |
|
144 */ |
|
145 public function getItems($offset, $itemCountPerPage) |
|
146 { |
|
147 $this->_select->limit($itemCountPerPage, $offset); |
|
148 |
|
149 return $this->_select->query()->fetchAll(); |
|
150 } |
|
151 |
|
152 /** |
|
153 * Returns the total number of rows in the result set. |
|
154 * |
|
155 * @return integer |
|
156 */ |
|
157 public function count() |
|
158 { |
|
159 if ($this->_rowCount === null) { |
|
160 $this->setRowCount( |
|
161 $this->getCountSelect() |
|
162 ); |
|
163 } |
|
164 |
|
165 return $this->_rowCount; |
|
166 } |
|
167 |
|
168 /** |
|
169 * Get the COUNT select object for the provided query |
|
170 * |
|
171 * TODO: Have a look at queries that have both GROUP BY and DISTINCT specified. |
|
172 * In that use-case I'm expecting problems when either GROUP BY or DISTINCT |
|
173 * has one column. |
|
174 * |
|
175 * @return Zend_Db_Select |
|
176 */ |
|
177 public function getCountSelect() |
|
178 { |
|
179 /** |
|
180 * We only need to generate a COUNT query once. It will not change for |
|
181 * this instance. |
|
182 */ |
|
183 if ($this->_countSelect !== null) { |
|
184 return $this->_countSelect; |
|
185 } |
|
186 |
|
187 $rowCount = clone $this->_select; |
|
188 $rowCount->__toString(); // Workaround for ZF-3719 and related |
|
189 |
|
190 $db = $rowCount->getAdapter(); |
|
191 |
|
192 $countColumn = $db->quoteIdentifier($db->foldCase(self::ROW_COUNT_COLUMN)); |
|
193 $countPart = 'COUNT(1) AS '; |
|
194 $groupPart = null; |
|
195 $unionParts = $rowCount->getPart(Zend_Db_Select::UNION); |
|
196 |
|
197 /** |
|
198 * If we're dealing with a UNION query, execute the UNION as a subquery |
|
199 * to the COUNT query. |
|
200 */ |
|
201 if (!empty($unionParts)) { |
|
202 $expression = new Zend_Db_Expr($countPart . $countColumn); |
|
203 |
|
204 $rowCount = $db->select()->from($rowCount, $expression); |
|
205 } else { |
|
206 $columnParts = $rowCount->getPart(Zend_Db_Select::COLUMNS); |
|
207 $groupParts = $rowCount->getPart(Zend_Db_Select::GROUP); |
|
208 $havingParts = $rowCount->getPart(Zend_Db_Select::HAVING); |
|
209 $isDistinct = $rowCount->getPart(Zend_Db_Select::DISTINCT); |
|
210 |
|
211 /** |
|
212 * If there is more than one column AND it's a DISTINCT query, more |
|
213 * than one group, or if the query has a HAVING clause, then take |
|
214 * the original query and use it as a subquery os the COUNT query. |
|
215 */ |
|
216 if (($isDistinct && count($columnParts) > 1) || count($groupParts) > 1 || !empty($havingParts)) { |
|
217 $rowCount = $db->select()->from($this->_select); |
|
218 } else if ($isDistinct) { |
|
219 $part = $columnParts[0]; |
|
220 |
|
221 if ($part[1] !== Zend_Db_Select::SQL_WILDCARD && !($part[1] instanceof Zend_Db_Expr)) { |
|
222 $column = $db->quoteIdentifier($part[1], true); |
|
223 |
|
224 if (!empty($part[0])) { |
|
225 $column = $db->quoteIdentifier($part[0], true) . '.' . $column; |
|
226 } |
|
227 |
|
228 $groupPart = $column; |
|
229 } |
|
230 } else if (!empty($groupParts) && $groupParts[0] !== Zend_Db_Select::SQL_WILDCARD && |
|
231 !($groupParts[0] instanceof Zend_Db_Expr)) { |
|
232 $groupPart = $db->quoteIdentifier($groupParts[0], true); |
|
233 } |
|
234 |
|
235 /** |
|
236 * If the original query had a GROUP BY or a DISTINCT part and only |
|
237 * one column was specified, create a COUNT(DISTINCT ) query instead |
|
238 * of a regular COUNT query. |
|
239 */ |
|
240 if (!empty($groupPart)) { |
|
241 $countPart = 'COUNT(DISTINCT ' . $groupPart . ') AS '; |
|
242 } |
|
243 |
|
244 /** |
|
245 * Create the COUNT part of the query |
|
246 */ |
|
247 $expression = new Zend_Db_Expr($countPart . $countColumn); |
|
248 |
|
249 $rowCount->reset(Zend_Db_Select::COLUMNS) |
|
250 ->reset(Zend_Db_Select::ORDER) |
|
251 ->reset(Zend_Db_Select::LIMIT_OFFSET) |
|
252 ->reset(Zend_Db_Select::GROUP) |
|
253 ->reset(Zend_Db_Select::DISTINCT) |
|
254 ->reset(Zend_Db_Select::HAVING) |
|
255 ->columns($expression); |
|
256 } |
|
257 |
|
258 $this->_countSelect = $rowCount; |
|
259 |
|
260 return $rowCount; |
|
261 } |
|
262 } |