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

Completed

Push — 3.x ( f5caca...8b4f6e )

by Hari

created 2017-01-02 17:47 UTC

Select::orWhere() A

↳ Parent: Select::offset()

Complexity

Conditions	1
Paths	1

Size

Total Lines	5
Code Lines	3

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	3
CRAP Score	1

Importance

Changes

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

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

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

/**
 *
 * An object for SELECT queries.
 *
 * @package Aura.SqlQuery
 *
 */
class Select extends AbstractQuery implements SelectInterface, SubselectInterface
{
    use WhereTrait;

    /**
     *
     * 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->limit  = 0;
        $this->offset = 0;
        if ($this->page) {
            $this->limit  = $this->paging;
            $this->offset = $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;
    }

    /**
     *
     * Returns the LIMIT value.
     *
     * @return int
     *
     */
    public function getLimit()
    {
        return $this->limit;
    }

    /**
     *
     * Returns the OFFSET value.
     *
     * @return int
     *
     */
    public function getOffset()
    {
        return $this->offset;
    }

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

auraphp / Aura.SqlQuery

Push — 3.x ( f5caca...8b4f6e )

Select::orWhere() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like