Select::fixJoinCondition() - Code Metrics - Inspection of "add back subselects as join conditions" - auraphp/Aura.SqlQuery - Measure and Improve Code Quality continuously with Scrutinizer

Completed

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

by Paul

created 2017-03-21 20:43 UTC

Select::fixJoinCondition() A

↳ Parent: Select

Complexity

Conditions	4
Paths	4

Size

Total Lines	19
Code Lines	10

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	10
CRAP Score	4

Importance

Changes

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

<?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->fixCondWithBind($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.
     *
     * @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	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->fixCondWithBind($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.
639		*
640		* @param string $cond The HAVING condition.
641		*
642		* @param array $bind arguments to bind to placeholders
643		*
644		* @return $this
645		*
646		*/
647	10	public function having($cond, array $bind = [])
648		{
649	10	$this->addClauseCondWithBind('having', 'AND', $cond, $bind);
650	10	return $this;
651		}
652
653		/**
654		*
655		* Adds a HAVING condition to the query by OR.
656		*
657		* @param string $cond The HAVING condition.
658		*
659		* @param array $bind arguments to bind to placeholders
660		*
661		* @return $this
662		*
663		* @see having()
664		*
665		*/
666	5	public function orHaving($cond, array $bind = [])
667		{
668	5	$this->addClauseCondWithBind('having', 'OR', $cond, $bind);
669	5	return $this;
670		}
671
672		/**
673		*
674		* Sets the limit and count by page number.
675		*
676		* @param int $page Limit results to this page number.
677		*
678		* @return $this
679		*
680		*/
681	30	public function page($page)
682		{
683	30	$this->page = (int) $page;
684	30	$this->setPagingLimitOffset();
685	30	return $this;
686		}
687
688		/**
689		*
690		* Updates the limit and offset values when changing pagination.
691		*
692		* @return null
693		*
694		*/
695	30	protected function setPagingLimitOffset()
696		{
697	30	$this->setLimit(0);
698	30	$this->setOffset(0);
699	30	if ($this->page) {
700	10	$this->setLimit($this->paging);
701	10	$this->setOffset($this->paging * ($this->page - 1));
702	10	}
703	30	}
704
705		/**
706		*
707		* Returns the page number being selected.
708		*
709		* @return int
710		*
711		*/
712	5	public function getPage()
713		{
714	5	return $this->page;
715		}
716
717		/**
718		*
719		* Takes the current select properties and retains them, then sets
720		* UNION for the next set of properties.
721		*
722		* @return $this
723		*
724		*/
725	15	public function union()
726		{
727	15	$this->union[] = $this->build() . PHP_EOL . 'UNION';
728	15	$this->reset();
729	15	return $this;
730		}
731
732		/**
733		*
734		* Takes the current select properties and retains them, then sets
735		* UNION ALL for the next set of properties.
736		*
737		* @return $this
738		*
739		*/
740	5	public function unionAll()
741		{
742	5	$this->union[] = $this->build() . PHP_EOL . 'UNION ALL';
743	5	$this->reset();
744	5	return $this;
745		}
746
747		/**
748		*
749		* Clears the current select properties; generally used after adding a
750		* union.
751		*
752		* @return null
753		*
754		*/
755	20	public function reset()
756		{
757	20	$this->resetFlags();
758	20	$this->resetCols();
759	20	$this->resetTables();
760	20	$this->resetWhere();
761	20	$this->resetGroupBy();
762	20	$this->resetHaving();
763	20	$this->resetOrderBy();
764	20	$this->limit(0);
765	20	$this->offset(0);
766	20	$this->page(0);
767	20	$this->forUpdate(false);
768	20	}
769
770		/**
771		*
772		* Resets the columns on the SELECT.
773		*
774		* @return $this
775		*
776		*/
777	20	public function resetCols()
778		{
779	20	$this->cols = array();
780	20	return $this;
781		}
782
783		/**
784		*
785		* Resets the FROM and JOIN clauses on the SELECT.
786		*
787		* @return $this
788		*
789		*/
790	20	public function resetTables()
791		{
792	20	$this->from = array();
793	20	$this->from_key = -1;
794	20	$this->join = array();
795	20	$this->table_refs = array();
796	20	return $this;
797		}
798
799		/**
800		*
801		* Resets the WHERE clause on the SELECT.
802		*
803		* @return $this
804		*
805		*/
806	20	public function resetWhere()
807		{
808	20	$this->where = array();
809	20	return $this;
810		}
811
812		/**
813		*
814		* Resets the GROUP BY clause on the SELECT.
815		*
816		* @return $this
817		*
818		*/
819	20	public function resetGroupBy()
820		{
821	20	$this->group_by = array();
822	20	return $this;
823		}
824
825		/**
826		*
827		* Resets the HAVING clause on the SELECT.
828		*
829		* @return $this
830		*
831		*/
832	20	public function resetHaving()
833		{
834	20	$this->having = array();
835	20	return $this;
836		}
837
838		/**
839		*
840		* Resets the ORDER BY clause on the SELECT.
841		*
842		* @return $this
843		*
844		*/
845	20	public function resetOrderBy()
846		{
847	20	$this->order_by = array();
848	20	return $this;
849		}
850
851		/**
852		*
853		* Resets the UNION and UNION ALL clauses on the SELECT.
854		*
855		* @return $this
856		*
857		*/
858	5	public function resetUnions()
859		{
860	5	$this->union = array();
861	5	return $this;
862		}
863
864		/**
865		*
866		* Builds this query object into a string.
867		*
868		* @return string
869		*
870		*/
871	189	protected function build()
872		{
873	189	$cols = array();
874	189	foreach ($this->cols as $key => $val) {
875	184	if (is_int($key)) {
876	184	$cols[] = $this->quoter->quoteNamesIn($val);
877	184	} else {
878	10	$cols[] = $this->quoter->quoteNamesIn("$val AS $key");
879		}
880	189	}
881
882		return 'SELECT'
883	189	. $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...
884	189	. $this->builder->buildCols($cols)
885	184	. $this->builder->buildFrom($this->from, $this->join)
886	184	. $this->builder->buildWhere($this->where)
887	184	. $this->builder->buildGroupBy($this->group_by)
888	184	. $this->builder->buildHaving($this->having)
889	184	. $this->builder->buildOrderBy($this->order_by)
890	184	. $this->builder->buildLimitOffset($this->limit, $this->offset)
891	184	. $this->builder->buildForUpdate($this->for_update);
892		}
893
894		/**
895		*
896		* Sets a limit count on the query.
897		*
898		* @param int $limit The number of rows to select.
899		*
900		* @return $this
901		*
902		*/
903	35	public function limit($limit)
904		{
905	35	$this->setLimit($limit);
906	35	if ($this->page) {
907	5	$this->page = 0;
908	5	$this->setOffset(0);
909	5	}
910	35	return $this;
911		}
912
913		/**
914		*
915		* Sets a limit offset on the query.
916		*
917		* @param int $offset Start returning after this many rows.
918		*
919		* @return $this
920		*
921		*/
922	35	public function offset($offset)
923		{
924	35	$this->setOffset($offset);
925	35	if ($this->page) {
926	5	$this->page = 0;
927	5	$this->setLimit(0);
928	5	}
929	35	return $this;
930		}
931
932		/**
933		*
934		* Adds a column order to the query.
935		*
936		* @param array $spec The columns and direction to order by.
937		*
938		* @return $this
939		*
940		*/
941	5	public function orderBy(array $spec)
942		{
943	5	return $this->addOrderBy($spec);
944		}
945		}
946

auraphp / Aura.SqlQuery

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

Select::fixJoinCondition() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like