Select::union() - Code Metrics - Inspection of "Select: fixed aliasing, renamed "references" to "r..." - hfw/db - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — master ( 4a3939...6f91f6 )

by y

created 2019-04-04 19:51 UTC

Select::union() A

↳ Parent: Project

Complexity

Conditions	2
Paths	2

Size

Total Lines	11
Code Lines	8

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
eloc	8
dl	0
loc	11
rs	10
c	0
b	0
f	0
cc	2
nc	2
nop	2

<?php

namespace Helix\DB;

use Closure;
use Countable;
use Generator;
use Helix\DB;
use Helix\DB\SQL\ExpressionInterface;
use Helix\DB\SQL\Predicate;
use IteratorAggregate;

/**
 * Represents a `SELECT` query.
 */
class Select extends AbstractTable implements Countable, IteratorAggregate, ExpressionInterface {

    /**
     * Compiled column list.
     *
     * @internal
     * @var string
     */
    protected $_columns = '';

    /**
     * Compiled column list.
     *
     * @internal
     * @var string
     */
    protected $_group = '';

    /**
     * Compiled predicates.
     *
     * @internal
     * @var string
     */
    protected $_having = '';

    /**
     * Compiled unions and intersections.
     *
     * @internal
     * @var string
     */
    protected $_import = '';

    /**
     * Compiled joins.
     *
     * @internal
     * @var string
     */
    protected $_join = '';

    /**
     * Compiled limit and offset.
     *
     * @internal
     * @var string
     */
    protected $_limit = '';

    /**
     * Compiled column list.
     *
     * @internal
     * @var string
     */
    protected $_order = '';

    /**
     * Compiled predicates.
     *
     * @internal
     * @var string
     */
    protected $_where = '';

    /**
     * Human-readable alias.
     * This is initialized using `uniqid()` and the table's name.
     *
     * @var string
     */
    protected $alias;

    /**
     * A callback to yield each result.
     * Defaults to yielding directly from the statement.
     *
     * @var Closure `(Statement $statement):Generator`
     */
    protected $fetcher;

    /**
     * Columns that can be accessed by an outer query.
     *
     * @var Column[]
     */
    protected $refs = [];

    /**
     * @var string
     */
    protected $table;

    /**
     * @param DB $db
     * @param string|Select $table
     * @param string[] $columns
     */
    public function __construct (DB $db, $table, array $columns) {
        parent::__construct($db);
        if ($table instanceof Select) {
            $this->table = $table->toSubquery();
            $this->alias = uniqid('_') . "_{$table->alias}";
        }
        else {
            $this->table = (string)$table;
            $this->alias = uniqid('_') . "__{$table}";
        }
        $this->setColumns($columns);
        $this->fetcher = function(Statement $statement) {
            yield from $statement;
        };
    }

    /**
     * @param array $args
     * @return Statement
     */
    public function __invoke (array $args = []) {
        return $this->execute($args);
    }

    /**
     * Returns the alias.
     *
     * @return string
     */
    final public function __toString () {
        return $this->alias;
    }

    /**
     * Clones the instance and selects `COUNT(*)`, using the given execution arguments.
     *
     * @param array $args Execution arguments.
     * @return int
     */
    public function count (array $args = []): int {
        $clone = clone $this;
        $clone->_columns = 'COUNT(*)';
        $clone->_order = '';
        return (int)$clone->execute($args)->fetchColumn();
    }

    /**
     * Executes the select, preparing a statement first if arguments are used.
     *
     * @param array $args
     * @return Statement
     */
    public function execute (array $args = []) {
        if (empty($args)) {
            return $this->db->query($this->toSql());
        }
        return $this->prepare()->__invoke($args);
    }

    /**
     * Executes and fetches all results.
     *
     * @see fetcher
     *
     * @param array $args Execution arguments.
     * @return array
     */
    public function getAll (array $args = []): array {
        return iterator_to_array($this->fetcher->__invoke($this->execute($args)));
    }

    /**
     * Executes and yields from the fetcher.
     * This is preferable over `fetchAll()` for iterating large result sets.
     *
     * @see fetcher
     *
     * @param array $args Execution arguments.
     * @return Generator
     */
    public function getEach (array $args = []) {
        yield from $this->fetcher->__invoke($this->execute($args));
    }

    /**
     * Executes and returns from the fetcher.
     *
     * @see fetcher
     *
     * @param array $args
     * @return mixed
     */
    public function getFirst (array $args = []) {
        return $this->getEach($args)->current();
    }

    /**
     * Executes without arguments and yields from the fetcher.
     *
     * @see fetcher
     *
     * @return Generator
     */
    public function getIterator () {
        yield from $this->getEach();
    }

    /**
     * Adds a column to the `GROUP BY` clause.
     *
     * @param string $column
     * @return $this
     */
    public function group (string $column) {
        if (!strlen($this->_group)) {
            $this->_group = " GROUP BY {$column}";
        }
        else {
            $this->_group .= ", {$column}";
        }
        return $this;
    }

    /**
     * Adds a condition to the `HAVING` clause.
     *
     * @param string $condition
     * @return $this
     */
    public function having (string $condition) {
        if (!strlen($this->_having)) {
            $this->_having = " HAVING {$condition}";
        }
        else {
            $this->_having .= " AND {$condition}";
        }
        return $this;
    }

    /**
     * `INTERSECT` or `INTERSECT ALL`
     *
     * @param Select $select
     * @param bool $all
     * @return $this
     */
    public function intersect (Select $select, $all = false) {
        $select = clone $select;
        $select->_order = '';
        $select->_limit = '';
        if ($all) {
            $this->_import .= " INTERSECT ALL {$select->toSql()}";
        }
        else {
            $this->_import .= " INTERSECT {$select->toSql()}";
        }
        return $this;
    }

    /**
     * `NOT EXISTS (SELECT ...)`
     *
     * @return Predicate
     */
    public function isEmpty () {
        return new Predicate("NOT EXISTS ({$this->toSql()})");
    }

    /**
     * `EXISTS (SELECT ...)`
     *
     * @return Predicate
     */
    public function isNotEmpty () {
        return new Predicate("EXISTS ({$this->toSql()})");
    }

    /**
     * Adds a `JOIN` clause.
     *
     * @param string|Select $table
     * @param string $condition
     * @param string $type
     * @return $this
     */
    public function join ($table, string $condition, string $type = 'INNER') {
        if ($table instanceof Select) {
            $table = $table->toSubquery();
        }
        $this->_join .= " {$type} JOIN {$table} ON {$condition}";
        return $this;
    }

    /**
     * Sets the `LIMIT` clause.
     *
     * @param int $limit
     * @param int $offset
     * @return $this
     */
    public function limit (int $limit, int $offset = 0) {
        if ($limit == 0) {
            $this->_limit = '';
        }
        else {
            $this->_limit = " LIMIT {$limit}";
            if ($offset > 1) {
                $this->_limit .= " OFFSET {$offset}";
            }
        }
        return $this;
    }

    /**
     * Whether a selected column can be referenced by an outer query.
     *
     * @param mixed $offset Ordinal or reference name.
     * @return bool
     */
    public function offsetExists ($offset): bool {
        return isset($this->refs[$offset]);
    }

    /**
     * Returns a reference {@link Column}, qualified by the instance's alias.
     *
     * @param mixed $offset Ordinal or reference name.
     * @return Column
     */
    public function offsetGet ($offset) {
        return $this->refs[$offset];
    }

    /**
     * Sets the `ORDER BY` clause.
     *
     * @param string $order
     * @return $this
     */
    public function order (string $order) {
        if (strlen($order)) {
            $order = " ORDER BY {$order}";
        }
        $this->_order = $order;
        return $this;
    }

    /**
     * @return Statement
     */
    public function prepare () {
        return $this->db->prepare($this->toSql());
    }

    /**
     * @param string $alias
     * @return $this
     */
    public function setAlias (string $alias) {
        $this->alias = $alias;
        return $this;
    }

    /**
     * Compiles the column list and exposed references.
     *
     * @param string[] $columns
     * @return $this
     */
    public function setColumns (array $columns) {
        $this->refs = [];
        $_columns = [];
        $i = 0;
        foreach ($columns as $alias => $column) {
            preg_match('/^([a-z_][a-z0-9_]+\.)?(?<name>[a-z_][a-z0-9_]+)$/i', $column, $match);
            $name = $match['name'] ?? null;
            if (is_int($alias)) {
                $alias = $name;
            }
            if (isset($alias)) {
                $ref = new Column($this->db, $alias, $this->alias);
                $this->refs[$alias] = $ref;
                $this->refs[$i] = $ref;
                if ($alias !== $name) {
                    $column = "{$column} AS {$alias}";
                }
            }
            $_columns[] = "{$column}";
            $i++;
        }
        $this->_columns = implode(', ', $_columns);
        return $this;
    }

    /**
     * @param Closure $fetcher
     * @return $this
     */
    public function setFetcher (Closure $fetcher) {
        $this->fetcher = $fetcher;
        return $this;
    }

    /**
     * `SELECT ...`
     *
     * @return string
     */
    public function toSql (): string {
        $sql = "SELECT {$this->_columns} FROM {$this->table}";
        $sql .= $this->_join;
        $sql .= $this->_where;
        $sql .= $this->_group;
        $sql .= $this->_having;
        $sql .= $this->_import;
        $sql .= $this->_order;
        $sql .= $this->_limit;
        return $sql;
    }

    /**
     * `(SELECT ...) AS ALIAS`
     *
     * @return string
     */
    public function toSubquery (): string {
        return "({$this->toSql()}) AS {$this->alias}";
    }

    /**
     * `UNION` or `UNION ALL`
     *
     * @param Select $select
     * @param bool $all
     * @return $this
     */
    public function union (Select $select, $all = false) {
        $select = clone $select;
        $select->_order = '';
        $select->_limit = '';
        if ($all) {
            $this->_import .= " UNION ALL {$select->toSql()}";
        }
        else {
            $this->_import .= " UNION {$select->toSql()}";
        }
        return $this;
    }

    /**
     * Adds a condition to the `WHERE` clause.
     *
     * @param string $condition
     * @return $this
     */
    public function where (string $condition) {
        if (!strlen($this->_where)) {
            $this->_where = " WHERE {$condition}";
        }
        else {
            $this->_where .= " AND {$condition}";
        }
        return $this;
    }
}

1			<?php
2
3			namespace Helix\DB;
4
5			use Closure;
6			use Countable;
7			use Generator;
8			use Helix\DB;
9			use Helix\DB\SQL\ExpressionInterface;
10			use Helix\DB\SQL\Predicate;
11			use IteratorAggregate;
12
13			/**
14			* Represents a `SELECT` query.
15			*/
16			class Select extends AbstractTable implements Countable, IteratorAggregate, ExpressionInterface {
17
18			/**
19			* Compiled column list.
20			*
21			* @internal
22			* @var string
23			*/
24			protected $_columns = '';
25
26			/**
27			* Compiled column list.
28			*
29			* @internal
30			* @var string
31			*/
32			protected $_group = '';
33
34			/**
35			* Compiled predicates.
36			*
37			* @internal
38			* @var string
39			*/
40			protected $_having = '';
41
42			/**
43			* Compiled unions and intersections.
44			*
45			* @internal
46			* @var string
47			*/
48			protected $_import = '';
49
50			/**
51			* Compiled joins.
52			*
53			* @internal
54			* @var string
55			*/
56			protected $_join = '';
57
58			/**
59			* Compiled limit and offset.
60			*
61			* @internal
62			* @var string
63			*/
64			protected $_limit = '';
65
66			/**
67			* Compiled column list.
68			*
69			* @internal
70			* @var string
71			*/
72			protected $_order = '';
73
74			/**
75			* Compiled predicates.
76			*
77			* @internal
78			* @var string
79			*/
80			protected $_where = '';
81
82			/**
83			* Human-readable alias.
84			* This is initialized using `uniqid()` and the table's name.
85			*
86			* @var string
87			*/
88			protected $alias;
89
90			/**
91			* A callback to yield each result.
92			* Defaults to yielding directly from the statement.
93			*
94			* @var Closure `(Statement $statement):Generator`
95			*/
96			protected $fetcher;
97
98			/**
99			* Columns that can be accessed by an outer query.
100			*
101			* @var Column[]
102			*/
103			protected $refs = [];
104
105			/**
106			* @var string
107			*/
108			protected $table;
109
110			/**
111			* @param DB $db
112			* @param string\|Select $table
113			* @param string[] $columns
114			*/
115			public function __construct (DB $db, $table, array $columns) {
116			parent::__construct($db);
117			if ($table instanceof Select) {
118			$this->table = $table->toSubquery();
119			$this->alias = uniqid('_') . "_{$table->alias}";
120			}
121			else {
122			$this->table = (string)$table;
123			$this->alias = uniqid('_') . "__{$table}";
124			}
125			$this->setColumns($columns);
126			$this->fetcher = function(Statement $statement) {
127			yield from $statement;
128			};
129			}
130
131			/**
132			* @param array $args
133			* @return Statement
134			*/
135			public function __invoke (array $args = []) {
136			return $this->execute($args);
137			}
138
139			/**
140			* Returns the alias.
141			*
142			* @return string
143			*/
144			final public function __toString () {
145			return $this->alias;
146			}
147
148			/**
149			* Clones the instance and selects `COUNT(*)`, using the given execution arguments.
150			*
151			* @param array $args Execution arguments.
152			* @return int
153			*/
154			public function count (array $args = []): int {
155			$clone = clone $this;
156			$clone->_columns = 'COUNT(*)';
157			$clone->_order = '';
158			return (int)$clone->execute($args)->fetchColumn();
159			}
160
161			/**
162			* Executes the select, preparing a statement first if arguments are used.
163			*
164			* @param array $args
165			* @return Statement
166			*/
167			public function execute (array $args = []) {
168			if (empty($args)) {
169			return $this->db->query($this->toSql());
170			}
171			return $this->prepare()->__invoke($args);
172			}
173
174			/**
175			* Executes and fetches all results.
176			*
177			* @see fetcher
178			*
179			* @param array $args Execution arguments.
180			* @return array
181			*/
182			public function getAll (array $args = []): array {
183			return iterator_to_array($this->fetcher->__invoke($this->execute($args)));
184			}
185
186			/**
187			* Executes and yields from the fetcher.
188			* This is preferable over `fetchAll()` for iterating large result sets.
189			*
190			* @see fetcher
191			*
192			* @param array $args Execution arguments.
193			* @return Generator
194			*/
195			public function getEach (array $args = []) {
196			yield from $this->fetcher->__invoke($this->execute($args));
197			}
198
199			/**
200			* Executes and returns from the fetcher.
201			*
202			* @see fetcher
203			*
204			* @param array $args
205			* @return mixed
206			*/
207			public function getFirst (array $args = []) {
208			return $this->getEach($args)->current();
209			}
210
211			/**
212			* Executes without arguments and yields from the fetcher.
213			*
214			* @see fetcher
215			*
216			* @return Generator
217			*/
218			public function getIterator () {
219			yield from $this->getEach();
220			}
221
222			/**
223			* Adds a column to the `GROUP BY` clause.
224			*
225			* @param string $column
226			* @return $this
227			*/
228			public function group (string $column) {
229			if (!strlen($this->_group)) {
230			$this->_group = " GROUP BY {$column}";
231			}
232			else {
233			$this->_group .= ", {$column}";
234			}
235			return $this;
236			}
237
238			/**
239			* Adds a condition to the `HAVING` clause.
240			*
241			* @param string $condition
242			* @return $this
243			*/
244			public function having (string $condition) {
245			if (!strlen($this->_having)) {
246			$this->_having = " HAVING {$condition}";
247			}
248			else {
249			$this->_having .= " AND {$condition}";
250			}
251			return $this;
252			}
253
254			/**
255			* `INTERSECT` or `INTERSECT ALL`
256			*
257			* @param Select $select
258			* @param bool $all
259			* @return $this
260			*/
261			public function intersect (Select $select, $all = false) {
262			$select = clone $select;
263			$select->_order = '';
264			$select->_limit = '';
265			if ($all) {
266			$this->_import .= " INTERSECT ALL {$select->toSql()}";
267			}
268			else {
269			$this->_import .= " INTERSECT {$select->toSql()}";
270			}
271			return $this;
272			}
273
274			/**
275			* `NOT EXISTS (SELECT ...)`
276			*
277			* @return Predicate
278			*/
279			public function isEmpty () {
280			return new Predicate("NOT EXISTS ({$this->toSql()})");
281			}
282
283			/**
284			* `EXISTS (SELECT ...)`
285			*
286			* @return Predicate
287			*/
288			public function isNotEmpty () {
289			return new Predicate("EXISTS ({$this->toSql()})");
290			}
291
292			/**
293			* Adds a `JOIN` clause.
294			*
295			* @param string\|Select $table
296			* @param string $condition
297			* @param string $type
298			* @return $this
299			*/
300			public function join ($table, string $condition, string $type = 'INNER') {
301			if ($table instanceof Select) {
302			$table = $table->toSubquery();
303			}
304			$this->_join .= " {$type} JOIN {$table} ON {$condition}";
305			return $this;
306			}
307
308			/**
309			* Sets the `LIMIT` clause.
310			*
311			* @param int $limit
312			* @param int $offset
313			* @return $this
314			*/
315			public function limit (int $limit, int $offset = 0) {
316			if ($limit == 0) {
317			$this->_limit = '';
318			}
319			else {
320			$this->_limit = " LIMIT {$limit}";
321			if ($offset > 1) {
322			$this->_limit .= " OFFSET {$offset}";
323			}
324			}
325			return $this;
326			}
327
328			/**
329			* Whether a selected column can be referenced by an outer query.
330			*
331			* @param mixed $offset Ordinal or reference name.
332			* @return bool
333			*/
334			public function offsetExists ($offset): bool {
335			return isset($this->refs[$offset]);
336			}
337
338			/**
339			* Returns a reference {@link Column}, qualified by the instance's alias.
340			*
341			* @param mixed $offset Ordinal or reference name.
342			* @return Column
343			*/
344			public function offsetGet ($offset) {
345			return $this->refs[$offset];
346			}
347
348			/**
349			* Sets the `ORDER BY` clause.
350			*
351			* @param string $order
352			* @return $this
353			*/
354			public function order (string $order) {
355			if (strlen($order)) {
356			$order = " ORDER BY {$order}";
357			}
358			$this->_order = $order;
359			return $this;
360			}
361
362			/**
363			* @return Statement
364			*/
365			public function prepare () {
366			return $this->db->prepare($this->toSql());
367			}
368
369			/**
370			* @param string $alias
371			* @return $this
372			*/
373			public function setAlias (string $alias) {
374			$this->alias = $alias;
375			return $this;
376			}
377
378			/**
379			* Compiles the column list and exposed references.
380			*
381			* @param string[] $columns
382			* @return $this
383			*/
384			public function setColumns (array $columns) {
385			$this->refs = [];
386			$_columns = [];
387			$i = 0;
388			foreach ($columns as $alias => $column) {
389			preg_match('/^([a-z_][a-z0-9_]+\.)?(?<name>[a-z_][a-z0-9_]+)$/i', $column, $match);
390			$name = $match['name'] ?? null;
391			if (is_int($alias)) {
392			$alias = $name;
393			}
394			if (isset($alias)) {
395			$ref = new Column($this->db, $alias, $this->alias);
396			$this->refs[$alias] = $ref;
397			$this->refs[$i] = $ref;
398			if ($alias !== $name) {
399			$column = "{$column} AS {$alias}";
400			}
401			}
402			$_columns[] = "{$column}";
403			$i++;
404			}
405			$this->_columns = implode(', ', $_columns);
406			return $this;
407			}
408
409			/**
410			* @param Closure $fetcher
411			* @return $this
412			*/
413			public function setFetcher (Closure $fetcher) {
414			$this->fetcher = $fetcher;
415			return $this;
416			}
417
418			/**
419			* `SELECT ...`
420			*
421			* @return string
422			*/
423			public function toSql (): string {
424			$sql = "SELECT {$this->_columns} FROM {$this->table}";
425			$sql .= $this->_join;
426			$sql .= $this->_where;
427			$sql .= $this->_group;
428			$sql .= $this->_having;
429			$sql .= $this->_import;
430			$sql .= $this->_order;
431			$sql .= $this->_limit;
432			return $sql;
433			}
434
435			/**
436			* `(SELECT ...) AS ALIAS`
437			*
438			* @return string
439			*/
440			public function toSubquery (): string {
441			return "({$this->toSql()}) AS {$this->alias}";
442			}
443
444			/**
445			* `UNION` or `UNION ALL`
446			*
447			* @param Select $select
448			* @param bool $all
449			* @return $this
450			*/
451			public function union (Select $select, $all = false) {
452			$select = clone $select;
453			$select->_order = '';
454			$select->_limit = '';
455			if ($all) {
456			$this->_import .= " UNION ALL {$select->toSql()}";
457			}
458			else {
459			$this->_import .= " UNION {$select->toSql()}";
460			}
461			return $this;
462			}
463
464			/**
465			* Adds a condition to the `WHERE` clause.
466			*
467			* @param string $condition
468			* @return $this
469			*/
470			public function where (string $condition) {
471			if (!strlen($this->_where)) {
472			$this->_where = " WHERE {$condition}";
473			}
474			else {
475			$this->_where .= " AND {$condition}";
476			}
477			return $this;
478			}
479			}

hfw / db

Push — master ( 4a3939...6f91f6 )

Select::union() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like