Select::getResult() - Code Metrics - Inspection of "added more numeric functions, and DB::sqliteCreate..." - hfw/db - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — master ( c6a312...8ed35d )

by y

created 2021-06-07 05:37 UTC

Select::getResult() A

↳ Parent: Select

Complexity

Conditions	1
Paths	1

Size

Total Lines	2
Code Lines	1

Duplication

Lines	0
Ratio	0 %

Importance

Changes

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

<?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.
 *
 * @method static static factory(DB $db, $table, array $columns)
 */
class Select extends AbstractTable implements Countable, IteratorAggregate, ExpressionInterface {

    use FactoryTrait;

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

    /**
     * Executes and returns the first column of the first row.
     * Use this for reductive queries that only have a single result.
     *
     * @return mixed
     */
    public function getResult (array $args = []) {
        return $this->execute($args)->fetchColumn();
    }

    /**
     * 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 Predicate::factory($this->db, "NOT EXISTS ({$this->toSql()})");
    }

    /**
     * `EXISTS (SELECT ...)`
     *
     * @return Predicate
     */
    public function isNotEmpty () {
        return Predicate::factory($this->db, "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;
    }

    /**
     * Returns a reference {@link Column} for an outer query, qualified by the instance's alias.
     *
     * @param int|string $ref Ordinal or reference name.
     * @return null|Column
     */
    public function offsetGet ($ref) {
        if (is_int($ref)) {
            return current(array_slice($this->refs, $ref, 1)) ?: null;
        }
        return $this->refs[$ref] ?? null;
    }

    /**
     * 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;
        foreach ($this->refs as $k => $column) {
            $this->refs[$k] = $column->setQualifier($alias);
        }
        return $this;
    }

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

hfw / db

Push — master ( c6a312...8ed35d )

Select::getResult() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like