Select::offsetExists() - Code Metrics - Inspection of "DB :: added is<Driver>() methods, added array acce..." - hfw/db - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — master ( 6c4562...bd418e )

by y

created 2020-11-05 22:58 UTC

Select::offsetExists() A

↳ Parent: Select::offsetGet()

Complexity

Conditions	1
Paths	1

Size

Total Lines	2
Code Lines	1

Duplication

Lines	0
Ratio	0 %

Importance

Changes	2
Bugs	0	Features	0

Metric	Value
eloc	1
c	2
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.
 */
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 $this->db->factory(Predicate::class, "NOT EXISTS ({$this->toSql()})");
    }

    /**
     * `EXISTS (SELECT ...)`
     *
     * @return Predicate
     */
    public function isNotEmpty () {
        return $this->db->factory(Predicate::class, "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] = $this->db->factory(Column::class, $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			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 $this->db->factory(Predicate::class, "NOT EXISTS ({$this->toSql()})");
281			}
282
283			/**
284			* `EXISTS (SELECT ...)`
285			*
286			* @return Predicate
287			*/
288			public function isNotEmpty () {
289			return $this->db->factory(Predicate::class, "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			* Returns a reference {@link Column} for an outer query, qualified by the instance's alias.
330			*
331			* @param int\|string $ref Ordinal or reference name.
332			* @return null\|Column
333			*/
334			public function offsetGet ($ref) {
335			if (is_int($ref)) {
336			return current(array_slice($this->refs, $ref, 1)) ?: null;
337			}
338			return $this->refs[$ref] ?? null;
339			}
340
341			/**
342			* Sets the `ORDER BY` clause.
343			*
344			* @param string $order
345			* @return $this
346			*/
347			public function order (string $order) {
348			if (strlen($order)) {
349			$order = " ORDER BY {$order}";
350			}
351			$this->_order = $order;
352			return $this;
353			}
354
355			/**
356			* @return Statement
357			*/
358			public function prepare () {
359			return $this->db->prepare($this->toSql());
360			}
361
362			/**
363			* @param string $alias
364			* @return $this
365			*/
366			public function setAlias (string $alias) {
367			$this->alias = $alias;
368			foreach ($this->refs as $k => $column) {
369			$this->refs[$k] = $column->setQualifier($alias);
370			}
371			return $this;
372			}
373
374			/**
375			* Compiles the column list and exposed reference columns.
376			*
377			* Columns may be expressions, like `COUNT(*)`
378			*
379			* Unless an alias is given for such columns, they can't be referenced externally.
380			*
381			* @param string[] $expressions Keyed by alias if applicable.
382			* @return $this
383			*/
384			public function setColumns (array $expressions) {
385			$this->refs = [];
386			$_columns = [];
387			foreach ($expressions as $alias => $expr) {
388			preg_match('/^([a-z_][a-z0-9_]+\.)?(?<name>[a-z_][a-z0-9_]+)$/i', $expr, $match);
389			$name = $match['name'] ?? null;
390			if (is_int($alias)) {
391			$alias = $name;
392			}
393			elseif ($alias !== $name) {
394			$expr .= " AS {$alias}";
395			}
396			if (isset($alias)) {
397			$this->refs[$alias] = $this->db->factory(Column::class, $this->db, $alias, $this->alias);
398			}
399			$_columns[] = "{$expr}";
400			}
401			$this->_columns = implode(', ', $_columns);
402			return $this;
403			}
404
405			/**
406			* @param Closure $fetcher
407			* @return $this
408			*/
409			public function setFetcher (Closure $fetcher) {
410			$this->fetcher = $fetcher;
411			return $this;
412			}
413
414			/**
415			* `SELECT ...`
416			*
417			* @return string
418			*/
419			public function toSql (): string {
420			$sql = "SELECT {$this->_columns} FROM {$this->table}";
421			$sql .= $this->_join;
422			$sql .= $this->_where;
423			$sql .= $this->_group;
424			$sql .= $this->_having;
425			$sql .= $this->_import;
426			$sql .= $this->_order;
427			$sql .= $this->_limit;
428			return $sql;
429			}
430
431			/**
432			* `(SELECT ...) AS ALIAS`
433			*
434			* @return string
435			*/
436			public function toSubquery (): string {
437			return "({$this->toSql()}) AS {$this->alias}";
438			}
439
440			/**
441			* `UNION` or `UNION ALL`
442			*
443			* @param Select $select
444			* @param bool $all
445			* @return $this
446			*/
447			public function union (Select $select, $all = false) {
448			$select = clone $select;
449			$select->_order = '';
450			$select->_limit = '';
451			if ($all) {
452			$this->_import .= " UNION ALL {$select->toSql()}";
453			}
454			else {
455			$this->_import .= " UNION {$select->toSql()}";
456			}
457			return $this;
458			}
459
460			/**
461			* Adds a condition to the `WHERE` clause.
462			*
463			* @param string $condition
464			* @return $this
465			*/
466			public function where (string $condition) {
467			if (!strlen($this->_where)) {
468			$this->_where = " WHERE {$condition}";
469			}
470			else {
471			$this->_where .= " AND {$condition}";
472			}
473			return $this;
474			}
475			}

hfw / db

Push — master ( 6c4562...bd418e )

Select::offsetExists() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like