PageRenderTime 83ms CodeModel.GetById 40ms app.highlight 9ms RepoModel.GetById 31ms app.codeStats 0ms

/Paginator/Adapter/DbSelect.php

https://bitbucket.org/bigstylee/zend-framework
PHP | 290 lines | 117 code | 39 blank | 134 comment | 26 complexity | bfe13c8fe7a37eb8ddf393c2ace18d7a MD5 | raw file
  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-2012 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 24754 2012-05-05 02:30:56Z adamlundrigan $
 20 */
 21
 22/**
 23 * @see Zend_Paginator_Adapter_Interface
 24 */
 25require_once 'Zend/Paginator/Adapter/Interface.php';
 26
 27/**
 28 * @see Zend_Db
 29 */
 30require_once 'Zend/Db.php';
 31
 32/**
 33 * @see Zend_Db_Select
 34 */
 35require_once 'Zend/Db/Select.php';
 36
 37/**
 38 * @category   Zend
 39 * @package    Zend_Paginator
 40 * @copyright  Copyright (c) 2005-2012 Zend Technologies USA Inc. (http://www.zend.com)
 41 * @license    http://framework.zend.com/license/new-bsd     New BSD License
 42 */
 43class 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     * Identifies this adapter for caching purposes.  This value will remain constant for
 75     * the entire life of this adapter regardless of how many different pages are queried.
 76     *
 77     * @var string
 78     */
 79    protected $_cacheIdentifier = null;
 80
 81    /**
 82     * Constructor.
 83     *
 84     * @param Zend_Db_Select $select The select query
 85     */
 86    public function __construct(Zend_Db_Select $select)
 87    {
 88        $this->_select = $select;
 89        $this->_cacheIdentifier = md5($select->assemble());
 90    }
 91
 92    /**
 93     * Returns the cache identifier.
 94     * 
 95     * @return string
 96     */
 97    public function getCacheIdentifier()
 98    {
 99        return $this->_cacheIdentifier;
100    }
101    
102    /**
103     * Sets the total row count, either directly or through a supplied
104     * query.  Without setting this, {@link getPages()} selects the count
105     * as a subquery (SELECT COUNT ... FROM (SELECT ...)).  While this
106     * yields an accurate count even with queries containing clauses like
107     * LIMIT, it can be slow in some circumstances.  For example, in MySQL,
108     * subqueries are generally slow when using the InnoDB storage engine.
109     * Users are therefore encouraged to profile their queries to find
110     * the solution that best meets their needs.
111     *
112     * @param  Zend_Db_Select|integer $totalRowCount Total row count integer
113     *                                               or query
114     * @return Zend_Paginator_Adapter_DbSelect $this
115     * @throws Zend_Paginator_Exception
116     */
117    public function setRowCount($rowCount)
118    {
119        if ($rowCount instanceof Zend_Db_Select) {
120            $columns = $rowCount->getPart(Zend_Db_Select::COLUMNS);
121
122            $countColumnPart = empty($columns[0][2])
123                             ? $columns[0][1]
124                             : $columns[0][2];
125
126            if ($countColumnPart instanceof Zend_Db_Expr) {
127                $countColumnPart = $countColumnPart->__toString();
128            }
129
130            $rowCountColumn = $this->_select->getAdapter()->foldCase(self::ROW_COUNT_COLUMN);
131
132            // The select query can contain only one column, which should be the row count column
133            if (false === strpos($countColumnPart, $rowCountColumn)) {
134                /**
135                 * @see Zend_Paginator_Exception
136                 */
137                require_once 'Zend/Paginator/Exception.php';
138
139                throw new Zend_Paginator_Exception('Row count column not found');
140            }
141
142            $result = $rowCount->query(Zend_Db::FETCH_ASSOC)->fetch();
143
144            $this->_rowCount = count($result) > 0 ? $result[$rowCountColumn] : 0;
145        } else if (is_integer($rowCount)) {
146            $this->_rowCount = $rowCount;
147        } else {
148            /**
149             * @see Zend_Paginator_Exception
150             */
151            require_once 'Zend/Paginator/Exception.php';
152
153            throw new Zend_Paginator_Exception('Invalid row count');
154        }
155
156        return $this;
157    }
158
159    /**
160     * Returns an array of items for a page.
161     *
162     * @param  integer $offset Page offset
163     * @param  integer $itemCountPerPage Number of items per page
164     * @return array
165     */
166    public function getItems($offset, $itemCountPerPage)
167    {
168        $this->_select->limit($itemCountPerPage, $offset);
169
170        return $this->_select->query()->fetchAll();
171    }
172
173    /**
174     * Returns the total number of rows in the result set.
175     *
176     * @return integer
177     */
178    public function count()
179    {
180        if ($this->_rowCount === null) {
181            $this->setRowCount(
182                $this->getCountSelect()
183            );
184        }
185
186        return $this->_rowCount;
187    }
188
189    /**
190     * Get the COUNT select object for the provided query
191     *
192     * TODO: Have a look at queries that have both GROUP BY and DISTINCT specified.
193     * In that use-case I'm expecting problems when either GROUP BY or DISTINCT
194     * has one column.
195     *
196     * @return Zend_Db_Select
197     */
198    public function getCountSelect()
199    {
200        /**
201         * We only need to generate a COUNT query once. It will not change for
202         * this instance.
203         */
204        if ($this->_countSelect !== null) {
205            return $this->_countSelect;
206        }
207
208        $rowCount = clone $this->_select;
209        $rowCount->__toString(); // Workaround for ZF-3719 and related
210
211        $db = $rowCount->getAdapter();
212
213        $countColumn = $db->quoteIdentifier($db->foldCase(self::ROW_COUNT_COLUMN));
214        $countPart   = 'COUNT(1) AS ';
215        $groupPart   = null;
216        $unionParts  = $rowCount->getPart(Zend_Db_Select::UNION);
217
218        /**
219         * If we're dealing with a UNION query, execute the UNION as a subquery
220         * to the COUNT query.
221         */
222        if (!empty($unionParts)) {
223            $expression = new Zend_Db_Expr($countPart . $countColumn);
224
225            $rowCount = $db
226                            ->select()
227                            ->bind($rowCount->getBind())
228                            ->from($rowCount, $expression);
229        } else {
230            $columnParts = $rowCount->getPart(Zend_Db_Select::COLUMNS);
231            $groupParts  = $rowCount->getPart(Zend_Db_Select::GROUP);
232            $havingParts = $rowCount->getPart(Zend_Db_Select::HAVING);
233            $isDistinct  = $rowCount->getPart(Zend_Db_Select::DISTINCT);
234
235            /**
236             * If there is more than one column AND it's a DISTINCT query, more
237             * than one group, or if the query has a HAVING clause, then take
238             * the original query and use it as a subquery os the COUNT query.
239             */
240            if (($isDistinct && ((count($columnParts) == 1 && $columnParts[0][1] == Zend_Db_Select::SQL_WILDCARD) 
241                 || count($columnParts) > 1)) || count($groupParts) > 1 || !empty($havingParts)) {
242                $rowCount->reset(Zend_Db_Select::ORDER);
243                $rowCount = $db
244                               ->select()
245                               ->bind($rowCount->getBind())
246                               ->from($rowCount);
247            } else if ($isDistinct) {
248                $part = $columnParts[0];
249
250                if ($part[1] !== Zend_Db_Select::SQL_WILDCARD && !($part[1] instanceof Zend_Db_Expr)) {
251                    $column = $db->quoteIdentifier($part[1], true);
252
253                    if (!empty($part[0])) {
254                        $column = $db->quoteIdentifier($part[0], true) . '.' . $column;
255                    }
256
257                    $groupPart = $column;
258                }
259            } else if (!empty($groupParts)) {
260                $groupPart = $db->quoteIdentifier($groupParts[0], true);
261            }
262
263            /**
264             * If the original query had a GROUP BY or a DISTINCT part and only
265             * one column was specified, create a COUNT(DISTINCT ) query instead
266             * of a regular COUNT query.
267             */
268            if (!empty($groupPart)) {
269                $countPart = 'COUNT(DISTINCT ' . $groupPart . ') AS ';
270            }
271
272            /**
273             * Create the COUNT part of the query
274             */
275            $expression = new Zend_Db_Expr($countPart . $countColumn);
276
277            $rowCount->reset(Zend_Db_Select::COLUMNS)
278                     ->reset(Zend_Db_Select::ORDER)
279                     ->reset(Zend_Db_Select::LIMIT_OFFSET)
280                     ->reset(Zend_Db_Select::GROUP)
281                     ->reset(Zend_Db_Select::DISTINCT)
282                     ->reset(Zend_Db_Select::HAVING)
283                     ->columns($expression);
284        }
285
286        $this->_countSelect = $rowCount;
287
288        return $rowCount;
289    }
290}