Select::addJoin() - Code Metrics - Inspection of "Extract builder logic to separate classes" - auraphp/Aura.SqlQuery - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Pull Request — 3.x (#131)

by Paul

created 2017-03-18 15:09 UTC

Select::addJoin() A

↳ Parent: Select

Complexity

Conditions	2
Paths	2

Size

Total Lines	6
Code Lines	4

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	4
CRAP Score	2

Importance

Changes

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

<?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()
    {
        $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	164	public function getStatement()
133		{
134	164	$union = '';
135	164	if (! empty($this->union)) {
136		$union = implode(PHP_EOL, $this->union) . PHP_EOL;
137		}
138	5	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		$this->setPagingLimitOffset();
155		}
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	5	public function getPaging()
167		{
168	5	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	5	}
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	11	public function distinct($enable = true)
198		{
199		$this->setFlag('DISTINCT', $enable);
200	6	return $this;
201	11	}
202
203		/**
204		*
205		* Is the select DISTINCT?
206		*
207		* @return bool
208		*
209		*/
210		public function isDistinct()
211		{
212		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	184	public function cols(array $cols)
229		{
230		foreach ($cols as $key => $val) {
231		$this->addCol($key, $val);
232		}
233	184	return $this;
234	184	}
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	145	protected function addCol($key, $val)
250		{
251		if (is_string($key)) {
252		// [col => alias]
253	20	$this->cols[$val] = $key;
254		} else {
255		$this->addColWithAlias($val);
256	20	}
257	145	}
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	145	protected function addColWithAlias($spec)
270		{
271		$parts = explode(' ', $spec);
272		$count = count($parts);
273	145	if ($count == 2) {
274		// "col alias"
275	5	$this->cols[$parts[1]] = $parts[0];
276	145	} elseif ($count == 3 && strtoupper($parts[1]) == 'AS') {
277		// "col AS alias"
278		$this->cols[$parts[2]] = $parts[0];
279		} else {
280		// no recognized alias
281	145	$this->cols[] = $spec;
282		}
283	145	}
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		$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		public function hasCol($alias)
321		{
322		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		public function hasCols()
333		{
334		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	59	protected function addTableRef($type, $spec)
363		{
364	34	$name = $spec;
365
366		$pos = strripos($name, ' AS ');
367	34	if ($pos !== false) {
368		$name = trim(substr($name, $pos + 4));
369		}
370
371	34	if (isset($this->table_refs[$name])) {
372	25	$used = $this->table_refs[$name];
373		throw new Exception("Cannot reference '$type $spec' after '$used'");
374		}
375
376	59	$this->table_refs[$name] = "$type $spec";
377	59	}
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		public function from($spec)
389		{
390		$this->addTableRef('FROM', $spec);
391		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		public function fromRaw($spec)
405		{
406		$this->addTableRef('FROM', $spec);
407		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	89	protected function addFrom($spec)
420		{
421	89	$this->from[] = array($spec);
422	89	$this->from_key ++;
423	89	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	5	public function fromSubSelect($spec, $name)
439		{
440	5	$this->addTableRef('FROM (SELECT ...) AS', $name);
441		$spec = $this->subSelect($spec, ' ');
442		$name = $this->quoter->quoteName($name);
443		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	15	protected function subSelect($spec, $indent)
459		{
460	15	if ($spec instanceof SelectInterface) {
461		$this->bindValues($spec->getBindValues());
462		}
463
464	15	return PHP_EOL . $indent
465		. ltrim(preg_replace('/^/m', $indent, (string) $spec))
466	15	. 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	5	public function join($join, $spec, $cond = null, array $bind = array())
487		{
488		$join = strtoupper(ltrim("$join JOIN"));
489	5	$this->addTableRef($join, $spec);
490
491		$spec = $this->quoter->quoteName($spec);
492		$cond = $this->fixJoinCondition($cond, $bind);
493		return $this->addJoin(rtrim("$join $spec $cond"));
494	5	}
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	25	protected function fixJoinCondition($cond, array $bind)
509		{
510	10	if (! $cond) {
511	25	return '';
512		}
513
514		$cond = $this->quoter->quoteNamesIn($cond);
515		$cond = $this->rebuildCondAndBindValues($cond, $bind);
516
517		if (strtoupper(substr(ltrim($cond), 0, 3)) == 'ON ') {
518	5	return $cond;
519		}
520
521		if (strtoupper(substr(ltrim($cond), 0, 6)) == 'USING ') {
522	5	return $cond;
523		}
524
525	20	return 'ON ' . $cond;
526	10	}
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		return $this->join('INNER', $spec, $cond, $bind);
546	10	}
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		return $this->join('LEFT', $spec, $cond, $bind);
566	10	}
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	5	public function joinSubSelect($join, $spec, $name, $cond = null, array $bind = array())
590		{
591		$join = strtoupper(ltrim("$join JOIN"));
592	5	$this->addTableRef("$join (SELECT ...) AS", $name);
593
594		$spec = $this->subSelect($spec, ' ');
595		$name = $this->quoter->quoteName($name);
596		$cond = $this->fixJoinCondition($cond, $bind);
597
598		$text = rtrim("$join ($spec ) AS $name $cond");
599		return $this->addJoin(' ' . $text);
600	5	}
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	10	protected function addJoin($spec)
613		{
614	10	$from_key = ($this->from_key == -1) ? 0 : $this->from_key;
615	10	$this->join[$from_key][] = $spec;
616	10	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		foreach ($spec as $col) {
631		$this->group_by[] = $this->quoter->quoteNamesIn($col);
632		}
633	5	return $this;
634	5	}
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	5	public function having($cond, ...$bind)
650		{
651		$this->addClauseCondWithBind('having', 'AND', $cond, $bind);
652	5	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		public function orHaving($cond, ...$bind)
671		{
672		$this->addClauseCondWithBind('having', 'OR', $cond, $bind);
673		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	25	public function page($page)
686		{
687	25	$this->page = (int) $page;
688		$this->setPagingLimitOffset();
689	25	return $this;
690		}
691
692		/**
693		*
694		* Updates the limit and offset values when changing pagination.
695		*
696		* @return null
697		*
698		*/
699	25	protected function setPagingLimitOffset()
700		{
701		$this->setLimit(0);
702		$this->setOffset(0);
703	25	if ($this->page) {
704		$this->setLimit($this->paging);
705		$this->setOffset($this->paging * ($this->page - 1));
706		}
707	25	}
708
709		/**
710		*
711		* Returns the page number being selected.
712		*
713		* @return int
714		*
715		*/
716		public function getPage()
717		{
718		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		$this->union[] = $this->build() . PHP_EOL . 'UNION';
732		$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		$this->union[] = $this->build() . PHP_EOL . 'UNION ALL';
747		$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		$this->resetFlags();
762		$this->resetCols();
763		$this->resetTables();
764		$this->resetWhere();
765		$this->resetGroupBy();
766		$this->resetHaving();
767		$this->resetOrderBy();
768		$this->limit(0);
769		$this->offset(0);
770		$this->page(0);
771		$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	144	protected function build()
876		{
877	144	$cols = array();
878		foreach ($this->cols as $key => $val) {
879	5	if (is_int($key)) {
880		$cols[] = $this->quoter->quoteNamesIn($val);
881		} else {
882		$cols[] = $this->quoter->quoteNamesIn("$val AS $key");
883		}
884		}
885
886		return 'SELECT'
887		. $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...
888		. $this->builder->buildCols($cols)
889		. $this->builder->buildFrom($this->from, $this->join)
890		. $this->builder->buildWhere($this->where)
891		. $this->builder->buildGroupBy($this->group_by)
892		. $this->builder->buildHaving($this->having)
893		. $this->builder->buildOrderBy($this->order_by)
894		. $this->builder->buildLimitOffset($this->limit, $this->offset)
895		. $this->builder->buildForUpdate($this->for_update);
896		}
897
898	144	/**
899		*
900	144	* Sets a limit count on the query.
901		*
902		* @param int $limit The number of rows to select.
903		*
904	139	* @return $this
905	139	*
906		*/
907		public function limit($limit)
908		{
909		$this->setLimit($limit);
910	105	if ($this->page) {
911		$this->page = 0;
912		$this->setOffset(0);
913		}
914		return $this;
915		}
916
917		/**
918		*
919		* Sets a limit offset on the query.
920		*
921		* @param int $offset Start returning after this many rows.
922		*
923	139	* @return $this
924		*
925	139	*/
926	55	public function offset($offset)
927		{
928		$this->setOffset($offset);
929	84	if ($this->page) {
930	84	$this->page = 0;
931	59	$this->setLimit(0);
932		}
933		return $this;
934		}
935
936		/**
937		*
938		* Adds a column order to the query.
939		*
940		* @param array $spec The columns and direction to order by.
941		*
942		* @return $this
943		*
944		*/
945		public function orderBy(array $spec)
946	139	{
947		return $this->addOrderBy($spec);
948	139	}
949		}
950

auraphp / Aura.SqlQuery

Pull Request — 3.x (#131)

Select::addJoin() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like