Code Coverage
 
Classes and Traits
Functions and Methods
Lines
Total
0.00%
0 / 1
77.78%
42 / 54
 CRAP
90.12%
374 / 415
Select
0.00%
0 / 1
77.78%
42 / 54
 208.55
90.12%
374 / 415
 __construct(Adapter $adapter)
100.00%
1 / 1
 1
100.00%
3 / 3
 getBind()
100.00%
1 / 1
 1
100.00%
1 / 1
 bind($bind)
100.00%
1 / 1
 1
100.00%
2 / 2
 distinct($flag = true)
100.00%
1 / 1
 1
100.00%
2 / 2
 preColumnsOption($option)
100.00%
1 / 1
 1
100.00%
2 / 2
 from($name, $cols = '*', $schema = null)
100.00%
1 / 1
 1
100.00%
1 / 1
 columns($cols = '*', $correlationName = null)
100.00%
1 / 1
 4
100.00%
8 / 8
 union($select = array(), $type = self::SQL_UNION)
100.00%
1 / 1
 4
100.00%
9 / 9
 join($name, $cond, $cols = self::SQL_WILDCARD, $schema = null)
100.00%
1 / 1
 1
100.00%
1 / 1
 joinInner($name, $cond, $cols = self::SQL_WILDCARD, $schema = null)
100.00%
1 / 1
 1
100.00%
1 / 1
 joinLeft($name, $cond, $cols = self::SQL_WILDCARD, $schema = null)
100.00%
1 / 1
 1
100.00%
1 / 1
 joinRight($name, $cond, $cols = self::SQL_WILDCARD, $schema = null)
100.00%
1 / 1
 1
100.00%
1 / 1
 joinFull($name, $cond, $cols = self::SQL_WILDCARD, $schema = null)
100.00%
1 / 1
 1
100.00%
1 / 1
 joinCross($name, $cols = self::SQL_WILDCARD, $schema = null)
100.00%
1 / 1
 1
100.00%
1 / 1
 joinNatural($name, $cols = self::SQL_WILDCARD, $schema = null)
100.00%
1 / 1
 1
100.00%
1 / 1
 where($cond, $value = null, $type = null)
100.00%
1 / 1
 1
100.00%
2 / 2
 orWhere($cond, $value = null, $type = null)
100.00%
1 / 1
 1
100.00%
2 / 2
 registerConditionSet($name, $conjunction)
0.00%
0 / 1
 3.03
85.71%
6 / 7
 whereConditionSet($setName, $condition, $value = null, $type = null, $number = null)
0.00%
0 / 1
 12
0.00%
0 / 7
 group($spec)
100.00%
1 / 1
 4
100.00%
10 / 10
 having($cond, $value = null, $type = null)
100.00%
1 / 1
 3
100.00%
8 / 8
 orHaving($cond, $value = null, $type = null)
100.00%
1 / 1
 3
100.00%
8 / 8
 order($spec)
100.00%
1 / 1
 7
100.00%
20 / 20
 limit($count = null, $offset = null)
100.00%
1 / 1
 1
100.00%
3 / 3
 limitPage($page, $rowCount)
100.00%
1 / 1
 3
100.00%
5 / 5
 forUpdate($flag = true)
100.00%
1 / 1
 1
100.00%
2 / 2
 getPart($part)
100.00%
1 / 1
 2
100.00%
4 / 4
 quoteWithAlias($tableName, $columnName)
0.00%
0 / 1
 5.02
60.00%
3 / 5
 query($bind = array())
100.00%
1 / 1
 1
100.00%
1 / 1
 assemble()
100.00%
1 / 1
 3
100.00%
8 / 8
 reset($part = null)
100.00%
1 / 1
 3
100.00%
6 / 6
 getAdapter()
100.00%
1 / 1
 1
100.00%
1 / 1
 joinInternal($type, $name, $cond, $cols, $schema = null)
0.00%
0 / 1
 22.57
84.75%
50 / 59
 joinUsingInternal($type, $name, $cond, $cols = '*', $schema = null)
100.00%
1 / 1
 2
100.00%
8 / 8
 uniqueCorrelation($name)
0.00%
0 / 1
 5.40
55.56%
5 / 9
 tableCols($correlationName, $cols, $afterCorrelationName = null)
0.00%
0 / 1
 17.09
93.18%
41 / 44
 whereInternal($condition, $value = null, $type = null, $bool = true)
0.00%
0 / 1
 5.01
92.31%
12 / 13
 getDummyTable()
100.00%
1 / 1
 1
100.00%
1 / 1
 getQuotedSchema($schema = null)
100.00%
1 / 1
 2
100.00%
3 / 3
 getQuotedTable($tableName, $correlationName = null)
100.00%
1 / 1
 1
100.00%
1 / 1
 renderDistinct($sql)
100.00%
1 / 1
 2
100.00%
4 / 4
 renderPrecolumnsoptions($sql)
100.00%
1 / 1
 2
100.00%
4 / 4
 renderColumns($sql)
0.00%
0 / 1
 6.05
88.89%
16 / 18
 renderFrom($sql)
100.00%
1 / 1
 8
100.00%
21 / 21
 renderUnion($sql)
0.00%
0 / 1
 5.07
85.71%
12 / 14
 renderWhere($sql)
100.00%
1 / 1
 3
100.00%
4 / 4
 renderWheresets($sql)
0.00%
0 / 1
 8.83
57.14%
8 / 14
 renderGroup($sql)
100.00%
1 / 1
 4
100.00%
8 / 8
 renderHaving($sql)
100.00%
1 / 1
 3
100.00%
4 / 4
 renderOrder($sql)
0.00%
0 / 1
 8.12
87.50%
14 / 16
 renderLimitoffset($sql)
100.00%
1 / 1
 4
100.00%
13 / 13
 renderForupdate($sql)
100.00%
1 / 1
 2
100.00%
4 / 4
 __call($method, array $args)
100.00%
1 / 1
 5
100.00%
14 / 14
 __toString()
0.00%
0 / 1
 2.26
60.00%
3 / 5
<
<?php
/**
* Dewdrop
*
* @link https://github.com/DeltaSystems/dewdrop
* @copyright Delta Systems (http://deltasys.com)
* @license https://github.com/DeltaSystems/dewdrop/LICENSE
*/
namespace Dewdrop\Db;
use Dewdrop\Db\Select\SelectException;
/**
* \Dewdrop\Db\Select provides a simple programmatic API for assembilng
* SQL SELECT statements. It's essentially a direct port of
* Zend_Db_Select from Zend Framework 1.
*
* The Zend Framework test cases were used to ensure that \Dewdrop\Db\Select
* generates accurate and functional SQL statements.
*/
class Select
{
const DISTINCT = 'distinct';
const PRE_COLUMNS_OPTIONS = 'precolumnsoptions';
const COLUMNS = 'columns';
const FROM = 'from';
const UNION = 'union';
const WHERE = 'where';
const WHERE_CONDITION_SETS = 'wheresets';
const GROUP = 'group';
const HAVING = 'having';
const ORDER = 'order';
const LIMIT_COUNT = 'limitcount';
const LIMIT_OFFSET = 'limitoffset';
const FOR_UPDATE = 'forupdate';
const INNER_JOIN = 'inner join';
const LEFT_JOIN = 'left join';
const RIGHT_JOIN = 'right join';
const FULL_JOIN = 'full join';
const CROSS_JOIN = 'cross join';
const NATURAL_JOIN = 'natural join';
const SQL_WILDCARD = '*';
const SQL_SELECT = 'SELECT';
const SQL_UNION = 'UNION';
const SQL_UNION_ALL = 'UNION ALL';
const SQL_FROM = 'FROM';
const SQL_WHERE = 'WHERE';
const SQL_DISTINCT = 'DISTINCT';
const SQL_GROUP_BY = 'GROUP BY';
const SQL_ORDER_BY = 'ORDER BY';
const SQL_HAVING = 'HAVING';
const SQL_FOR_UPDATE = 'FOR UPDATE';
const SQL_AND = 'AND';
const SQL_AS = 'AS';
const SQL_OR = 'OR';
const SQL_ON = 'ON';
const SQL_ASC = 'ASC';
const SQL_DESC = 'DESC';
/**
* Bind variables for query
*
* @var array
*/
protected $bind = array();
/**
* The DB adapter that generated this object
*
* @var \Dewdrop\Db\Adapter
*/
protected $adapter;
/**
* The initial values for the $parts array.
* NOTE: It is important for the 'FOR_UPDATE' part to be last to ensure
* maximum compatibility with database adapters.
*
* @var array
*/
protected static $partsInit = [
self::DISTINCT => false,
self::PRE_COLUMNS_OPTIONS => [],
self::COLUMNS => [],
self::UNION => [],
self::FROM => [],
self::WHERE => [],
self::WHERE_CONDITION_SETS => [],
self::GROUP => [],
self::HAVING => [],
self::ORDER => [],
self::LIMIT_COUNT => null,
self::LIMIT_OFFSET => null,
self::FOR_UPDATE => false
];
/**
* Specify legal join types.
*
* @var array
*/
protected static $joinTypes = array(
self::INNER_JOIN,
self::LEFT_JOIN,
self::RIGHT_JOIN,
self::FULL_JOIN,
self::CROSS_JOIN,
self::NATURAL_JOIN,
);
/**
* Specify legal union types.
*
* @var array
*/
protected static $unionTypes = array(
self::SQL_UNION,
self::SQL_UNION_ALL
);
/**
* The component parts of a SELECT statement.
* Initialized to the $partsInit array in the constructor.
*
* @var array
*/
protected $parts = array();
/**
* Tracks which columns are being select from each table and join.
*
* @var array
*/
protected $tableCols = array();
/**
* Class constructor
*
* @param Adapter $adapter
*/
public function __construct(Adapter $adapter)
{
$this->adapter = $adapter;
$this->parts = self::$partsInit;
}
/**
* Get bind variables
*
* @return array
*/
public function getBind()
{
return $this->bind;
}
/**
* Set bind variables
*
* @param mixed $bind
* @return \Dewdrop\Db\Select
*/
public function bind($bind)
{
$this->bind = $bind;
return $this;
}
/**
* Makes the query SELECT DISTINCT.
*
* @param bool $flag Whether or not the SELECT is DISTINCT (default true).
* @return \Dewdrop\Db\Select
*/
public function distinct($flag = true)
{
$this->parts[self::DISTINCT] = (bool) $flag;
return $this;
}
/**
* Adds an option verbatim to the SQL before the selected columns
*
* @param string $option
* @return Select
*/
public function preColumnsOption($option)
{
$this->parts[static::PRE_COLUMNS_OPTIONS][] = $option;
return $this;
}
/**
* Adds a FROM table and optional columns to the query.
*
* The first parameter $name can be a simple string, in which case the
* correlation name is generated automatically. If you want to specify
* the correlation name, the first parameter must be an associative
* array in which the key is the physical table name, and the value is
* the correlation name. For example, array('table' => 'alias').
* The correlation name is prepended to all columns fetched for this
* table.
*
* The second parameter can be a single string or Expr object,
* or else an array of strings or Expr objects.
*
* The first parameter can be null or an empty string, in which case
* no correlation name is generated or prepended to the columns named
* in the second parameter.
*
* @param array|string|\Dewdrop\Db\Expr $name The table name or an associative array relating table name to
* correlation name.
* @param array|string|\Dewdrop\Db\Expr $cols The columns to select from this table.
* @param string $schema The schema name to specify, if any.
* @return \Dewdrop\Db\Select
*/
public function from($name, $cols = '*', $schema = null)
{
return $this->joinInternal(self::FROM, $name, null, $cols, $schema);
}
/**
* Specifies the columns used in the FROM clause.
*
* The parameter can be a single string or Expr object,
* or else an array of strings or Expr objects.
*
* @param array|string|Expr $cols The columns to select from this table.
* @param string $correlationName Correlation name of target table. OPTIONAL
* @return \Dewdrop\Db\Select This \Dewdrop\Db\Select object.
*/
public function columns($cols = '*', $correlationName = null)
{
if ($correlationName === null && count($this->parts[self::FROM])) {
$correlationNameKeys = array_keys($this->parts[self::FROM]);
$correlationName = current($correlationNameKeys);
}
if (!array_key_exists($correlationName, $this->parts[self::FROM])) {
throw new SelectException("No table has been specified for the FROM clause");
}
$this->tableCols($correlationName, $cols);
return $this;
}
/**
* Adds a UNION clause to the query.
*
* The first parameter has to be an array of \Dewdrop\Db\Select or
* sql query strings.
*
* <pre>
* $sql1 = $db->select();
* $sql2 = "SELECT ...";
* $select = $db->select()
* ->union(array($sql1, $sql2))
* ->order("id");
* </pre>
*
* @param array $select Array of select clauses for the union.
* @param string $type
* @return \Dewdrop\Db\Select This \Dewdrop\Db\Select object.
*/
public function union($select = array(), $type = self::SQL_UNION)
{
if (!is_array($select)) {
throw new SelectException(
"union() only accepts an array of \Dewdrop\Db\Select instances of sql query strings."
);
}
if (!in_array($type, self::$unionTypes)) {
throw new SelectException("Invalid union type '{$type}'");
}
foreach ($select as $target) {
$this->parts[self::UNION][] = array($target, $type);
}
return $this;
}
/**
* Adds a JOIN table and columns to the query.
*
* The $name and $cols parameters follow the same logic
* as described in the from() method.
*
* @param array|string|Expr $name The table name.
* @param string $cond Join on this condition.
* @param array|string $cols The columns to select from the joined table.
* @param string $schema The database name to specify, if any.
* @return \Dewdrop\Db\Select This \Dewdrop\Db\Select object.
*/
public function join($name, $cond, $cols = self::SQL_WILDCARD, $schema = null)
{
return $this->joinInner($name, $cond, $cols, $schema);
}
/**
* Add an INNER JOIN table and colums to the query
* Rows in both tables are matched according to the expression
* in the $cond argument. The result set is comprised
* of all cases where rows from the left table match
* rows from the right table.
*
* The $name and $cols parameters follow the same logic
* as described in the from() method.
*
* @param array|string|Expr $name The table name.
* @param string $cond Join on this condition.
* @param array|string $cols The columns to select from the joined table.
* @param string $schema The database name to specify, if any.
* @return \Dewdrop\Db\Select This \Dewdrop\Db\Select object.
*/
public function joinInner($name, $cond, $cols = self::SQL_WILDCARD, $schema = null)
{
return $this->joinInternal(self::INNER_JOIN, $name, $cond, $cols, $schema);
}
/**
* Add a LEFT OUTER JOIN table and colums to the query
* All rows from the left operand table are included,
* matching rows from the right operand table included,
* and the columns from the right operand table are filled
* with NULLs if no row exists matching the left table.
*
* The $name and $cols parameters follow the same logic
* as described in the from() method.
*
* @param array|string|Expr $name The table name.
* @param string $cond Join on this condition.
* @param array|string $cols The columns to select from the joined table.
* @param string $schema The database name to specify, if any.
* @return \Dewdrop\Db\Select This \Dewdrop\Db\Select object.
*/
public function joinLeft($name, $cond, $cols = self::SQL_WILDCARD, $schema = null)
{
return $this->joinInternal(self::LEFT_JOIN, $name, $cond, $cols, $schema);
}
/**
* Add a RIGHT OUTER JOIN table and colums to the query.
* Right outer join is the complement of left outer join.
* All rows from the right operand table are included,
* matching rows from the left operand table included,
* and the columns from the left operand table are filled
* with NULLs if no row exists matching the right table.
*
* The $name and $cols parameters follow the same logic
* as described in the from() method.
*
* @param array|string|Expr $name The table name.
* @param string $cond Join on this condition.
* @param array|string $cols The columns to select from the joined table.
* @param string $schema The database name to specify, if any.
* @return \Dewdrop\Db\Select This \Dewdrop\Db\Select object.
*/
public function joinRight($name, $cond, $cols = self::SQL_WILDCARD, $schema = null)
{
return $this->joinInternal(self::RIGHT_JOIN, $name, $cond, $cols, $schema);
}
/**
* Add a FULL OUTER JOIN table and colums to the query.
* A full outer join is like combining a left outer join
* and a right outer join. All rows from both tables are
* included, paired with each other on the same row of the
* result set if they satisfy the join condition, and otherwise
* paired with NULLs in place of columns from the other table.
*
* The $name and $cols parameters follow the same logic
* as described in the from() method.
*
* @param array|string|Expr $name The table name.
* @param string $cond Join on this condition.
* @param array|string $cols The columns to select from the joined table.
* @param string $schema The database name to specify, if any.
* @return \Dewdrop\Db\Select This \Dewdrop\Db\Select object.
*/
public function joinFull($name, $cond, $cols = self::SQL_WILDCARD, $schema = null)
{
return $this->joinInternal(self::FULL_JOIN, $name, $cond, $cols, $schema);
}
/**
* Add a CROSS JOIN table and colums to the query.
* A cross join is a cartesian product; there is no join condition.
*
* The $name and $cols parameters follow the same logic
* as described in the from() method.
*
* @param array|string|Expr $name The table name.
* @param array|string $cols The columns to select from the joined table.
* @param string $schema The database name to specify, if any.
* @return \Dewdrop\Db\Select This \Dewdrop\Db\Select object.
*/
public function joinCross($name, $cols = self::SQL_WILDCARD, $schema = null)
{
return $this->joinInternal(self::CROSS_JOIN, $name, null, $cols, $schema);
}
/**
* Add a NATURAL JOIN table and colums to the query.
* A natural join assumes an equi-join across any column(s)
* that appear with the same name in both tables.
* Only natural inner joins are supported by this API,
* even though SQL permits natural outer joins as well.
*
* The $name and $cols parameters follow the same logic
* as described in the from() method.
*
* @param array|string|Expr $name The table name.
* @param array|string $cols The columns to select from the joined table.
* @param string $schema The database name to specify, if any.
* @return \Dewdrop\Db\Select This \Dewdrop\Db\Select object.
*/
public function joinNatural($name, $cols = self::SQL_WILDCARD, $schema = null)
{
return $this->joinInternal(self::NATURAL_JOIN, $name, null, $cols, $schema);
}
/**
* Adds a WHERE condition to the query by AND.
*
* If a value is passed as the second param, it will be quoted
* and replaced into the condition wherever a question-mark
* appears. Array values are quoted and comma-separated.
*
* <pre>
* // simplest but non-secure
* $select->where("id = $id");
*
* // secure (ID is quoted but matched anyway)
* $select->where('id = ?', $id);
*
* // alternatively, with named binding
* $select->where('id = :id');
* </pre>
*
* Note that it is more correct to use named bindings in your
* queries for values other than strings. When you use named
* bindings, don't forget to pass the values when actually
* making a query:
*
* <pre>
* $db->fetchAll($select, array('id' => 5));
* </pre>
*
* @param string $cond The WHERE condition.
* @param mixed $value OPTIONAL The value to quote into the condition.
* @param int $type OPTIONAL The type of the given value
* @return \Dewdrop\Db\Select This \Dewdrop\Db\Select object.
*/
public function where($cond, $value = null, $type = null)
{
$this->parts[self::WHERE][] = $this->whereInternal($cond, $value, $type, true);
return $this;
}
/**
* Adds a WHERE condition to the query by OR.
*
* Otherwise identical to where().
*
* @param string $cond The WHERE condition.
* @param mixed $value OPTIONAL The value to quote into the condition.
* @param int $type OPTIONAL The type of the given value
* @return \Dewdrop\Db\Select This \Dewdrop\Db\Select object.
*
* @see where()
*/
public function orWhere($cond, $value = null, $type = null)
{
$this->parts[self::WHERE][] = $this->whereInternal($cond, $value, $type, false);
return $this;
}
/**
* Register a new condition set, useful when you want a block of conditions
* to be joined to the overall Select, grouped in their own parens. Useful
* to help users build complex boolean queries that interact predictably
* with WHERE clause additions you've made in code.
*
* @param string $name
* @param string $conjunction
* @return $this
* @throws SelectException
*/
public function registerConditionSet($name, $conjunction)
{
if (self::SQL_AND !== $conjunction && self::SQL_OR !== $conjunction) {
throw new SelectException('Condition sets must use AND or OR');
}
$this->parts[self::WHERE_CONDITION_SETS][$name] = array(
'conjunction' => $conjunction,
'conditions' => array()
);
return $this;
}
/**
* Add a new condition to a condition set. The set must be registered with
* registerConditionSet() prior to calling this method. Otherwise, it
* behaves just like where().
*
* @param string $setName
* @param string $condition
* @param mixed $value
* @param null|string $type
* @param null|integer $number
* @return $this
* @throws SelectException
*/
public function whereConditionSet($setName, $condition, $value = null, $type = null, $number = null)
{
if (!isset($this->parts[self::WHERE_CONDITION_SETS][$setName])) {
throw new SelectException("Adding condition to unregistered set: {$setName}");
}
if (null !== $value) {
$condition = $this->adapter->quoteInto($condition, $value, $type, $number);
}
$this->parts[self::WHERE_CONDITION_SETS][$setName]['conditions'][] = $condition;
return $this;
}
/**
* Adds grouping to the query.
*
* @param array|string $spec The column(s) to group by.
* @return \Dewdrop\Db\Select This \Dewdrop\Db\Select object.
*/
public function group($spec)
{
if (!is_array($spec)) {
$spec = array($spec);
}
foreach ($spec as $val) {
if (preg_match('/\(.*\)/', (string) $val)) {
$val = new Expr($val);
}
$this->parts[self::GROUP][] = $val;
}
return $this;
}
/**
* Adds a HAVING condition to the query by AND.
*
* If a value is passed as the second param, it will be quoted
* and replaced into the condition wherever a question-mark
* appears. See {@link where()} for an example
*
* @param string $cond The HAVING condition.
* @param mixed $value OPTIONAL The value to quote into the condition.
* @param int $type OPTIONAL The type of the given value
* @return \Dewdrop\Db\Select This \Dewdrop\Db\Select object.
*/
public function having($cond, $value = null, $type = null)
{
if ($value !== null) {
$cond = $this->adapter->quoteInto($cond, $value, $type);
}
if ($this->parts[self::HAVING]) {
$this->parts[self::HAVING][] = self::SQL_AND . " ($cond)";
} else {
$this->parts[self::HAVING][] = "($cond)";
}
return $this;
}
/**
* Adds a HAVING condition to the query by OR.
*
* Otherwise identical to orHaving().
*
* @param string $cond The HAVING condition.
* @param mixed $value OPTIONAL The value to quote into the condition.
* @param int $type OPTIONAL The type of the given value
* @return \Dewdrop\Db\Select This \Dewdrop\Db\Select object.
*
* @see having()
*/
public function orHaving($cond, $value = null, $type = null)
{
if ($value !== null) {
$cond = $this->adapter->quoteInto($cond, $value, $type);
}
if ($this->parts[self::HAVING]) {
$this->parts[self::HAVING][] = self::SQL_OR . " ($cond)";
} else {
$this->parts[self::HAVING][] = "($cond)";
}
return $this;
}
/**
* Adds a row order to the query.
*
* @param mixed $spec The column(s) and direction to order by.
* @return \Dewdrop\Db\Select This \Dewdrop\Db\Select object.
*/
public function order($spec)
{
if (!is_array($spec)) {
$spec = array($spec);
}
// force 'ASC' or 'DESC' on each order spec, default is ASC.
foreach ($spec as $val) {
if ($val instanceof Expr) {
$expr = $val->__toString();
if (empty($expr)) {
continue;
}
$this->parts[self::ORDER][] = $val;
} else {
if (empty($val)) {
continue;
}
$direction = self::SQL_ASC;
if (preg_match('/(.*\W)(' . self::SQL_ASC . '|' . self::SQL_DESC . ')\b/si', $val, $matches)) {
$val = trim($matches[1]);
$direction = $matches[2];
}
$this->parts[self::ORDER][] = array($val, $direction);
}
}
return $this;
}
/**
* Sets a limit count and offset to the query.
*
* @param int $count OPTIONAL The number of rows to return.
* @param int $offset OPTIONAL Start returning after this many rows.
* @return \Dewdrop\Db\Select This \Dewdrop\Db\Select object.
*/
public function limit($count = null, $offset = null)
{
$this->parts[self::LIMIT_COUNT] = (int) $count;
$this->parts[self::LIMIT_OFFSET] = (int) $offset;
return $this;
}
/**
* Sets the limit and count by page number.
*
* @param int $page Limit results to this page number.
* @param int $rowCount Use this many rows per page.
* @return \Dewdrop\Db\Select This \Dewdrop\Db\Select object.
*/
public function limitPage($page, $rowCount)
{
$page = ($page > 0) ? $page : 1;
$rowCount = ($rowCount > 0) ? $rowCount : 1;
$this->parts[self::LIMIT_COUNT] = (int) $rowCount;
$this->parts[self::LIMIT_OFFSET] = (int) $rowCount * ($page - 1);
return $this;
}
/**
* Makes the query SELECT FOR UPDATE.
*
* @param bool $flag Whether or not the SELECT is FOR UPDATE (default true).
* @return \Dewdrop\Db\Select This \Dewdrop\Db\Select object.
*/
public function forUpdate($flag = true)
{
$this->parts[self::FOR_UPDATE] = (bool) $flag;
return $this;
}
/**
* Get part of the structured information for the currect query.
*
* @param string $part
* @return mixed
* @throws SelectException
*/
public function getPart($part)
{
$part = strtolower($part);
if (!array_key_exists($part, $this->parts)) {
throw new SelectException("Invalid Select part '$part'");
}
return $this->parts[$part];
}
/**
* This method will quote the supplied table and column name pair, using
* whatever alias has been defined for the table on this Select object.
* This can be useful when you're modifying a Select and you are not sure
* what aliases have been added for the table you want to reference.
*
* @throws SelectException
* @param string $tableName
* @param string $columnName
* @return string
*/
public function quoteWithAlias($tableName, $columnName)
{
foreach ($this->getPart(self::FROM) as $queryName => $info) {
if ($tableName === $info['tableName'] || $queryName === $tableName) {
return $this->getAdapter()->quoteIdentifier("{$queryName}.{$columnName}");
}
}
throw new SelectException("Table {$tableName} could not be found in the query.");
}
/**
* Executes the current select object and returns the result
*
* @param mixed $bind An array of data to bind to the placeholders.
* @return mixed
*/
public function query($bind = array())
{
return $this->adapter->query($this, $bind);
}
/**
* Converts this object to an SQL SELECT string.
*
* @return string|null This object as a SELECT string. (or null if a string cannot be produced.)
*/
public function assemble()
{
$sql = self::SQL_SELECT;
foreach (array_keys(self::$partsInit) as $part) {
$method = 'render' . ucfirst($part);
if (method_exists($this, $method)) {
$sql = $this->$method($sql);
}
}
return $sql;
}
/**
* Clear parts of the Select object, or an individual part.
*
* @param string $part OPTIONAL
* @return \Dewdrop\Db\Select
*/
public function reset($part = null)
{
if ($part == null) {
$this->parts = self::$partsInit;
} elseif (array_key_exists($part, self::$partsInit)) {
$this->parts[$part] = self::$partsInit[$part];
}
return $this;
}
/**
* Gets the \Dewdrop\Db\Adapter for this
* particular \Dewdrop\Db\Select object.
*
* @return \Dewdrop\Db\Adapter
*/
public function getAdapter()
{
return $this->adapter;
}
/**
* Populate the {@link $parts} 'join' key
*
* Does the dirty work of populating the join key.
*
* The $name and $cols parameters follow the same logic
* as described in the from() method.
*
* @param null|string $type Type of join; inner, left, and null are currently supported
* @param array|string|Expr $name Table name
* @param string $cond Join on this condition
* @param array|string $cols The columns to select from the joined table
* @param string $schema The database name to specify, if any.
* @return \Dewdrop\Db\Select This \Dewdrop\Db\Select object
* @throws \Dewdrop\Db\Select_Exception
*/
protected function joinInternal($type, $name, $cond, $cols, $schema = null)
{
if (!in_array($type, self::$joinTypes) && $type != self::FROM) {
throw new SelectException("Invalid join type '$type'");
}
if (count($this->parts[self::UNION])) {
throw new SelectException("Invalid use of table with " . self::SQL_UNION);
}
if (empty($name)) {
$correlationName = $tableName = '';
} elseif (is_array($name)) {
// Must be array($correlationName => $tableName) or array($ident, ...)
foreach ($name as $_correlationName => $_tableName) {
if (is_string($_correlationName)) {
// We assume the key is the correlation name and value is the table name
$tableName = $_tableName;
$correlationName = $_correlationName;
} else {
// We assume just an array of identifiers, with no correlation name
$tableName = $_tableName;
$correlationName = $this->uniqueCorrelation($tableName);
}
break;
}
} elseif ($name instanceof Expr || $name instanceof Select) {
$tableName = $name;
$correlationName = $this->uniqueCorrelation('t');
} elseif (preg_match('/^(.+)\s+AS\s+(.+)$/i', $name, $m)) {
$tableName = $m[1];
$correlationName = $m[2];
} else {
$tableName = $name;
$correlationName = $this->uniqueCorrelation($tableName);
}
// Schema from table name overrides schema argument
if (!is_object($tableName) && false !== strpos($tableName, '.')) {
list($schema, $tableName) = explode('.', $tableName);
}
$lastFromCorrelationName = null;
if (!empty($correlationName)) {
if (array_key_exists($correlationName, $this->parts[self::FROM])) {
throw new SelectException("You cannot define a correlation name '$correlationName' more than once");
}
if ($type == self::FROM) {
// append this from after the last from joinType
$tmpFromParts = $this->parts[self::FROM];
$this->parts[self::FROM] = array();
// move all the froms onto the stack
while ($tmpFromParts) {
$currentCorrelationName = key($tmpFromParts);
if ($tmpFromParts[$currentCorrelationName]['joinType'] != self::FROM) {
break;
}
$lastFromCorrelationName = $currentCorrelationName;
$this->parts[self::FROM][$currentCorrelationName] = array_shift($tmpFromParts);
}
} else {
$tmpFromParts = array();
}
$this->parts[self::FROM][$correlationName] = array(
'joinType' => $type,
'schema' => $schema,
'tableName' => $tableName,
'joinCondition' => $cond
);
while ($tmpFromParts) {
$currentCorrelationName = key($tmpFromParts);
$this->parts[self::FROM][$currentCorrelationName] = array_shift($tmpFromParts);
}
}
// add to the columns from this joined table
if ($type == self::FROM && $lastFromCorrelationName == null) {
$lastFromCorrelationName = true;
}
$this->tableCols($correlationName, $cols, $lastFromCorrelationName);
return $this;
}
/**
* Handle JOIN... USING... syntax
*
* This is functionality identical to the existing JOIN methods, however
* the join condition can be passed as a single column name. This method
* then completes the ON condition by using the same field for the FROM
* table and the JOIN table.
*
* <pre>
* $select = $db->select()->from('table1')
* ->joinUsing('table2', 'column1');
*
* // SELECT * FROM table1 JOIN table2 ON table1.column1 = table2.column2
* </pre>
*
* These joins are called by the developer simply by adding 'Using' to the
* method name. E.g.
* * joinUsing
* * joinInnerUsing
* * joinFullUsing
* * joinRightUsing
* * joinLeftUsing
*
* @param string $type
* @param string $name
* @param string $cond
* @param string|array $cols
* @param string $schema
* @return \Dewdrop\Db\Select This \Dewdrop\Db\Select object.
*/
public function joinUsingInternal($type, $name, $cond, $cols = '*', $schema = null)
{
if (empty($this->parts[self::FROM])) {
throw new SelectException("You can only perform a joinUsing after specifying a FROM table");
}
$join = $this->adapter->quoteIdentifier(key($this->parts[self::FROM]), true);
$from = $this->adapter->quoteIdentifier($this->uniqueCorrelation($name), true);
$cond1 = $from . '.' . $cond;
$cond2 = $join . '.' . $cond;
$cond = $cond1 . ' = ' . $cond2;
return $this->join($type, $name, $cond, $cols, $schema);
}
/**
* Generate a unique correlation name
*
* @param string|array $name A qualified identifier.
* @return string A unique correlation name.
*/
private function uniqueCorrelation($name)
{
if (is_array($name)) {
$c = end($name);
} else {
// Extract just the last name of a qualified table name
$dot = strrpos($name, '.');
$c = ($dot === false) ? $name : substr($name, $dot+1);
}
for ($i = 2; array_key_exists($c, $this->parts[self::FROM]); ++$i) {
$c = $name . '_' . (string) $i;
}
return $c;
}
/**
* Adds to the internal table-to-column mapping array.
*
* @param string $correlationName The table/join the columns come from.
* @param array|string $cols The list of columns; preferably as
* an array, but possibly as a string containing one column.
* @param bool|string $afterCorrelationName True if it should be prepended,
* a correlation name if it should be inserted
* @return void
*/
protected function tableCols($correlationName, $cols, $afterCorrelationName = null)
{
if (!is_array($cols)) {
$cols = array($cols);
}
if ($correlationName == null) {
$correlationName = '';
}
$columnValues = array();
foreach (array_filter($cols) as $alias => $col) {
$currentCorrelationName = $correlationName;
if (is_string($col)) {
// Check for a column matching "<column> AS <alias>" and extract the alias name
if (preg_match('/^(.+)\s+' . self::SQL_AS . '\s+(.+)$/i', $col, $m)) {
$col = $m[1];
$alias = $m[2];
}
// Check for columns that look like functions and convert to Expr
if (preg_match('/\(.*\)/', $col)) {
$col = new Expr($col);
} elseif (preg_match('/(.+)\.(.+)/', $col, $m)) {
$currentCorrelationName = $m[1];
$col = $m[2];
}
}
$columnValues[] = array($currentCorrelationName, $col, is_string($alias) ? $alias : null);
}
if ($columnValues) {
// should we attempt to prepend or insert these values?
if ($afterCorrelationName === true || is_string($afterCorrelationName)) {
$tmpColumns = $this->parts[self::COLUMNS];
$this->parts[self::COLUMNS] = array();
} else {
$tmpColumns = array();
}
// find the correlation name to insert after
if (is_string($afterCorrelationName)) {
while ($tmpColumns) {
$this->parts[self::COLUMNS][] = $currentColumn = array_shift($tmpColumns);
if ($currentColumn[0] == $afterCorrelationName) {
break;
}
}