Select::fixJoinCondition() - Code Metrics - Inspection of "move copy/pasted limit()/offset() code to traits" - auraphp/Aura.SqlQuery - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Pull Request — 3.x (#120)

unknown

created 2017-01-02 21:50 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;
    }

    /**
     *
     * 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;
    }

    /**
     *
     * 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
     *
     */
    protected function reset()
    {
        $this->resetFlags();
        $this->resetCols();
        $this->resetTables();
        $this->resetWhere();
        $this->resetGroupBy();
        $this->resetHaving();
        $this->resetOrderBy();
        $this->limit(0);
        $this->offset(0);
        $this->page(0);
        $this->forUpdate(false);
    }

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

        return PHP_EOL . 'FOR UPDATE';
    }

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

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

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


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

auraphp / Aura.SqlQuery

Pull Request — 3.x (#120)

Select::fixJoinCondition() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like