Select::removeCol() - Code Metrics - Inspection of "Merge pull request #120 from pavarnos/limitoffsett..." - auraphp/Aura.SqlQuery - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — 3.x ( 9be43c...53c3d5 )

by Paul

created 2017-02-19 15:34 UTC

Select::removeCol() A

↳ Parent: Select

Complexity

Conditions	3
Paths	3

Size

Total Lines	16
Code Lines	9

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	9
CRAP Score	3

Importance

Changes

Metric	Value
dl	0
loc	16
ccs	9
cts	9
cp	1
rs	9.4285
c	0
b	0
f	0
cc	3
eloc	9
nc	3
nop	1
crap	3

<?php
/**
 *
 * This file is part of Aura for PHP.
 *
 * @license http://opensource.org/licenses/bsd-license.php BSD
 *
 */
namespace Aura\SqlQuery\Common;

use Aura\SqlQuery\AbstractQuery;
use Aura\SqlQuery\Exception;

/**
 *
 * An object for SELECT queries.
 *
 * @package Aura.SqlQuery
 *
 */
class Select extends AbstractQuery implements SelectInterface, SubselectInterface
{
    use WhereTrait;
    use LimitOffsetTrait { limit as setLimit; offset as setOffset; }

    /**
     *
     * An array of union SELECT statements.
     *
     * @var array
     *
     */
    protected $union = array();

    /**
     *
     * Is this a SELECT FOR UPDATE?
     *
     * @var
     *
     */
    protected $for_update = false;

    /**
     *
     * The columns to be selected.
     *
     * @var array
     *
     */
    protected $cols = array();

    /**
     *
     * Select from these tables; includes JOIN clauses.
     *
     * @var array
     *
     */
    protected $from = array();

    /**
     *
     * The current key in the `$from` array.
     *
     * @var int
     *
     */
    protected $from_key = -1;

    /**
     *
     * Tracks which JOIN clauses are attached to which FROM tables.
     *
     * @var array
     *
     */
    protected $join = array();

    /**
     *
     * GROUP BY these columns.
     *
     * @var array
     *
     */
    protected $group_by = array();

    /**
     *
     * The list of HAVING conditions.
     *
     * @var array
     *
     */
    protected $having = array();

    /**
     *
     * The page number to select.
     *
     * @var int
     *
     */
    protected $page = 0;

    /**
     *
     * The number of rows per page.
     *
     * @var int
     *
     */
    protected $paging = 10;

    /**
     *
     * Tracks table references to avoid duplicate identifiers.
     *
     * @var array
     *
     */
    protected $table_refs = array();

    /**
     *
     * Returns this query object as an SQL statement string.
     *
     * @return string An SQL statement string.
     *
     */
    public function getStatement()
    {
        $union = '';
        if (! empty($this->union)) {
            $union = implode(PHP_EOL, $this->union) . PHP_EOL;
        }
        return $union . $this->build();
    }

    /**
     *
     * Sets the number of rows per page.
     *
     * @param int $paging The number of rows to page at.
     *
     * @return $this
     *
     */
    public function setPaging($paging)
    {
        $this->paging = (int) $paging;
        if ($this->page) {
            $this->setPagingLimitOffset();
        }
        return $this;
    }

    /**
     *
     * Gets the number of rows per page.
     *
     * @return int The number of rows per page.
     *
     */
    public function getPaging()
    {
        return $this->paging;
    }

    /**
     *
     * Makes the select FOR UPDATE (or not).
     *
     * @param bool $enable Whether or not the SELECT is FOR UPDATE (default
     * true).
     *
     * @return $this
     *
     */
    public function forUpdate($enable = true)
    {
        $this->for_update = (bool) $enable;
        return $this;
    }

    /**
     *
     * Makes the select DISTINCT (or not).
     *
     * @param bool $enable Whether or not the SELECT is DISTINCT (default
     * true).
     *
     * @return $this
     *
     */
    public function distinct($enable = true)
    {
        $this->setFlag('DISTINCT', $enable);
        return $this;
    }

    /**
     *
     * Is the select DISTINCT?
     *
     * @return bool
     *
     */
    public function isDistinct()
    {
        return $this->hasFlag('DISTINCT');
    }

    /**
     *
     * Adds columns to the query.
     *
     * Multiple calls to cols() will append to the list of columns, not
     * overwrite the previous columns.
     *
     * @param array $cols The column(s) to add to the query. The elements can be
     * any mix of these: `array("col", "col AS alias", "col" => "alias")`
     *
     * @return $this
     *
     */
    public function cols(array $cols)
    {
        foreach ($cols as $key => $val) {
            $this->addCol($key, $val);
        }
        return $this;
    }

    /**
     *
     * Adds a column and alias to the columns to be selected.
     *
     * @param mixed $key If an integer, ignored. Otherwise, the column to be
     * added.
     *
     * @param mixed $val If $key was an integer, the column to be added;
     * otherwise, the column alias.
     *
     * @return null
     *
     */
    protected function addCol($key, $val)
    {
        if (is_string($key)) {
            // [col => alias]
            $this->cols[$val] = $key;
        } else {
            $this->addColWithAlias($val);
        }
    }

    /**
     *
     * Adds a column with an alias to the columns to be selected.
     *
     * @param string $spec The column specification: "col alias",
     * "col AS alias", or something else entirely.
     *
     * @return null
     *
     */
    protected function addColWithAlias($spec)
    {
        $parts = explode(' ', $spec);
        $count = count($parts);
        if ($count == 2) {
            // "col alias"
            $this->cols[$parts[1]] = $parts[0];
        } elseif ($count == 3 && strtoupper($parts[1]) == 'AS') {
            // "col AS alias"
            $this->cols[$parts[2]] = $parts[0];
        } else {
            // no recognized alias
            $this->cols[] = $spec;
        }
    }

    /**
     *
     * Remove a column via its alias.
     *
     * @param string $alias The column to remove
     *
     * @return bool
     *
     */
    public function removeCol($alias)
    {
        if (isset($this->cols[$alias])) {
            unset($this->cols[$alias]);

            return true;
        }

        $index = array_search($alias, $this->cols);
        if ($index !== false) {
            unset($this->cols[$index]);
            return true;
        }

        return false;
    }

    /**
     *
     * Has the column or alias been added to the query?
     *
     * @param string $alias The column or alias to look for
     *
     * @return bool
     *
     */
    public function hasCol($alias)
    {
        return isset($this->cols[$alias]) || array_search($alias, $this->cols) !== false;
    }

    /**
     *
     * Does the query have any columns in it?
     *
     * @return bool
     *
     */
    public function hasCols()
    {
        return (bool) $this->cols;
    }

    /**
     *
     * Returns a list of columns.
     *
     * @return array
     *
     */
    public function getCols()
    {
        return $this->cols;
    }

    /**
     *
     * Tracks table references.
     *
     * @param string $type FROM, JOIN, etc.
     *
     * @param string $spec The table and alias name.
     *
     * @return null
     *
     * @throws Exception when the reference has already been used.
     *
     */
    protected function addTableRef($type, $spec)
    {
        $name = $spec;

        $pos = strripos($name, ' AS ');
        if ($pos !== false) {
            $name = trim(substr($name, $pos + 4));
        }

        if (isset($this->table_refs[$name])) {
            $used = $this->table_refs[$name];
            throw new Exception("Cannot reference '$type $spec' after '$used'");
        }

        $this->table_refs[$name] = "$type $spec";
    }

    /**
     *
     * Adds a FROM element to the query; quotes the table name automatically.
     *
     * @param string $spec The table specification; "foo" or "foo AS bar".
     *
     * @return $this
     *
     */
    public function from($spec)
    {
        $this->addTableRef('FROM', $spec);
        return $this->addFrom($this->quoter->quoteName($spec));
    }

    /**
     *
     * Adds a raw unquoted FROM element to the query; useful for adding FROM
     * elements that are functions.
     *
     * @param string $spec The table specification, e.g. "function_name()".
     *
     * @return $this
     *
     */
    public function fromRaw($spec)
    {
        $this->addTableRef('FROM', $spec);
        return $this->addFrom($spec);
    }

    /**
     *
     * Adds to the $from property and increments the key count.
     *
     * @param string $spec The table specification.
     *
     * @return $this
     *
     */
    protected function addFrom($spec)
    {
        $this->from[] = array($spec);
        $this->from_key ++;
        return $this;
    }

    /**
     *
     * Adds an aliased sub-select to the query.
     *
     * @param string|Select $spec If a Select object, use as the sub-select;
     * if a string, the sub-select string.
     *
     * @param string $name The alias name for the sub-select.
     *
     * @return $this
     *
     */
    public function fromSubSelect($spec, $name)
    {
        $this->addTableRef('FROM (SELECT ...) AS', $name);
        $spec = $this->subSelect($spec, '        ');
        $name = $this->quoter->quoteName($name);
        return $this->addFrom("({$spec}    ) AS $name");
    }

    /**
     *
     * Formats a sub-SELECT statement, binding values from a Select object as
     * needed.
     *
     * @param string|SelectInterface $spec A sub-SELECT specification.
     *
     * @param string $indent Indent each line with this string.
     *
     * @return string The sub-SELECT string.
     *
     */
    protected function subSelect($spec, $indent)
    {
        if ($spec instanceof SelectInterface) {
            $this->bindValues($spec->getBindValues());
        }

        return PHP_EOL . $indent
            . ltrim(preg_replace('/^/m', $indent, (string) $spec))
            . PHP_EOL;
    }

    /**
     *
     * Adds a JOIN table and columns to the query.
     *
     * @param string $join The join type: inner, left, natural, etc.
     *
     * @param string $spec The table specification; "foo" or "foo AS bar".
     *
     * @param string $cond Join on this condition.
     *
     * @param array $bind Values to bind to ?-placeholders in the condition.
     *
     * @return $this
     *
     * @throws Exception
     *
     */
    public function join($join, $spec, $cond = null, array $bind = array())
    {
        $join = strtoupper(ltrim("$join JOIN"));
        $this->addTableRef($join, $spec);

        $spec = $this->quoter->quoteName($spec);
        $cond = $this->fixJoinCondition($cond, $bind);
        return $this->addJoin(rtrim("$join $spec $cond"));
    }

    /**
     *
     * Fixes a JOIN condition to quote names in the condition and prefix it
     * with a condition type ('ON' is the default and 'USING' is recognized).
     *
     * @param string $cond Join on this condition.
     *
     * @param array $bind Values to bind to ?-placeholders in the condition.
     *
     * @return string
     *
     */
    protected function fixJoinCondition($cond, array $bind)
    {
        if (! $cond) {
            return '';
        }

        $cond = $this->quoter->quoteNamesIn($cond);
        $cond = $this->rebuildCondAndBindValues($cond, $bind);

        if (strtoupper(substr(ltrim($cond), 0, 3)) == 'ON ') {
            return $cond;
        }

        if (strtoupper(substr(ltrim($cond), 0, 6)) == 'USING ') {
            return $cond;
        }

        return 'ON ' . $cond;
    }

    /**
     *
     * Adds a INNER JOIN table and columns to the query.
     *
     * @param string $spec The table specification; "foo" or "foo AS bar".
     *
     * @param string $cond Join on this condition.
     *
     * @param array $bind Values to bind to ?-placeholders in the condition.
     *
     * @return $this
     *
     * @throws Exception
     *
     */
    public function innerJoin($spec, $cond = null, array $bind = array())
    {
        return $this->join('INNER', $spec, $cond, $bind);
    }

    /**
     *
     * Adds a LEFT JOIN table and columns to the query.
     *
     * @param string $spec The table specification; "foo" or "foo AS bar".
     *
     * @param string $cond Join on this condition.
     *
     * @param array $bind Values to bind to ?-placeholders in the condition.
     *
     * @return $this
     *
     * @throws Exception
     *
     */
    public function leftJoin($spec, $cond = null, array $bind = array())
    {
        return $this->join('LEFT', $spec, $cond, $bind);
    }

    /**
     *
     * Adds a JOIN to an aliased subselect and columns to the query.
     *
     * @param string $join The join type: inner, left, natural, etc.
     *
     * @param string|Select $spec If a Select
     * object, use as the sub-select; if a string, the sub-select
     * command string.
     *
     * @param string $name The alias name for the sub-select.
     *
     * @param string $cond Join on this condition.
     *
     * @param array $bind Values to bind to ?-placeholders in the condition.
     *
     * @return $this
     *
     * @throws Exception
     *
     */
    public function joinSubSelect($join, $spec, $name, $cond = null, array $bind = array())
    {
        $join = strtoupper(ltrim("$join JOIN"));
        $this->addTableRef("$join (SELECT ...) AS", $name);

        $spec = $this->subSelect($spec, '            ');
        $name = $this->quoter->quoteName($name);
        $cond = $this->fixJoinCondition($cond, $bind);

        $text = rtrim("$join ($spec        ) AS $name $cond");
        return $this->addJoin('        ' . $text);
    }

    /**
     *
     * Adds the JOIN to the right place, given whether or not a FROM has been
     * specified yet.
     *
     * @param string $spec The JOIN clause.
     *
     * @return $this
     *
     */
    protected function addJoin($spec)
    {
        $from_key = ($this->from_key == -1) ? 0 : $this->from_key;
        $this->join[$from_key][] = $spec;
        return $this;
    }

    /**
     *
     * Adds grouping to the query.
     *
     * @param array $spec The column(s) to group by.
     *
     * @return $this
     *
     */
    public function groupBy(array $spec)
    {
        foreach ($spec as $col) {
            $this->group_by[] = $this->quoter->quoteNamesIn($col);
        }
        return $this;
    }

    /**
     *
     * Adds a HAVING condition to the query by AND. If the condition has
     * ?-placeholders, additional arguments to the method will be bound to
     * those placeholders sequentially.
     *
     * @param string $cond The HAVING condition.
     *
     * @param array ...$bind arguments to bind to placeholders
     *
     * @return $this
     *
     */
    public function having($cond, ...$bind)
    {
        $this->addClauseCondWithBind('having', 'AND', $cond, $bind);
        return $this;
    }

    /**
     *
     * Adds a HAVING condition to the query by AND. If the condition has
     * ?-placeholders, additional arguments to the method will be bound to
     * those placeholders sequentially.
     *
     * @param string $cond The HAVING condition.
     *
     * @param array ...$bind arguments to bind to placeholders
     *
     * @return $this
     *
     * @see having()
     *
     */
    public function orHaving($cond, ...$bind)
    {
        $this->addClauseCondWithBind('having', 'OR', $cond, $bind);
        return $this;
    }

    /**
     *
     * Sets the limit and count by page number.
     *
     * @param int $page Limit results to this page number.
     *
     * @return $this
     *
     */
    public function page($page)
    {
        $this->page = (int) $page;
        $this->setPagingLimitOffset();
        return $this;
    }

    /**
     *
     * Updates the limit and offset values when changing pagination.
     *
     * @return null
     *
     */
    protected function setPagingLimitOffset()
    {
        $this->setLimit(0);
        $this->setOffset(0);
        if ($this->page) {
            $this->setLimit($this->paging);
            $this->setOffset($this->paging * ($this->page - 1));
        }
    }

    /**
     *
     * Returns the page number being selected.
     *
     * @return int
     *
     */
    public function getPage()
    {
        return $this->page;
    }

    /**
     *
     * Takes the current select properties and retains them, then sets
     * UNION for the next set of properties.
     *
     * @return $this
     *
     */
    public function union()
    {
        $this->union[] = $this->build() . PHP_EOL . 'UNION';
        $this->reset();
        return $this;
    }

    /**
     *
     * Takes the current select properties and retains them, then sets
     * UNION ALL for the next set of properties.
     *
     * @return $this
     *
     */
    public function unionAll()
    {
        $this->union[] = $this->build() . PHP_EOL . 'UNION ALL';
        $this->reset();
        return $this;
    }

    /**
     *
     * Clears the current select properties; generally used after adding a
     * union.
     *
     * @return null
     *
     */
    public function reset()
    {
        $this->resetFlags();
        $this->resetCols();
        $this->resetTables();
        $this->resetWhere();
        $this->resetGroupBy();
        $this->resetHaving();
        $this->resetOrderBy();
        $this->limit(0);
        $this->offset(0);
        $this->page(0);
        $this->forUpdate(false);
    }

    /**
     *
     * Resets the columns on the SELECT.
     *
     * @return $this
     *
     */
    public function resetCols()
    {
        $this->cols = array();
        return $this;
    }

    /**
     *
     * Resets the FROM and JOIN clauses on the SELECT.
     *
     * @return $this
     *
     */
    public function resetTables()
    {
        $this->from = array();
        $this->from_key = -1;
        $this->join = array();
        $this->table_refs = array();
        return $this;
    }

    /**
     *
     * Resets the WHERE clause on the SELECT.
     *
     * @return $this
     *
     */
    public function resetWhere()
    {
        $this->where = array();
        return $this;
    }

    /**
     *
     * Resets the GROUP BY clause on the SELECT.
     *
     * @return $this
     *
     */
    public function resetGroupBy()
    {
        $this->group_by = array();
        return $this;
    }

    /**
     *
     * Resets the HAVING clause on the SELECT.
     *
     * @return $this
     *
     */
    public function resetHaving()
    {
        $this->having = array();
        return $this;
    }

    /**
     *
     * Resets the ORDER BY clause on the SELECT.
     *
     * @return $this
     *
     */
    public function resetOrderBy()
    {
        $this->order_by = array();
        return $this;
    }

    /**
     *
     * Resets the UNION and UNION ALL clauses on the SELECT.
     *
     * @return $this
     *
     */
    public function resetUnions()
    {
        $this->union = array();
        return $this;
    }

    /**
     *
     * Builds this query object into a string.
     *
     * @return string
     *
     */
    protected function build()
    {
        return 'SELECT'
            . $this->buildFlags()
            . $this->buildCols()
            . $this->buildFrom() // includes JOIN
            . $this->buildWhere()
            . $this->buildGroupBy()
            . $this->buildHaving()
            . $this->buildOrderBy()
            . $this->buildLimit()
            . $this->buildForUpdate();
    }

    /**
     *
     * Builds the columns clause.
     *
     * @return string
     *
     * @throws Exception when there are no columns in the SELECT.
     *
     */
    protected function buildCols()
    {
        if (empty($this->cols)) {
            throw new Exception('No columns in the SELECT.');
        }

        $cols = array();
        foreach ($this->cols as $key => $val) {
            if (is_int($key)) {
                $cols[] = $this->quoter->quoteNamesIn($val);
            } else {
                $cols[] = $this->quoter->quoteNamesIn("$val AS $key");
            }
        }

        return $this->indentCsv($cols);
    }

    /**
     *
     * Builds the FROM clause.
     *
     * @return string
     *
     */
    protected function buildFrom()
    {
        if (empty($this->from)) {
            return ''; // not applicable
        }

        $refs = array();
        foreach ($this->from as $from_key => $from) {
            if (isset($this->join[$from_key])) {
                $from = array_merge($from, $this->join[$from_key]);
            }
            $refs[] = implode(PHP_EOL, $from);
        }
        return PHP_EOL . 'FROM' . $this->indentCsv($refs);
    }

    /**
     *
     * Builds the GROUP BY clause.
     *
     * @return string
     *
     */
    protected function buildGroupBy()
    {
        if (empty($this->group_by)) {
            return ''; // not applicable
        }

        return PHP_EOL . 'GROUP BY' . $this->indentCsv($this->group_by);
    }

    /**
     *
     * Builds the HAVING clause.
     *
     * @return string
     *
     */
    protected function buildHaving()
    {
        if (empty($this->having)) {
            return ''; // not applicable
        }

        return PHP_EOL . 'HAVING' . $this->indent($this->having);
    }

    /**
     *
     * Builds the FOR UPDATE clause.
     *
     * @return string
     *
     */
    protected function buildForUpdate()
    {
        if (! $this->for_update) {
            return ''; // not applicable
        }

        return PHP_EOL . 'FOR UPDATE';
    }

    /**
     *
     * Sets a limit count on the query.
     *
     * @param int $limit The number of rows to select.
     *
     * @return $this
     *
     */
    public function limit($limit)
    {
        $this->setLimit($limit);
        if ($this->page) {
            $this->page = 0;
            $this->setOffset(0);
        }
        return $this;
    }

    /**
     *
     * Sets a limit offset on the query.
     *
     * @param int $offset Start returning after this many rows.
     *
     * @return $this
     *
     */
    public function offset($offset)
    {
        $this->setOffset($offset);
        if ($this->page) {
            $this->page = 0;
            $this->setLimit(0);
        }
        return $this;
    }

    /**
     *
     * Adds a column order to the query.
     *
     * @param array $spec The columns and direction to order by.
     *
     * @return $this
     *
     */
    public function orderBy(array $spec)
    {
        return $this->addOrderBy($spec);
    }
}


1		<?php
2		/**
3		*
4		* This file is part of Aura for PHP.
5		*
6		* @license http://opensource.org/licenses/bsd-license.php BSD
7		*
8		*/
9		namespace Aura\SqlQuery\Common;
10
11		use Aura\SqlQuery\AbstractQuery;
12		use Aura\SqlQuery\Exception;
13
14		/**
15		*
16		* An object for SELECT queries.
17		*
18		* @package Aura.SqlQuery
19		*
20		*/
21		class Select extends AbstractQuery implements SelectInterface, SubselectInterface
22		{
23		use WhereTrait;
24		use LimitOffsetTrait { limit as setLimit; offset as setOffset; }
25
26		/**
27		*
28		* An array of union SELECT statements.
29		*
30		* @var array
31		*
32		*/
33		protected $union = array();
34
35		/**
36		*
37		* Is this a SELECT FOR UPDATE?
38		*
39		* @var
40		*
41		*/
42		protected $for_update = false;
43
44		/**
45		*
46		* The columns to be selected.
47		*
48		* @var array
49		*
50		*/
51		protected $cols = array();
52
53		/**
54		*
55		* Select from these tables; includes JOIN clauses.
56		*
57		* @var array
58		*
59		*/
60		protected $from = array();
61
62		/**
63		*
64		* The current key in the `$from` array.
65		*
66		* @var int
67		*
68		*/
69		protected $from_key = -1;
70
71		/**
72		*
73		* Tracks which JOIN clauses are attached to which FROM tables.
74		*
75		* @var array
76		*
77		*/
78		protected $join = array();
79
80		/**
81		*
82		* GROUP BY these columns.
83		*
84		* @var array
85		*
86		*/
87		protected $group_by = array();
88
89		/**
90		*
91		* The list of HAVING conditions.
92		*
93		* @var array
94		*
95		*/
96		protected $having = array();
97
98		/**
99		*
100		* The page number to select.
101		*
102		* @var int
103		*
104		*/
105		protected $page = 0;
106
107		/**
108		*
109		* The number of rows per page.
110		*
111		* @var int
112		*
113		*/
114		protected $paging = 10;
115
116		/**
117		*
118		* Tracks table references to avoid duplicate identifiers.
119		*
120		* @var array
121		*
122		*/
123		protected $table_refs = array();
124
125		/**
126		*
127		* Returns this query object as an SQL statement string.
128		*
129		* @return string An SQL statement string.
130		*
131		*/
132	189	public function getStatement()
133		{
134	189	$union = '';
135	189	if (! empty($this->union)) {
136	15	$union = implode(PHP_EOL, $this->union) . PHP_EOL;
137	15	}
138	189	return $union . $this->build();
139		}
140
141		/**
142		*
143		* Sets the number of rows per page.
144		*
145		* @param int $paging The number of rows to page at.
146		*
147		* @return $this
148		*
149		*/
150	10	public function setPaging($paging)
151		{
152	10	$this->paging = (int) $paging;
153	10	if ($this->page) {
154	5	$this->setPagingLimitOffset();
155	5	}
156	10	return $this;
157		}
158
159		/**
160		*
161		* Gets the number of rows per page.
162		*
163		* @return int The number of rows per page.
164		*
165		*/
166	10	public function getPaging()
167		{
168	10	return $this->paging;
169		}
170
171		/**
172		*
173		* Makes the select FOR UPDATE (or not).
174		*
175		* @param bool $enable Whether or not the SELECT is FOR UPDATE (default
176		* true).
177		*
178		* @return $this
179		*
180		*/
181	25	public function forUpdate($enable = true)
182		{
183	25	$this->for_update = (bool) $enable;
184	25	return $this;
185		}
186
187		/**
188		*
189		* Makes the select DISTINCT (or not).
190		*
191		* @param bool $enable Whether or not the SELECT is DISTINCT (default
192		* true).
193		*
194		* @return $this
195		*
196		*/
197	16	public function distinct($enable = true)
198		{
199	16	$this->setFlag('DISTINCT', $enable);
200	16	return $this;
201		}
202
203		/**
204		*
205		* Is the select DISTINCT?
206		*
207		* @return bool
208		*
209		*/
210	10	public function isDistinct()
211		{
212	10	return $this->hasFlag('DISTINCT');
213		}
214
215		/**
216		*
217		* Adds columns to the query.
218		*
219		* Multiple calls to cols() will append to the list of columns, not
220		* overwrite the previous columns.
221		*
222		* @param array $cols The column(s) to add to the query. The elements can be
223		* any mix of these: `array("col", "col AS alias", "col" => "alias")`
224		*
225		* @return $this
226		*
227		*/
228	229	public function cols(array $cols)
229		{
230	229	foreach ($cols as $key => $val) {
231	229	$this->addCol($key, $val);
232	229	}
233	229	return $this;
234		}
235
236		/**
237		*
238		* Adds a column and alias to the columns to be selected.
239		*
240		* @param mixed $key If an integer, ignored. Otherwise, the column to be
241		* added.
242		*
243		* @param mixed $val If $key was an integer, the column to be added;
244		* otherwise, the column alias.
245		*
246		* @return null
247		*
248		*/
249	229	protected function addCol($key, $val)
250		{
251	229	if (is_string($key)) {
252		// [col => alias]
253	25	$this->cols[$val] = $key;
254	25	} else {
255	219	$this->addColWithAlias($val);
256		}
257	229	}
258
259		/**
260		*
261		* Adds a column with an alias to the columns to be selected.
262		*
263		* @param string $spec The column specification: "col alias",
264		* "col AS alias", or something else entirely.
265		*
266		* @return null
267		*
268		*/
269	219	protected function addColWithAlias($spec)
270		{
271	219	$parts = explode(' ', $spec);
272	219	$count = count($parts);
273	219	if ($count == 2) {
274		// "col alias"
275	5	$this->cols[$parts[1]] = $parts[0];
276	219	} elseif ($count == 3 && strtoupper($parts[1]) == 'AS') {
277		// "col AS alias"
278	5	$this->cols[$parts[2]] = $parts[0];
279	5	} else {
280		// no recognized alias
281	219	$this->cols[] = $spec;
282		}
283	219	}
284
285		/**
286		*
287		* Remove a column via its alias.
288		*
289		* @param string $alias The column to remove
290		*
291		* @return bool
292		*
293		*/
294	15	public function removeCol($alias)
295		{
296	15	if (isset($this->cols[$alias])) {
297	5	unset($this->cols[$alias]);
298
299	5	return true;
300		}
301
302	10	$index = array_search($alias, $this->cols);
303	10	if ($index !== false) {
304	5	unset($this->cols[$index]);
305	5	return true;
306		}
307
308	5	return false;
309		}
310
311		/**
312		*
313		* Has the column or alias been added to the query?
314		*
315		* @param string $alias The column or alias to look for
316		*
317		* @return bool
318		*
319		*/
320	5	public function hasCol($alias)
321		{
322	5	return isset($this->cols[$alias]) \|\| array_search($alias, $this->cols) !== false;
323		}
324
325		/**
326		*
327		* Does the query have any columns in it?
328		*
329		* @return bool
330		*
331		*/
332	5	public function hasCols()
333		{
334	5	return (bool) $this->cols;
335		}
336
337		/**
338		*
339		* Returns a list of columns.
340		*
341		* @return array
342		*
343		*/
344	15	public function getCols()
345		{
346	15	return $this->cols;
347		}
348
349		/**
350		*
351		* Tracks table references.
352		*
353		* @param string $type FROM, JOIN, etc.
354		*
355		* @param string $spec The table and alias name.
356		*
357		* @return null
358		*
359		* @throws Exception when the reference has already been used.
360		*
361		*/
362	154	protected function addTableRef($type, $spec)
363		{
364	154	$name = $spec;
365
366	154	$pos = strripos($name, ' AS ');
367	154	if ($pos !== false) {
368	35	$name = trim(substr($name, $pos + 4));
369	35	}
370
371	154	if (isset($this->table_refs[$name])) {
372	25	$used = $this->table_refs[$name];
373	25	throw new Exception("Cannot reference '$type $spec' after '$used'");
374		}
375
376	154	$this->table_refs[$name] = "$type $spec";
377	154	}
378
379		/**
380		*
381		* Adds a FROM element to the query; quotes the table name automatically.
382		*
383		* @param string $spec The table specification; "foo" or "foo AS bar".
384		*
385		* @return $this
386		*
387		*/
388	144	public function from($spec)
389		{
390	144	$this->addTableRef('FROM', $spec);
391	144	return $this->addFrom($this->quoter->quoteName($spec));
392		}
393
394		/**
395		*
396		* Adds a raw unquoted FROM element to the query; useful for adding FROM
397		* elements that are functions.
398		*
399		* @param string $spec The table specification, e.g. "function_name()".
400		*
401		* @return $this
402		*
403		*/
404	5	public function fromRaw($spec)
405		{
406	5	$this->addTableRef('FROM', $spec);
407	5	return $this->addFrom($spec);
408		}
409
410		/**
411		*
412		* Adds to the $from property and increments the key count.
413		*
414		* @param string $spec The table specification.
415		*
416		* @return $this
417		*
418		*/
419	154	protected function addFrom($spec)
420		{
421	154	$this->from[] = array($spec);
422	154	$this->from_key ++;
423	154	return $this;
424		}
425
426		/**
427		*
428		* Adds an aliased sub-select to the query.
429		*
430		* @param string\|Select $spec If a Select object, use as the sub-select;
431		* if a string, the sub-select string.
432		*
433		* @param string $name The alias name for the sub-select.
434		*
435		* @return $this
436		*
437		*/
438	15	public function fromSubSelect($spec, $name)
439		{
440	15	$this->addTableRef('FROM (SELECT ...) AS', $name);
441	10	$spec = $this->subSelect($spec, ' ');
442	10	$name = $this->quoter->quoteName($name);
443	10	return $this->addFrom("({$spec} ) AS $name");
444		}
445
446		/**
447		*
448		* Formats a sub-SELECT statement, binding values from a Select object as
449		* needed.
450		*
451		* @param string\|SelectInterface $spec A sub-SELECT specification.
452		*
453		* @param string $indent Indent each line with this string.
454		*
455		* @return string The sub-SELECT string.
456		*
457		*/
458	25	protected function subSelect($spec, $indent)
459		{
460	25	if ($spec instanceof SelectInterface) {
461	10	$this->bindValues($spec->getBindValues());
462	10	}
463
464	25	return PHP_EOL . $indent
465	25	. ltrim(preg_replace('/^/m', $indent, (string) $spec))
466	25	. PHP_EOL;
467		}
468
469		/**
470		*
471		* Adds a JOIN table and columns to the query.
472		*
473		* @param string $join The join type: inner, left, natural, etc.
474		*
475		* @param string $spec The table specification; "foo" or "foo AS bar".
476		*
477		* @param string $cond Join on this condition.
478		*
479		* @param array $bind Values to bind to ?-placeholders in the condition.
480		*
481		* @return $this
482		*
483		* @throws Exception
484		*
485		*/
486	45	public function join($join, $spec, $cond = null, array $bind = array())
487		{
488	45	$join = strtoupper(ltrim("$join JOIN"));
489	45	$this->addTableRef($join, $spec);
490
491	40	$spec = $this->quoter->quoteName($spec);
492	40	$cond = $this->fixJoinCondition($cond, $bind);
493	40	return $this->addJoin(rtrim("$join $spec $cond"));
494		}
495
496		/**
497		*
498		* Fixes a JOIN condition to quote names in the condition and prefix it
499		* with a condition type ('ON' is the default and 'USING' is recognized).
500		*
501		* @param string $cond Join on this condition.
502		*
503		* @param array $bind Values to bind to ?-placeholders in the condition.
504		*
505		* @return string
506		*
507		*/
508	55	protected function fixJoinCondition($cond, array $bind)
509		{
510	55	if (! $cond) {
511	25	return '';
512		}
513
514	55	$cond = $this->quoter->quoteNamesIn($cond);
515	55	$cond = $this->rebuildCondAndBindValues($cond, $bind);
516
517	55	if (strtoupper(substr(ltrim($cond), 0, 3)) == 'ON ') {
518	5	return $cond;
519		}
520
521	55	if (strtoupper(substr(ltrim($cond), 0, 6)) == 'USING ') {
522	5	return $cond;
523		}
524
525	50	return 'ON ' . $cond;
526		}
527
528		/**
529		*
530		* Adds a INNER JOIN table and columns to the query.
531		*
532		* @param string $spec The table specification; "foo" or "foo AS bar".
533		*
534		* @param string $cond Join on this condition.
535		*
536		* @param array $bind Values to bind to ?-placeholders in the condition.
537		*
538		* @return $this
539		*
540		* @throws Exception
541		*
542		*/
543	10	public function innerJoin($spec, $cond = null, array $bind = array())
544		{
545	10	return $this->join('INNER', $spec, $cond, $bind);
546		}
547
548		/**
549		*
550		* Adds a LEFT JOIN table and columns to the query.
551		*
552		* @param string $spec The table specification; "foo" or "foo AS bar".
553		*
554		* @param string $cond Join on this condition.
555		*
556		* @param array $bind Values to bind to ?-placeholders in the condition.
557		*
558		* @return $this
559		*
560		* @throws Exception
561		*
562		*/
563	10	public function leftJoin($spec, $cond = null, array $bind = array())
564		{
565	10	return $this->join('LEFT', $spec, $cond, $bind);
566		}
567
568		/**
569		*
570		* Adds a JOIN to an aliased subselect and columns to the query.
571		*
572		* @param string $join The join type: inner, left, natural, etc.
573		*
574		* @param string\|Select $spec If a Select
575		* object, use as the sub-select; if a string, the sub-select
576		* command string.
577		*
578		* @param string $name The alias name for the sub-select.
579		*
580		* @param string $cond Join on this condition.
581		*
582		* @param array $bind Values to bind to ?-placeholders in the condition.
583		*
584		* @return $this
585		*
586		* @throws Exception
587		*
588		*/
589	20	public function joinSubSelect($join, $spec, $name, $cond = null, array $bind = array())
590		{
591	20	$join = strtoupper(ltrim("$join JOIN"));
592	20	$this->addTableRef("$join (SELECT ...) AS", $name);
593
594	15	$spec = $this->subSelect($spec, ' ');
595	15	$name = $this->quoter->quoteName($name);
596	15	$cond = $this->fixJoinCondition($cond, $bind);
597
598	15	$text = rtrim("$join ($spec ) AS $name $cond");
599	15	return $this->addJoin(' ' . $text);
600		}
601
602		/**
603		*
604		* Adds the JOIN to the right place, given whether or not a FROM has been
605		* specified yet.
606		*
607		* @param string $spec The JOIN clause.
608		*
609		* @return $this
610		*
611		*/
612	55	protected function addJoin($spec)
613		{
614	55	$from_key = ($this->from_key == -1) ? 0 : $this->from_key;
615	55	$this->join[$from_key][] = $spec;
616	55	return $this;
617		}
618
619		/**
620		*
621		* Adds grouping to the query.
622		*
623		* @param array $spec The column(s) to group by.
624		*
625		* @return $this
626		*
627		*/
628	5	public function groupBy(array $spec)
629		{
630	5	foreach ($spec as $col) {
631	5	$this->group_by[] = $this->quoter->quoteNamesIn($col);
632	5	}
633	5	return $this;
634		}
635
636		/**
637		*
638		* Adds a HAVING condition to the query by AND. If the condition has
639		* ?-placeholders, additional arguments to the method will be bound to
640		* those placeholders sequentially.
641		*
642		* @param string $cond The HAVING condition.
643		*
644		* @param array ...$bind arguments to bind to placeholders
645		*
646		* @return $this
647		*
648		*/
649	10	public function having($cond, ...$bind)
650		{
651	10	$this->addClauseCondWithBind('having', 'AND', $cond, $bind);
652	10	return $this;
653		}
654
655		/**
656		*
657		* Adds a HAVING condition to the query by AND. If the condition has
658		* ?-placeholders, additional arguments to the method will be bound to
659		* those placeholders sequentially.
660		*
661		* @param string $cond The HAVING condition.
662		*
663		* @param array ...$bind arguments to bind to placeholders
664		*
665		* @return $this
666		*
667		* @see having()
668		*
669		*/
670	5	public function orHaving($cond, ...$bind)
671		{
672	5	$this->addClauseCondWithBind('having', 'OR', $cond, $bind);
673	5	return $this;
674		}
675
676		/**
677		*
678		* Sets the limit and count by page number.
679		*
680		* @param int $page Limit results to this page number.
681		*
682		* @return $this
683		*
684		*/
685	30	public function page($page)
686		{
687	30	$this->page = (int) $page;
688	30	$this->setPagingLimitOffset();
689	30	return $this;
690		}
691
692		/**
693		*
694		* Updates the limit and offset values when changing pagination.
695		*
696		* @return null
697		*
698		*/
699	30	protected function setPagingLimitOffset()
700		{
701	30	$this->setLimit(0);
702	30	$this->setOffset(0);
703	30	if ($this->page) {
704	10	$this->setLimit($this->paging);
705	10	$this->setOffset($this->paging * ($this->page - 1));
706	10	}
707	30	}
708
709		/**
710		*
711		* Returns the page number being selected.
712		*
713		* @return int
714		*
715		*/
716	5	public function getPage()
717		{
718	5	return $this->page;
719		}
720
721		/**
722		*
723		* Takes the current select properties and retains them, then sets
724		* UNION for the next set of properties.
725		*
726		* @return $this
727		*
728		*/
729	15	public function union()
730		{
731	15	$this->union[] = $this->build() . PHP_EOL . 'UNION';
732	15	$this->reset();
733	15	return $this;
734		}
735
736		/**
737		*
738		* Takes the current select properties and retains them, then sets
739		* UNION ALL for the next set of properties.
740		*
741		* @return $this
742		*
743		*/
744	5	public function unionAll()
745		{
746	5	$this->union[] = $this->build() . PHP_EOL . 'UNION ALL';
747	5	$this->reset();
748	5	return $this;
749		}
750
751		/**
752		*
753		* Clears the current select properties; generally used after adding a
754		* union.
755		*
756		* @return null
757		*
758		*/
759	20	public function reset()
760		{
761	20	$this->resetFlags();
762	20	$this->resetCols();
763	20	$this->resetTables();
764	20	$this->resetWhere();
765	20	$this->resetGroupBy();
766	20	$this->resetHaving();
767	20	$this->resetOrderBy();
768	20	$this->limit(0);
769	20	$this->offset(0);
770	20	$this->page(0);
771	20	$this->forUpdate(false);
772	20	}
773
774		/**
775		*
776		* Resets the columns on the SELECT.
777		*
778		* @return $this
779		*
780		*/
781	20	public function resetCols()
782		{
783	20	$this->cols = array();
784	20	return $this;
785		}
786
787		/**
788		*
789		* Resets the FROM and JOIN clauses on the SELECT.
790		*
791		* @return $this
792		*
793		*/
794	20	public function resetTables()
795		{
796	20	$this->from = array();
797	20	$this->from_key = -1;
798	20	$this->join = array();
799	20	$this->table_refs = array();
800	20	return $this;
801		}
802
803		/**
804		*
805		* Resets the WHERE clause on the SELECT.
806		*
807		* @return $this
808		*
809		*/
810	20	public function resetWhere()
811		{
812	20	$this->where = array();
813	20	return $this;
814		}
815
816		/**
817		*
818		* Resets the GROUP BY clause on the SELECT.
819		*
820		* @return $this
821		*
822		*/
823	20	public function resetGroupBy()
824		{
825	20	$this->group_by = array();
826	20	return $this;
827		}
828
829		/**
830		*
831		* Resets the HAVING clause on the SELECT.
832		*
833		* @return $this
834		*
835		*/
836	20	public function resetHaving()
837		{
838	20	$this->having = array();
839	20	return $this;
840		}
841
842		/**
843		*
844		* Resets the ORDER BY clause on the SELECT.
845		*
846		* @return $this
847		*
848		*/
849	20	public function resetOrderBy()
850		{
851	20	$this->order_by = array();
852	20	return $this;
853		}
854
855		/**
856		*
857		* Resets the UNION and UNION ALL clauses on the SELECT.
858		*
859		* @return $this
860		*
861		*/
862	5	public function resetUnions()
863		{
864	5	$this->union = array();
865	5	return $this;
866		}
867
868		/**
869		*
870		* Builds this query object into a string.
871		*
872		* @return string
873		*
874		*/
875	189	protected function build()
876		{
877		return 'SELECT'
878	189	. $this->buildFlags()
879	189	. $this->buildCols()
880	184	. $this->buildFrom() // includes JOIN
881	184	. $this->buildWhere()
882	184	. $this->buildGroupBy()
883	184	. $this->buildHaving()
884	184	. $this->buildOrderBy()
885	184	. $this->buildLimit()
886	184	. $this->buildForUpdate();
887		}
888
889		/**
890		*
891		* Builds the columns clause.
892		*
893		* @return string
894		*
895		* @throws Exception when there are no columns in the SELECT.
896		*
897		*/
898	189	protected function buildCols()
899		{
900	189	if (empty($this->cols)) {
901	5	throw new Exception('No columns in the SELECT.');
902		}
903
904	184	$cols = array();
905	184	foreach ($this->cols as $key => $val) {
906	184	if (is_int($key)) {
907	184	$cols[] = $this->quoter->quoteNamesIn($val);
908	184	} else {
909	10	$cols[] = $this->quoter->quoteNamesIn("$val AS $key");
910		}
911	184	}
912
913	184	return $this->indentCsv($cols);
914		}
915
916		/**
917		*
918		* Builds the FROM clause.
919		*
920		* @return string
921		*
922		*/
923	184	protected function buildFrom()
924		{
925	184	if (empty($this->from)) {
926	60	return ''; // not applicable
927		}
928
929	124	$refs = array();
930	124	foreach ($this->from as $from_key => $from) {
931	124	if (isset($this->join[$from_key])) {
932	55	$from = array_merge($from, $this->join[$from_key]);
933	55	}
934	124	$refs[] = implode(PHP_EOL, $from);
935	124	}
936	124	return PHP_EOL . 'FROM' . $this->indentCsv($refs);
937		}
938
939		/**
940		*
941		* Builds the GROUP BY clause.
942		*
943		* @return string
944		*
945		*/
946	184	protected function buildGroupBy()
947		{
948	184	if (empty($this->group_by)) {
949	179	return ''; // not applicable
950		}
951
952	5	return PHP_EOL . 'GROUP BY' . $this->indentCsv($this->group_by);
953		}
954
955		/**
956		*
957		* Builds the HAVING clause.
958		*
959		* @return string
960		*
961		*/
962	184	protected function buildHaving()
963		{
964	184	if (empty($this->having)) {
965	169	return ''; // not applicable
966		}
967
968	15	return PHP_EOL . 'HAVING' . $this->indent($this->having);
969		}
970
971		/**
972		*
973		* Builds the FOR UPDATE clause.
974		*
975		* @return string
976		*
977		*/
978	184	protected function buildForUpdate()
979		{
980	184	if (! $this->for_update) {
981	179	return ''; // not applicable
982		}
983
984	5	return PHP_EOL . 'FOR UPDATE';
985		}
986
987		/**
988		*
989		* Sets a limit count on the query.
990		*
991		* @param int $limit The number of rows to select.
992		*
993		* @return $this
994		*
995		*/
996	35	public function limit($limit)
997		{
998	35	$this->setLimit($limit);
999	35	if ($this->page) {
1000	5	$this->page = 0;
1001	5	$this->setOffset(0);
1002	5	}
1003	35	return $this;
1004		}
1005
1006		/**
1007		*
1008		* Sets a limit offset on the query.
1009		*
1010		* @param int $offset Start returning after this many rows.
1011		*
1012		* @return $this
1013		*
1014		*/
1015	35	public function offset($offset)
1016		{
1017	35	$this->setOffset($offset);
1018	35	if ($this->page) {
1019	5	$this->page = 0;
1020	5	$this->setLimit(0);
1021	5	}
1022	35	return $this;
1023		}
1024
1025		/**
1026		*
1027		* Adds a column order to the query.
1028		*
1029		* @param array $spec The columns and direction to order by.
1030		*
1031		* @return $this
1032		*
1033		*/
1034	5	public function orderBy(array $spec)
1035		{
1036	5	return $this->addOrderBy($spec);
1037		}
1038		}
1039

auraphp / Aura.SqlQuery

Push — 3.x ( 9be43c...53c3d5 )

Select::removeCol() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like