3 * Zend Framework (http://framework.zend.com/)
5 * @link http://github.com/zendframework/zf2 for the canonical source repository
6 * @copyright Copyright (c) 2005-2015 Zend Technologies USA Inc. (http://www.zend.com)
7 * @license http://framework.zend.com/license/new-bsd New BSD License
10 namespace Zend\Db\Sql
;
12 use Zend\Db\Adapter\Driver\DriverInterface
;
13 use Zend\Db\Adapter\ParameterContainer
;
14 use Zend\Db\Adapter\Platform\PlatformInterface
;
18 * @property Where $where
19 * @property Having $having
21 class Select
extends AbstractPreparableSql
27 const SELECT
= 'select';
28 const QUANTIFIER
= 'quantifier';
29 const COLUMNS
= 'columns';
30 const TABLE
= 'table';
31 const JOINS
= 'joins';
32 const WHERE
= 'where';
33 const GROUP
= 'group';
34 const HAVING
= 'having';
35 const ORDER
= 'order';
36 const LIMIT
= 'limit';
37 const OFFSET
= 'offset';
38 const QUANTIFIER_DISTINCT
= 'DISTINCT';
39 const QUANTIFIER_ALL
= 'ALL';
40 const JOIN_INNER
= 'inner';
41 const JOIN_OUTER
= 'outer';
42 const JOIN_LEFT
= 'left';
43 const JOIN_RIGHT
= 'right';
44 const JOIN_OUTER_RIGHT
= 'outer right';
45 const JOIN_OUTER_LEFT
= 'outer left';
47 const ORDER_ASCENDING
= 'ASC';
48 const ORDER_DESCENDING
= 'DESC';
49 const COMBINE
= 'combine';
50 const COMBINE_UNION
= 'union';
51 const COMBINE_EXCEPT
= 'except';
52 const COMBINE_INTERSECT
= 'intersect';
56 * @var array Specifications
58 protected $specifications = array(
59 'statementStart' => '%1$s',
60 self
::SELECT
=> array(
61 'SELECT %1$s FROM %2$s' => array(
62 array(1 => '%1$s', 2 => '%1$s AS %2$s', 'combinedby' => ', '),
65 'SELECT %1$s %2$s FROM %3$s' => array(
67 array(1 => '%1$s', 2 => '%1$s AS %2$s', 'combinedby' => ', '),
70 'SELECT %1$s' => array(
71 array(1 => '%1$s', 2 => '%1$s AS %2$s', 'combinedby' => ', '),
76 array(3 => '%1$s JOIN %2$s ON %3$s', 'combinedby' => ' ')
79 self
::WHERE
=> 'WHERE %1$s',
81 'GROUP BY %1$s' => array(
82 array(1 => '%1$s', 'combinedby' => ', ')
85 self
::HAVING
=> 'HAVING %1$s',
87 'ORDER BY %1$s' => array(
88 array(1 => '%1$s', 2 => '%1$s %2$s', 'combinedby' => ', ')
91 self
::LIMIT
=> 'LIMIT %1$s',
92 self
::OFFSET
=> 'OFFSET %1$s',
93 'statementEnd' => '%1$s',
94 self
::COMBINE
=> '%1$s ( %2$s )',
100 protected $tableReadOnly = false;
105 protected $prefixColumnsWithTable = true;
108 * @var string|array|TableIdentifier
110 protected $table = null;
113 * @var null|string|Expression
115 protected $quantifier = null;
120 protected $columns = array(self
::SQL_STAR
);
125 protected $joins = array();
130 protected $where = null;
135 protected $order = array();
140 protected $group = null;
143 * @var null|string|array
145 protected $having = null;
150 protected $limit = null;
155 protected $offset = null;
160 protected $combine = array();
165 * @param null|string|array|TableIdentifier $table
167 public function __construct($table = null)
171 $this->tableReadOnly
= true;
174 $this->where
= new Where
;
175 $this->having
= new Having
;
181 * @param string|array|TableIdentifier $table
182 * @throws Exception\InvalidArgumentException
185 public function from($table)
187 if ($this->tableReadOnly
) {
188 throw new Exception\
InvalidArgumentException('Since this object was created with a table and/or schema in the constructor, it is read only.');
191 if (!is_string($table) && !is_array($table) && !$table instanceof TableIdentifier
) {
192 throw new Exception\
InvalidArgumentException('$table must be a string, array, or an instance of TableIdentifier');
195 if (is_array($table) && (!is_string(key($table)) ||
count($table) !== 1)) {
196 throw new Exception\
InvalidArgumentException('from() expects $table as an array is a single element associative array');
199 $this->table
= $table;
204 * @param string|Expression $quantifier DISTINCT|ALL
207 public function quantifier($quantifier)
209 if (!is_string($quantifier) && !$quantifier instanceof ExpressionInterface
) {
210 throw new Exception\
InvalidArgumentException(
211 'Quantifier must be one of DISTINCT, ALL, or some platform specific object implementing ExpressionInterface'
214 $this->quantifier
= $quantifier;
219 * Specify columns from which to select
221 * Possible valid states:
226 * value can be strings or Expression objects
228 * array(string => value, ...)
229 * key string will be use as alias,
230 * value can be string or Expression objects
232 * @param array $columns
233 * @param bool $prefixColumnsWithTable
236 public function columns(array $columns, $prefixColumnsWithTable = true)
238 $this->columns
= $columns;
239 $this->prefixColumnsWithTable
= (bool) $prefixColumnsWithTable;
246 * @param string|array $name
248 * @param string|array $columns
249 * @param string $type one of the JOIN_* constants
250 * @throws Exception\InvalidArgumentException
253 public function join($name, $on, $columns = self
::SQL_STAR
, $type = self
::JOIN_INNER
)
255 if (is_array($name) && (!is_string(key($name)) ||
count($name) !== 1)) {
256 throw new Exception\
InvalidArgumentException(
257 sprintf("join() expects '%s' as an array is a single element associative array", array_shift($name))
260 if (!is_array($columns)) {
261 $columns = array($columns);
263 $this->joins
[] = array(
266 'columns' => $columns,
273 * Create where clause
275 * @param Where|\Closure|string|array|Predicate\PredicateInterface $predicate
276 * @param string $combination One of the OP_* constants from Predicate\PredicateSet
277 * @throws Exception\InvalidArgumentException
280 public function where($predicate, $combination = Predicate\PredicateSet
::OP_AND
)
282 if ($predicate instanceof Where
) {
283 $this->where
= $predicate;
285 $this->where
->addPredicates($predicate, $combination);
290 public function group($group)
292 if (is_array($group)) {
293 foreach ($group as $o) {
297 $this->group
[] = $group;
303 * Create where clause
305 * @param Where|\Closure|string|array $predicate
306 * @param string $combination One of the OP_* constants from Predicate\PredicateSet
309 public function having($predicate, $combination = Predicate\PredicateSet
::OP_AND
)
311 if ($predicate instanceof Having
) {
312 $this->having
= $predicate;
314 $this->having
->addPredicates($predicate, $combination);
320 * @param string|array $order
323 public function order($order)
325 if (is_string($order)) {
326 if (strpos($order, ',') !== false) {
327 $order = preg_split('#,\s+#', $order);
329 $order = (array) $order;
331 } elseif (!is_array($order)) {
332 $order = array($order);
334 foreach ($order as $k => $v) {
336 $this->order
[$k] = $v;
348 public function limit($limit)
350 if (!is_numeric($limit)) {
351 throw new Exception\
InvalidArgumentException(sprintf(
352 '%s expects parameter to be numeric, "%s" given',
354 (is_object($limit) ?
get_class($limit) : gettype($limit))
358 $this->limit
= $limit;
366 public function offset($offset)
368 if (!is_numeric($offset)) {
369 throw new Exception\
InvalidArgumentException(sprintf(
370 '%s expects parameter to be numeric, "%s" given',
372 (is_object($offset) ?
get_class($offset) : gettype($offset))
376 $this->offset
= $offset;
381 * @param Select $select
382 * @param string $type
383 * @param string $modifier
385 * @throws Exception\InvalidArgumentException
387 public function combine(Select
$select, $type = self
::COMBINE_UNION
, $modifier = '')
389 if ($this->combine
!== array()) {
390 throw new Exception\
InvalidArgumentException('This Select object is already combined and cannot be combined with multiple Selects objects');
392 $this->combine
= array(
395 'modifier' => $modifier
401 * @param string $part
403 * @throws Exception\InvalidArgumentException
405 public function reset($part)
409 if ($this->tableReadOnly
) {
410 throw new Exception\
InvalidArgumentException(
411 'Since this object was created with a table and/or schema in the constructor, it is read only.'
416 case self
::QUANTIFIER
:
417 $this->quantifier
= null;
420 $this->columns
= array();
423 $this->joins
= array();
426 $this->where
= new Where
;
432 $this->having
= new Having
;
438 $this->offset
= null;
441 $this->order
= array();
444 $this->combine
= array();
450 public function setSpecification($index, $specification)
452 if (!method_exists($this, 'process' . $index)) {
453 throw new Exception\
InvalidArgumentException('Not a valid specification name.');
455 $this->specifications
[$index] = $specification;
459 public function getRawState($key = null)
462 self
::TABLE
=> $this->table
,
463 self
::QUANTIFIER
=> $this->quantifier
,
464 self
::COLUMNS
=> $this->columns
,
465 self
::JOINS
=> $this->joins
,
466 self
::WHERE
=> $this->where
,
467 self
::ORDER
=> $this->order
,
468 self
::GROUP
=> $this->group
,
469 self
::HAVING
=> $this->having
,
470 self
::LIMIT
=> $this->limit
,
471 self
::OFFSET
=> $this->offset
,
472 self
::COMBINE
=> $this->combine
474 return (isset($key) && array_key_exists($key, $rawState)) ?
$rawState[$key] : $rawState;
478 * Returns whether the table is read only or not.
482 public function isTableReadOnly()
484 return $this->tableReadOnly
;
488 * Render table with alias in from/join parts
490 * @todo move TableIdentifier concatination here
491 * @param string $table
492 * @param string $alias
495 protected function renderTable($table, $alias = null)
497 return $table . ($alias ?
' AS ' . $alias : '');
500 protected function processStatementStart(PlatformInterface
$platform, DriverInterface
$driver = null, ParameterContainer
$parameterContainer = null)
502 if ($this->combine
!== array()) {
507 protected function processStatementEnd(PlatformInterface
$platform, DriverInterface
$driver = null, ParameterContainer
$parameterContainer = null)
509 if ($this->combine
!== array()) {
515 * Process the select part
517 * @param PlatformInterface $platform
518 * @param DriverInterface $driver
519 * @param ParameterContainer $parameterContainer
522 protected function processSelect(PlatformInterface
$platform, DriverInterface
$driver = null, ParameterContainer
$parameterContainer = null)
526 list($table, $fromTable) = $this->resolveTable($this->table
, $platform, $driver, $parameterContainer);
527 // process table columns
529 foreach ($this->columns
as $columnIndexOrAs => $column) {
530 if ($column === self
::SQL_STAR
) {
531 $columns[] = array($fromTable . self
::SQL_STAR
);
535 $columnName = $this->resolveColumnValue(
538 'fromTable' => $fromTable,
539 'isIdentifier' => true,
544 (is_string($columnIndexOrAs) ?
$columnIndexOrAs : 'column')
546 // process As portion
547 if (is_string($columnIndexOrAs)) {
548 $columnAs = $platform->quoteIdentifier($columnIndexOrAs);
549 } elseif (stripos($columnName, ' as ') === false) {
550 $columnAs = (is_string($column)) ?
$platform->quoteIdentifier($column) : 'Expression' . $expr++
;
552 $columns[] = (isset($columnAs)) ?
array($columnName, $columnAs) : array($columnName);
555 // process join columns
556 foreach ($this->joins
as $join) {
557 $joinName = (is_array($join['name'])) ?
key($join['name']) : $join['name'];
558 $joinName = parent
::resolveTable($joinName, $platform, $driver, $parameterContainer);
560 foreach ($join['columns'] as $jKey => $jColumn) {
562 $jFromTable = is_scalar($jColumn)
563 ?
$joinName . $platform->getIdentifierSeparator()
565 $jColumns[] = $this->resolveColumnValue(
567 'column' => $jColumn,
568 'fromTable' => $jFromTable,
569 'isIdentifier' => true,
574 (is_string($jKey) ?
$jKey : 'column')
576 if (is_string($jKey)) {
577 $jColumns[] = $platform->quoteIdentifier($jKey);
578 } elseif ($jColumn !== self
::SQL_STAR
) {
579 $jColumns[] = $platform->quoteIdentifier($jColumn);
581 $columns[] = $jColumns;
585 if ($this->quantifier
) {
586 $quantifier = ($this->quantifier
instanceof ExpressionInterface
)
587 ?
$this->processExpression($this->quantifier
, $platform, $driver, $parameterContainer, 'quantifier')
591 if (!isset($table)) {
592 return array($columns);
593 } elseif (isset($quantifier)) {
594 return array($quantifier, $columns, $table);
596 return array($columns, $table);
600 protected function processJoins(PlatformInterface
$platform, DriverInterface
$driver = null, ParameterContainer
$parameterContainer = null)
607 $joinSpecArgArray = array();
608 foreach ($this->joins
as $j => $join) {
613 if (is_array($join['name'])) {
614 $joinName = current($join['name']);
615 $joinAs = $platform->quoteIdentifier(key($join['name']));
617 $joinName = $join['name'];
620 if ($joinName instanceof Expression
) {
621 $joinName = $joinName->getExpression();
622 } elseif ($joinName instanceof TableIdentifier
) {
623 $joinName = $joinName->getTableAndSchema();
624 $joinName = ($joinName[1] ?
$platform->quoteIdentifier($joinName[1]) . $platform->getIdentifierSeparator() : '') . $platform->quoteIdentifier($joinName[0]);
625 } elseif ($joinName instanceof Select
) {
626 $joinName = '(' . $this->processSubSelect($joinName, $platform, $driver, $parameterContainer) . ')';
627 } elseif (is_string($joinName) ||
(is_object($joinName) && is_callable(array($joinName, '__toString')))) {
628 $joinName = $platform->quoteIdentifier($joinName);
630 throw new Exception\
InvalidArgumentException(sprintf('Join name expected to be Expression|TableIdentifier|Select|string, "%s" given', gettype($joinName)));
633 $joinSpecArgArray[$j] = array(
634 strtoupper($join['type']),
635 $this->renderTable($joinName, $joinAs),
639 // note: for Expression objects, pass them to processExpression with a prefix specific to each join (used for named parameters)
640 $joinSpecArgArray[$j][] = ($join['on'] instanceof ExpressionInterface
)
641 ?
$this->processExpression($join['on'], $platform, $driver, $parameterContainer, 'join' . ($j+
1) . 'part')
642 : $platform->quoteIdentifierInFragment($join['on'], array('=', 'AND', 'OR', '(', ')', 'BETWEEN', '<', '>')); // on
645 return array($joinSpecArgArray);
648 protected function processWhere(PlatformInterface
$platform, DriverInterface
$driver = null, ParameterContainer
$parameterContainer = null)
650 if ($this->where
->count() == 0) {
654 $this->processExpression($this->where
, $platform, $driver, $parameterContainer, 'where')
658 protected function processGroup(PlatformInterface
$platform, DriverInterface
$driver = null, ParameterContainer
$parameterContainer = null)
660 if ($this->group
=== null) {
663 // process table columns
665 foreach ($this->group
as $column) {
666 $groups[] = $this->resolveColumnValue(
669 'isIdentifier' => true,
677 return array($groups);
680 protected function processHaving(PlatformInterface
$platform, DriverInterface
$driver = null, ParameterContainer
$parameterContainer = null)
682 if ($this->having
->count() == 0) {
686 $this->processExpression($this->having
, $platform, $driver, $parameterContainer, 'having')
690 protected function processOrder(PlatformInterface
$platform, DriverInterface
$driver = null, ParameterContainer
$parameterContainer = null)
692 if (empty($this->order
)) {
696 foreach ($this->order
as $k => $v) {
697 if ($v instanceof ExpressionInterface
) {
699 $this->processExpression($v, $platform, $driver, $parameterContainer)
704 if (strpos($v, ' ') !== false) {
705 list($k, $v) = preg_split('# #', $v, 2);
708 $v = self
::ORDER_ASCENDING
;
711 if (strtoupper($v) == self
::ORDER_DESCENDING
) {
712 $orders[] = array($platform->quoteIdentifierInFragment($k), self
::ORDER_DESCENDING
);
714 $orders[] = array($platform->quoteIdentifierInFragment($k), self
::ORDER_ASCENDING
);
717 return array($orders);
720 protected function processLimit(PlatformInterface
$platform, DriverInterface
$driver = null, ParameterContainer
$parameterContainer = null)
722 if ($this->limit
=== null) {
725 if ($parameterContainer) {
726 $parameterContainer->offsetSet('limit', $this->limit
, ParameterContainer
::TYPE_INTEGER
);
727 return array($driver->formatParameterName('limit'));
729 return array($platform->quoteValue($this->limit
));
732 protected function processOffset(PlatformInterface
$platform, DriverInterface
$driver = null, ParameterContainer
$parameterContainer = null)
734 if ($this->offset
=== null) {
737 if ($parameterContainer) {
738 $parameterContainer->offsetSet('offset', $this->offset
, ParameterContainer
::TYPE_INTEGER
);
739 return array($driver->formatParameterName('offset'));
742 return array($platform->quoteValue($this->offset
));
745 protected function processCombine(PlatformInterface
$platform, DriverInterface
$driver = null, ParameterContainer
$parameterContainer = null)
747 if ($this->combine
== array()) {
751 $type = $this->combine
['type'];
752 if ($this->combine
['modifier']) {
753 $type .= ' ' . $this->combine
['modifier'];
758 $this->processSubSelect($this->combine
['select'], $platform, $driver, $parameterContainer),
763 * Variable overloading
765 * @param string $name
766 * @throws Exception\InvalidArgumentException
769 public function __get($name)
771 switch (strtolower($name)) {
775 return $this->having
;
777 throw new Exception\
InvalidArgumentException('Not a valid magic property for this object');
784 * Resets the where object each time the Select is cloned.
788 public function __clone()
790 $this->where
= clone $this->where
;
791 $this->having
= clone $this->having
;
795 * @param string|TableIdentifier|Select $table
796 * @param PlatformInterface $platform
797 * @param DriverInterface $driver
798 * @param ParameterContainer $parameterContainer
801 protected function resolveTable($table, PlatformInterface
$platform, DriverInterface
$driver = null, ParameterContainer
$parameterContainer = null)
805 if (is_array($table)) {
806 $alias = key($table);
807 $table = current($table);
810 $table = parent
::resolveTable($table, $platform, $driver, $parameterContainer);
813 $fromTable = $platform->quoteIdentifier($alias);
814 $table = $this->renderTable($table, $fromTable);
819 if ($this->prefixColumnsWithTable
&& $fromTable) {
820 $fromTable .= $platform->getIdentifierSeparator();