library/Zend/Paginator/Adapter/DbSelect.php

   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$
  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 }