Select::orderBy() - Code Metrics - Inspection of "add missing public methods to SelectInterface and..." - auraphp/Aura.SqlQuery - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Pull Request — 3.x (#112)

unknown

created 2017-01-02 20:34 UTC

Select::orderBy() A

↳ Parent: Select

Complexity

Conditions	1
Paths	1

Size

Total Lines	4
Code Lines	2

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	2
CRAP Score	1

Importance

Changes

Metric	Value
dl	0
loc	4
c	0
b	0
f	0
ccs	2
cts	2
cp	1
rs	10
cc	1
eloc	2
nc	1
nop	1
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;
    }

    /**
     *
     * Is the select DISTINCT?
     *
     * @return bool
     *
     */
    public function isDistinct()
    {
        return $this->hasFlag('DISTINCT');
    }

    /**
     *
     * Adds columns to the query.
     *
     * Multiple calls to cols() will append to the list of columns, not
     * overwrite the previous columns.
     *
     * @param array $cols The column(s) to add to the query. The elements can be
     * any mix of these: `array("col", "col AS alias", "col" => "alias")`
     *
     * @return $this
     *
     */
    public function cols(array $cols)
    {
        foreach ($cols as $key => $val) {
            $this->addCol($key, $val);
        }
        return $this;
    }

    /**
     *
     * Adds a column and alias to the columns to be selected.
     *
     * @param mixed $key If an integer, ignored. Otherwise, the column to be
     * added.
     *
     * @param mixed $val If $key was an integer, the column to be added;
     * otherwise, the column alias.
     *
     * @return null
     *
     */
    protected function addCol($key, $val)
    {
        if (is_string($key)) {
            // [col => alias]
            $this->cols[$val] = $key;
        } else {
            $this->addColWithAlias($val);
        }
    }

    /**
     *
     * Adds a column with an alias to the columns to be selected.
     *
     * @param string $spec The column specification: "col alias",
     * "col AS alias", or something else entirely.
     *
     * @return null
     *
     */
    protected function addColWithAlias($spec)
    {
        $parts = explode(' ', $spec);
        $count = count($parts);
        if ($count == 2) {
            // "col alias"
            $this->cols[$parts[1]] = $parts[0];
        } elseif ($count == 3 && strtoupper($parts[1]) == 'AS') {
            // "col AS alias"
            $this->cols[$parts[2]] = $parts[0];
        } else {
            // no recognized alias
            $this->cols[] = $spec;
        }
    }

    /**
     *
     * Remove a column via its alias.
     *
     * @param string $alias The column to remove
     *
     * @return bool
     *
     */
    public function removeCol($alias)
    {
        if (isset($this->cols[$alias])) {
            unset($this->cols[$alias]);

            return true;
        }

        $index = array_search($alias, $this->cols);
        if ($index !== false) {
            unset($this->cols[$index]);
            return true;
        }

        return false;
    }

    /**
     *
     * Has the column or alias been added to the query?
     *
     * @param string $alias The column or alias to look for
     *
     * @return bool
     *
     */
    public function hasCol($alias)
    {
        return isset($this->cols[$alias]) || array_search($alias, $this->cols) !== false;
    }

    /**
     *
     * Does the query have any columns in it?
     *
     * @return bool
     *
     */
    public function hasCols()
    {
        return (bool) $this->cols;
    }

    /**
     *
     * Returns a list of columns.
     *
     * @return array
     *
     */
    public function getCols()
    {
        return $this->cols;
    }

    /**
     *
     * Tracks table references.
     *
     * @param string $type FROM, JOIN, etc.
     *
     * @param string $spec The table and alias name.
     *
     * @return null
     *
     * @throws Exception when the reference has already been used.
     *
     */
    protected function addTableRef($type, $spec)
    {
        $name = $spec;

        $pos = strripos($name, ' AS ');
        if ($pos !== false) {
            $name = trim(substr($name, $pos + 4));
        }

        if (isset($this->table_refs[$name])) {
            $used = $this->table_refs[$name];
            throw new Exception("Cannot reference '$type $spec' after '$used'");
        }

        $this->table_refs[$name] = "$type $spec";
    }

    /**
     *
     * Adds a FROM element to the query; quotes the table name automatically.
     *
     * @param string $spec The table specification; "foo" or "foo AS bar".
     *
     * @return $this
     *
     */
    public function from($spec)
    {
        $this->addTableRef('FROM', $spec);
        return $this->addFrom($this->quoter->quoteName($spec));
    }

    /**
     *
     * Adds a raw unquoted FROM element to the query; useful for adding FROM
     * elements that are functions.
     *
     * @param string $spec The table specification, e.g. "function_name()".
     *
     * @return $this
     *
     */
    public function fromRaw($spec)
    {
        $this->addTableRef('FROM', $spec);
        return $this->addFrom($spec);
    }

    /**
     *
     * Adds to the $from property and increments the key count.
     *
     * @param string $spec The table specification.
     *
     * @return $this
     *
     */
    protected function addFrom($spec)
    {
        $this->from[] = array($spec);
        $this->from_key ++;
        return $this;
    }

    /**
     *
     * Adds an aliased sub-select to the query.
     *
     * @param string|Select $spec If a Select object, use as the sub-select;
     * if a string, the sub-select string.
     *
     * @param string $name The alias name for the sub-select.
     *
     * @return $this
     *
     */
    public function fromSubSelect($spec, $name)
    {
        $this->addTableRef('FROM (SELECT ...) AS', $name);
        $spec = $this->subSelect($spec, '        ');
        $name = $this->quoter->quoteName($name);
        return $this->addFrom("({$spec}    ) AS $name");
    }

    /**
     *
     * Formats a sub-SELECT statement, binding values from a Select object as
     * needed.
     *
     * @param string|SelectInterface $spec A sub-SELECT specification.
     *
     * @param string $indent Indent each line with this string.
     *
     * @return string The sub-SELECT string.
     *
     */
    protected function subSelect($spec, $indent)
    {
        if ($spec instanceof SelectInterface) {
            $this->bindValues($spec->getBindValues());
        }

        return PHP_EOL . $indent
            . ltrim(preg_replace('/^/m', $indent, (string) $spec))
            . PHP_EOL;
    }

    /**
     *
     * Adds a JOIN table and columns to the query.
     *
     * @param string $join The join type: inner, left, natural, etc.
     *
     * @param string $spec The table specification; "foo" or "foo AS bar".
     *
     * @param string $cond Join on this condition.
     *
     * @param array $bind Values to bind to ?-placeholders in the condition.
     *
     * @return $this
     *
     * @throws Exception
     *
     */
    public function join($join, $spec, $cond = null, array $bind = array())
    {
        $join = strtoupper(ltrim("$join JOIN"));
        $this->addTableRef($join, $spec);

        $spec = $this->quoter->quoteName($spec);
        $cond = $this->fixJoinCondition($cond, $bind);
        return $this->addJoin(rtrim("$join $spec $cond"));
    }

    /**
     *
     * Fixes a JOIN condition to quote names in the condition and prefix it
     * with a condition type ('ON' is the default and 'USING' is recognized).
     *
     * @param string $cond Join on this condition.
     *
     * @param array $bind Values to bind to ?-placeholders in the condition.
     *
     * @return string
     *
     */
    protected function fixJoinCondition($cond, array $bind)
    {
        if (! $cond) {
            return '';
        }

        $cond = $this->quoter->quoteNamesIn($cond);
        $cond = $this->rebuildCondAndBindValues($cond, $bind);

        if (strtoupper(substr(ltrim($cond), 0, 3)) == 'ON ') {
            return $cond;
        }

        if (strtoupper(substr(ltrim($cond), 0, 6)) == 'USING ') {
            return $cond;
        }

        return 'ON ' . $cond;
    }

    /**
     *
     * Adds a INNER JOIN table and columns to the query.
     *
     * @param string $spec The table specification; "foo" or "foo AS bar".
     *
     * @param string $cond Join on this condition.
     *
     * @param array $bind Values to bind to ?-placeholders in the condition.
     *
     * @return $this
     *
     * @throws Exception
     *
     */
    public function innerJoin($spec, $cond = null, array $bind = array())
    {
        return $this->join('INNER', $spec, $cond, $bind);
    }

    /**
     *
     * Adds a LEFT JOIN table and columns to the query.
     *
     * @param string $spec The table specification; "foo" or "foo AS bar".
     *
     * @param string $cond Join on this condition.
     *
     * @param array $bind Values to bind to ?-placeholders in the condition.
     *
     * @return $this
     *
     * @throws Exception
     *
     */
    public function leftJoin($spec, $cond = null, array $bind = array())
    {
        return $this->join('LEFT', $spec, $cond, $bind);
    }

    /**
     *
     * Adds a JOIN to an aliased subselect and columns to the query.
     *
     * @param string $join The join type: inner, left, natural, etc.
     *
     * @param string|Select $spec If a Select
     * object, use as the sub-select; if a string, the sub-select
     * command string.
     *
     * @param string $name The alias name for the sub-select.
     *
     * @param string $cond Join on this condition.
     *
     * @param array $bind Values to bind to ?-placeholders in the condition.
     *
     * @return $this
     *
     * @throws Exception
     *
     */
    public function joinSubSelect($join, $spec, $name, $cond = null, array $bind = array())
    {
        $join = strtoupper(ltrim("$join JOIN"));
        $this->addTableRef("$join (SELECT ...) AS", $name);

        $spec = $this->subSelect($spec, '            ');
        $name = $this->quoter->quoteName($name);
        $cond = $this->fixJoinCondition($cond, $bind);

        $text = rtrim("$join ($spec        ) AS $name $cond");
        return $this->addJoin('        ' . $text);
    }

    /**
     *
     * Adds the JOIN to the right place, given whether or not a FROM has been
     * specified yet.
     *
     * @param string $spec The JOIN clause.
     *
     * @return $this
     *
     */
    protected function addJoin($spec)
    {
        $from_key = ($this->from_key == -1) ? 0 : $this->from_key;
        $this->join[$from_key][] = $spec;
        return $this;
    }

    /**
     *
     * Adds grouping to the query.
     *
     * @param array $spec The column(s) to group by.
     *
     * @return $this
     *
     */
    public function groupBy(array $spec)
    {
        foreach ($spec as $col) {
            $this->group_by[] = $this->quoter->quoteNamesIn($col);
        }
        return $this;
    }

    /**
     *
     * Adds a HAVING condition to the query by AND. If the condition has
     * ?-placeholders, additional arguments to the method will be bound to
     * those placeholders sequentially.
     *
     * @param string $cond The HAVING condition.
     *
     * @param array ...$bind arguments to bind to placeholders
     *
     * @return $this
     *
     */
    public function having($cond, ...$bind)
    {
        $this->addClauseCondWithBind('having', 'AND', $cond, $bind);
        return $this;
    }

    /**
     *
     * Adds a HAVING condition to the query by AND. If the condition has
     * ?-placeholders, additional arguments to the method will be bound to
     * those placeholders sequentially.
     *
     * @param string $cond The HAVING condition.
     *
     * @param array ...$bind arguments to bind to placeholders
     *
     * @return $this
     *
     * @see having()
     *
     */
    public function orHaving($cond, ...$bind)
    {
        $this->addClauseCondWithBind('having', 'OR', $cond, $bind);
        return $this;
    }

    /**
     *
     * Sets the limit and count by page number.
     *
     * @param int $page Limit results to this page number.
     *
     * @return $this
     *
     */
    public function page($page)
    {
        $this->page = (int) $page;
        $this->setPagingLimitOffset();
        return $this;
    }

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

auraphp / Aura.SqlQuery

Pull Request — 3.x (#112)

Select::orderBy() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like