SelectQuery.php

<?php
/* vim: set expandtab tabstop=4 shiftwidth=4 softtabstop=4 enc=utf8: */
/**
 * @author Alexey Zakhlestin
 * @package mysql-query-builder
 **/
/*
 MySQL Query Builder
 Copyright © 2005-2009 Alexey Zakhlestin <indeyets@gmail.com>
 Copyright © 2005-2006 Konstantin Sedov <kostya.online@gmail.com>

 This library is free software; you can redistribute it and/or
 modify it under the terms of the GNU Lesser General Public
 License as published by the Free Software Foundation; either
 version 2.1 of the License, or (at your option) any later version.

 This library is distributed in the hope that it will be useful,
 but WITHOUT ANY WARRANTY; without even the implied warranty of
 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
 Lesser General Public License for more details.

 You should have received a copy of the GNU Lesser General Public
 License along with this library; if not, write to the Free Software
 Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
*/

/**
 * This class contains logic of "SELECT" queries
 *
 * @package mysql-query-builder
 * @author Alexey Zakhlestin
 */
class SelectQuery extends BasicQuery
{
 private $limit = null;
 private $selects = null;
 private $groupby = null;
 private $havings = null;
 private $indices = null;

 private $distinct = false;

 /**
 * Creates new SELECT-query object.
 * By default, it is equivalent of "SELECT t0.* FROM t0, t1, t2, tN", where t0-tN are tables given to this constructor
 *
 * @param mixed $tables 
 */
 public function __construct($tables)
 {
 parent::__construct($tables);
 $this->setSelect(array(new AllFields()));
 }

 /**
 * Specifies, which columns should be selected.
 * $_select can be instance of AllFields, MQB_Field or array of such instances.
 * If $distinct is set to TRUE, 'SELECT DISTINCT …' query shall be used
 *
 * @param mixed $_selects 
 * @param bool $distinct 
 * @return void
 * @throws InvalidArgumentException, RangeException
 */
 public function setSelect($_selects, $distinct = false)
 {
 if (!is_array($_selects)) {
 $_selects = array($_selects);
 }

 if (count($_selects) == 0) {
 throw new InvalidArgumentException('Nothing to select');
 }

 if (!is_bool($distinct)) {
 throw new InvalidArgumentException('"distinct" parameter should be boolean');
 }

 foreach ($_selects as $s) {
 if (!($s instanceof MQB_Field) and !($s instanceof AllFields))
 throw new RangeException('Allowed values are objects of the following classes: Field, AllFields, sqlFunction and Aggregate');
 }

 $this->selects = $_selects;
 $this->distinct = $distinct;
 $this->reset();
 }

 /**
 * Specifies, which index(es) should be preferred for the first table of query
 * $indices should be either string or array of strings
 *
 * @param mixed $indices 
 * @return void
 */
 public function setIndices($indices)
 {
 if (!is_array($indices)) {
 $indices = array($indices);
 }

 $this->indices = $indices;
 }

 /**
 * Specifies, if the query should use "GROUP BY" clause.
 * MQB_Field of array of MQB_Fields is allowed as parameter
 *
 * @param mixed $orderlist 
 * @return void
 * @throws InvalidArgumentException
 */
 public function setGroupby($orderlist)
 {
 if (!is_array($orderlist))
 $orderlist = array($orderlist);

 foreach ($orderlist as $field)
 if (!($field instanceof MQB_Field))
 throw new InvalidArgumentException('setGroupBy takes only [array of] MQB_Fields as parameter');

 $this->groupby = $orderlist;
 $this->reset();
 }

 /**
 * setup "LIMIT" clause of Query.
 *
 * @param integer $limit 
 * @param integer $offset 
 * @return void
 * @throws InvalidArgumentException
 */
 public function setLimit($limit, $offset=0)
 {
 if (!is_numeric($limit) or !is_numeric($offset))
 throw new InvalidArgumentException('Limit should be specified using numerics');

 $this->limit = array($limit, $offset);
 }

 /**
 * Accessor, which returns internal "GROUP BY" array
 *
 * @return array
 */
 public function showGroupBy()
 {
 return $this->groupby;
 }

 protected function getSql(array &$parameters)
 {
 return $this->getSelect($parameters).
 $this->getFrom($parameters).
 // $this->getIndices().
 $this->getWhere($parameters).
 $this->getGroupby($parameters).
 $this->getHaving($parameters).
 $this->getOrderby($parameters).
 $this->getLimit($parameters);
 }

 /**
 * Returns "LIMIT" clause which can be used in various queries
 *
 * @param array $parameters 
 * @return void
 */
 protected function getLimit(array &$parameters)
 {
 if (null === $this->limit)
 return "";

 return " LIMIT ".$this->limit[0].' OFFSET '.$this->limit[1];
 }

 /**
 * Returns number of columns, which will be returned by query
 *
 * @return integer
 */
 public function countSelects()
 {
 return count($this->selects);
 }

 private function getSelect(&$parameters)
 {
 $res = 'SELECT ';

 if (true === $this->distinct) {
 $res .= 'DISTINCT ';
 }

 $sqls = array();
 foreach ($this->selects as $s) {
 $sqls[] = $s->getSql($parameters, true);
 }

 return $res.implode(", ", $sqls);
 }

 protected function getFrom(array &$parameters)
 {
 $froms = array();
 for ($i = 0; $i < count($this->from); $i++) {
 $_str = $this->from[$i]->__toString().' AS `t'.$i.'`';
 if (0 == $i)
 $_str .= $this->getIndices();
 $froms[] = $_str;
 }

 $sql = ' FROM '.implode(", ", $froms);

 return $sql;
 }

 private function getGroupby(&$parameters)
 {
 if ($this->groupby === null)
 return "";

 foreach ($this->groupby as $groupby) {
 if (null !== $alias = $groupby->getAlias())
 $sqls[] = $alias;
 else
 $sqls[] = $groupby->getSql($parameters);
 }

 return " GROUP BY ".implode(", ", $sqls);
 }

 /**
 * Specifies "HAVING" clause of query
 *
 * @param MQB_Condition $conditions 
 * @return void
 * @author Jimi Dini
 */
 public function setHaving(MQB_Condition $conditions = null)
 {
 if (null === $conditions) {
 $this->havings = null;
 } elseif ($conditions instanceof MQB_Condition) {
 $this->havings = clone $conditions;
 }

 $this->reset();
 }

 protected function getHaving(&$parameters)
 {
 if (null == $this->havings)
 return "";

 return " HAVING ".$this->havings->getSql($parameters);
 }

 protected function getIndices()
 {
 if (null === $this->indices)
 return '';

 $res = ' USE INDEX (';
 $first = true;
 foreach ($this->indices as $idx) {
 if (true === $first)
 $first = false;
 else
 $res .= ', ';

 $res .= '`'.$idx.'`';
 }
 $res .= ')';

 return $res;
 }
}