Select::unionAll() - Code Metrics - Inspection of "join*() now support only named binding" - auraphp/Aura.SqlQuery - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — 3.x-named-placeholders-only ( 6a3d98...913a85 )

by Paul

created 2017-03-21 20:22 UTC

Select::unionAll() A

↳ Parent: Select

Complexity

Conditions	1
Paths	1

Size

Total Lines	6
Code Lines	4

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	4
CRAP Score	1

Importance

Changes

Metric	Value
dl	0
loc	6
c	0
b	0
f	0
ccs	4
cts	4
cp	1
rs	9.4285
cc	1
eloc	4
nc	1
nop	0
crap	1

<?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);

        foreach ($bind as $key => $val) {
            $this->bindValue($key, $val);
        }

        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.
     *
     * @param string $cond The HAVING condition.
     *
     * @param array $bind arguments to bind to placeholders
     *
     * @return $this
     *
     */
    public function having($cond, array $bind = [])
    {
        $this->addClauseCondWithBind('having', 'AND', $cond, $bind);
        return $this;
    }

    /**
     *
     * Adds a HAVING condition to the query by OR.
     *
     * @param string $cond The HAVING condition.
     *
     * @param array $bind arguments to bind to placeholders
     *
     * @return $this
     *
     * @see having()
     *
     */
    public function orHaving($cond, array $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()
    {
        $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 'SELECT'
            . $this->builder->buildFlags($this->flags)
class MyClass { }

$x = new MyClass();
$x->foo = true;
            . $this->builder->buildCols($cols)
            . $this->builder->buildFrom($this->from, $this->join)
            . $this->builder->buildWhere($this->where)
            . $this->builder->buildGroupBy($this->group_by)
            . $this->builder->buildHaving($this->having)
            . $this->builder->buildOrderBy($this->order_by)
            . $this->builder->buildLimitOffset($this->limit, $this->offset)
            . $this->builder->buildForUpdate($this->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	179	public function getStatement()
133		{
134	179	$union = '';
135	179	if (! empty($this->union)) {
136	15	$union = implode(PHP_EOL, $this->union) . PHP_EOL;
137	15	}
138	179	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	219	public function cols(array $cols)
229		{
230	219	foreach ($cols as $key => $val) {
231	219	$this->addCol($key, $val);
232	219	}
233	219	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	219	protected function addCol($key, $val)
250		{
251	219	if (is_string($key)) {
252		// [col => alias]
253	25	$this->cols[$val] = $key;
254	25	} else {
255	209	$this->addColWithAlias($val);
256		}
257	219	}
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	209	protected function addColWithAlias($spec)
270		{
271	209	$parts = explode(' ', $spec);
272	209	$count = count($parts);
273	209	if ($count == 2) {
274		// "col alias"
275	5	$this->cols[$parts[1]] = $parts[0];
276	209	} 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	209	$this->cols[] = $spec;
282		}
283	209	}
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	144	protected function addTableRef($type, $spec)
363		{
364	144	$name = $spec;
365
366	144	$pos = strripos($name, ' AS ');
367	144	if ($pos !== false) {
368	25	$name = trim(substr($name, $pos + 4));
369	25	}
370
371	144	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	144	$this->table_refs[$name] = "$type $spec";
377	144	}
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	134	public function from($spec)
389		{
390	134	$this->addTableRef('FROM', $spec);
391	134	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	144	protected function addFrom($spec)
420		{
421	144	$this->from[] = array($spec);
422	144	$this->from_key ++;
423	144	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
516	55	foreach ($bind as $key => $val) {
517	10	$this->bindValue($key, $val);
518	55	}
519
520	55	if (strtoupper(substr(ltrim($cond), 0, 3)) == 'ON ') {
521	5	return $cond;
522		}
523
524	55	if (strtoupper(substr(ltrim($cond), 0, 6)) == 'USING ') {
525	5	return $cond;
526		}
527
528	50	return 'ON ' . $cond;
529		}
530
531		/**
532		*
533		* Adds a INNER JOIN table and columns to the query.
534		*
535		* @param string $spec The table specification; "foo" or "foo AS bar".
536		*
537		* @param string $cond Join on this condition.
538		*
539		* @param array $bind Values to bind to ?-placeholders in the condition.
540		*
541		* @return $this
542		*
543		* @throws Exception
544		*
545		*/
546	10	public function innerJoin($spec, $cond = null, array $bind = array())
547		{
548	10	return $this->join('INNER', $spec, $cond, $bind);
549		}
550
551		/**
552		*
553		* Adds a LEFT JOIN table and columns to the query.
554		*
555		* @param string $spec The table specification; "foo" or "foo AS bar".
556		*
557		* @param string $cond Join on this condition.
558		*
559		* @param array $bind Values to bind to ?-placeholders in the condition.
560		*
561		* @return $this
562		*
563		* @throws Exception
564		*
565		*/
566	10	public function leftJoin($spec, $cond = null, array $bind = array())
567		{
568	10	return $this->join('LEFT', $spec, $cond, $bind);
569		}
570
571		/**
572		*
573		* Adds a JOIN to an aliased subselect and columns to the query.
574		*
575		* @param string $join The join type: inner, left, natural, etc.
576		*
577		* @param string\|Select $spec If a Select
578		* object, use as the sub-select; if a string, the sub-select
579		* command string.
580		*
581		* @param string $name The alias name for the sub-select.
582		*
583		* @param string $cond Join on this condition.
584		*
585		* @param array $bind Values to bind to ?-placeholders in the condition.
586		*
587		* @return $this
588		*
589		* @throws Exception
590		*
591		*/
592	20	public function joinSubSelect($join, $spec, $name, $cond = null, array $bind = array())
593		{
594	20	$join = strtoupper(ltrim("$join JOIN"));
595	20	$this->addTableRef("$join (SELECT ...) AS", $name);
596
597	15	$spec = $this->subSelect($spec, ' ');
598	15	$name = $this->quoter->quoteName($name);
599	15	$cond = $this->fixJoinCondition($cond, $bind);
600
601	15	$text = rtrim("$join ($spec ) AS $name $cond");
602	15	return $this->addJoin(' ' . $text);
603		}
604
605		/**
606		*
607		* Adds the JOIN to the right place, given whether or not a FROM has been
608		* specified yet.
609		*
610		* @param string $spec The JOIN clause.
611		*
612		* @return $this
613		*
614		*/
615	55	protected function addJoin($spec)
616		{
617	55	$from_key = ($this->from_key == -1) ? 0 : $this->from_key;
618	55	$this->join[$from_key][] = $spec;
619	55	return $this;
620		}
621
622		/**
623		*
624		* Adds grouping to the query.
625		*
626		* @param array $spec The column(s) to group by.
627		*
628		* @return $this
629		*
630		*/
631	5	public function groupBy(array $spec)
632		{
633	5	foreach ($spec as $col) {
634	5	$this->group_by[] = $this->quoter->quoteNamesIn($col);
635	5	}
636	5	return $this;
637		}
638
639		/**
640		*
641		* Adds a HAVING condition to the query by AND.
642		*
643		* @param string $cond The HAVING condition.
644		*
645		* @param array $bind arguments to bind to placeholders
646		*
647		* @return $this
648		*
649		*/
650	10	public function having($cond, array $bind = [])
651		{
652	10	$this->addClauseCondWithBind('having', 'AND', $cond, $bind);
653	10	return $this;
654		}
655
656		/**
657		*
658		* Adds a HAVING condition to the query by OR.
659		*
660		* @param string $cond The HAVING condition.
661		*
662		* @param array $bind arguments to bind to placeholders
663		*
664		* @return $this
665		*
666		* @see having()
667		*
668		*/
669	5	public function orHaving($cond, array $bind = [])
670		{
671	5	$this->addClauseCondWithBind('having', 'OR', $cond, $bind);
672	5	return $this;
673		}
674
675		/**
676		*
677		* Sets the limit and count by page number.
678		*
679		* @param int $page Limit results to this page number.
680		*
681		* @return $this
682		*
683		*/
684	30	public function page($page)
685		{
686	30	$this->page = (int) $page;
687	30	$this->setPagingLimitOffset();
688	30	return $this;
689		}
690
691		/**
692		*
693		* Updates the limit and offset values when changing pagination.
694		*
695		* @return null
696		*
697		*/
698	30	protected function setPagingLimitOffset()
699		{
700	30	$this->setLimit(0);
701	30	$this->setOffset(0);
702	30	if ($this->page) {
703	10	$this->setLimit($this->paging);
704	10	$this->setOffset($this->paging * ($this->page - 1));
705	10	}
706	30	}
707
708		/**
709		*
710		* Returns the page number being selected.
711		*
712		* @return int
713		*
714		*/
715	5	public function getPage()
716		{
717	5	return $this->page;
718		}
719
720		/**
721		*
722		* Takes the current select properties and retains them, then sets
723		* UNION for the next set of properties.
724		*
725		* @return $this
726		*
727		*/
728	15	public function union()
729		{
730	15	$this->union[] = $this->build() . PHP_EOL . 'UNION';
731	15	$this->reset();
732	15	return $this;
733		}
734
735		/**
736		*
737		* Takes the current select properties and retains them, then sets
738		* UNION ALL for the next set of properties.
739		*
740		* @return $this
741		*
742		*/
743	5	public function unionAll()
744		{
745	5	$this->union[] = $this->build() . PHP_EOL . 'UNION ALL';
746	5	$this->reset();
747	5	return $this;
748		}
749
750		/**
751		*
752		* Clears the current select properties; generally used after adding a
753		* union.
754		*
755		* @return null
756		*
757		*/
758	20	public function reset()
759		{
760	20	$this->resetFlags();
761	20	$this->resetCols();
762	20	$this->resetTables();
763	20	$this->resetWhere();
764	20	$this->resetGroupBy();
765	20	$this->resetHaving();
766	20	$this->resetOrderBy();
767	20	$this->limit(0);
768	20	$this->offset(0);
769	20	$this->page(0);
770	20	$this->forUpdate(false);
771	20	}
772
773		/**
774		*
775		* Resets the columns on the SELECT.
776		*
777		* @return $this
778		*
779		*/
780	20	public function resetCols()
781		{
782	20	$this->cols = array();
783	20	return $this;
784		}
785
786		/**
787		*
788		* Resets the FROM and JOIN clauses on the SELECT.
789		*
790		* @return $this
791		*
792		*/
793	20	public function resetTables()
794		{
795	20	$this->from = array();
796	20	$this->from_key = -1;
797	20	$this->join = array();
798	20	$this->table_refs = array();
799	20	return $this;
800		}
801
802		/**
803		*
804		* Resets the WHERE clause on the SELECT.
805		*
806		* @return $this
807		*
808		*/
809	20	public function resetWhere()
810		{
811	20	$this->where = array();
812	20	return $this;
813		}
814
815		/**
816		*
817		* Resets the GROUP BY clause on the SELECT.
818		*
819		* @return $this
820		*
821		*/
822	20	public function resetGroupBy()
823		{
824	20	$this->group_by = array();
825	20	return $this;
826		}
827
828		/**
829		*
830		* Resets the HAVING clause on the SELECT.
831		*
832		* @return $this
833		*
834		*/
835	20	public function resetHaving()
836		{
837	20	$this->having = array();
838	20	return $this;
839		}
840
841		/**
842		*
843		* Resets the ORDER BY clause on the SELECT.
844		*
845		* @return $this
846		*
847		*/
848	20	public function resetOrderBy()
849		{
850	20	$this->order_by = array();
851	20	return $this;
852		}
853
854		/**
855		*
856		* Resets the UNION and UNION ALL clauses on the SELECT.
857		*
858		* @return $this
859		*
860		*/
861	5	public function resetUnions()
862		{
863	5	$this->union = array();
864	5	return $this;
865		}
866
867		/**
868		*
869		* Builds this query object into a string.
870		*
871		* @return string
872		*
873		*/
874	179	protected function build()
875		{
876	179	$cols = array();
877	179	foreach ($this->cols as $key => $val) {
878	174	if (is_int($key)) {
879	174	$cols[] = $this->quoter->quoteNamesIn($val);
880	174	} else {
881	10	$cols[] = $this->quoter->quoteNamesIn("$val AS $key");
882		}
883	179	}
884
885		return 'SELECT'
886	179	. $this->builder->buildFlags($this->flags)
		0 ignored issues – show Bug introduced 2017-03-17 16:53 UTC by Report Bug Copy Issue Report The property `builder` does not exist. Did you maybe forget to declare it? In PHP it is possible to write to properties without declaring them. For example, the following is perfectly valid PHP code: class MyClass { } $x = new MyClass(); $x->foo = true; Generally, it is a good practice to explictly declare properties to avoid accidental typos and provide IDE auto-completion: class MyClass { public $foo; } $x = new MyClass(); $x->foo = true; Loading history...
887	179	. $this->builder->buildCols($cols)
888	174	. $this->builder->buildFrom($this->from, $this->join)
889	174	. $this->builder->buildWhere($this->where)
890	174	. $this->builder->buildGroupBy($this->group_by)
891	174	. $this->builder->buildHaving($this->having)
892	174	. $this->builder->buildOrderBy($this->order_by)
893	174	. $this->builder->buildLimitOffset($this->limit, $this->offset)
894	174	. $this->builder->buildForUpdate($this->for_update);
895		}
896
897		/**
898		*
899		* Sets a limit count on the query.
900		*
901		* @param int $limit The number of rows to select.
902		*
903		* @return $this
904		*
905		*/
906	35	public function limit($limit)
907		{
908	35	$this->setLimit($limit);
909	35	if ($this->page) {
910	5	$this->page = 0;
911	5	$this->setOffset(0);
912	5	}
913	35	return $this;
914		}
915
916		/**
917		*
918		* Sets a limit offset on the query.
919		*
920		* @param int $offset Start returning after this many rows.
921		*
922		* @return $this
923		*
924		*/
925	35	public function offset($offset)
926		{
927	35	$this->setOffset($offset);
928	35	if ($this->page) {
929	5	$this->page = 0;
930	5	$this->setLimit(0);
931	5	}
932	35	return $this;
933		}
934
935		/**
936		*
937		* Adds a column order to the query.
938		*
939		* @param array $spec The columns and direction to order by.
940		*
941		* @return $this
942		*
943		*/
944	5	public function orderBy(array $spec)
945		{
946	5	return $this->addOrderBy($spec);
947		}
948		}
949

auraphp / Aura.SqlQuery

Push — 3.x-named-placeholders-only ( 6a3d98...913a85 )

Select::unionAll() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like