SelectQuery::getSQL() - Code Metrics - mindplay-dk/sql - Measure and Improve Code Quality continuously with Scrutinizer

SelectQuery::getSQL() B
last analyzed 2024-10-27 08:46 UTC

↳ Parent: SelectQuery

Complexity

Conditions	8
Paths	128

Size

Total Lines	31
Code Lines	21

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	22
CRAP Score	8

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
cc	8
eloc	21
c	1
b	0
f	0
nc	128
nop	0
dl	0
loc	31
ccs	22
cts	22
cp	1
crap	8
rs	8.2111

<?php

namespace mindplay\sql\model\query;

use mindplay\sql\framework\Countable;
use mindplay\sql\framework\MapperProvider;
use mindplay\sql\model\components\Conditions;
use mindplay\sql\model\components\Mappers;
use mindplay\sql\model\components\Order;
use mindplay\sql\model\components\Range;
use mindplay\sql\model\components\ReturnVars;
use mindplay\sql\model\Driver;
use mindplay\sql\model\expr;
use mindplay\sql\model\schema\Column;
use mindplay\sql\model\schema\Table;
use mindplay\sql\model\schema\Type;
use mindplay\sql\model\TypeProvider;
use mindplay\sql\model\types\IntType;

/**
 * This class represents a SELECT query.
 *
 * This class implements `__toString()` magic, enabling the use of this query builder
 * in the SELECT, WHERE or ORDER BY clause of a parent SELECT (or other type of) query.
 *
 * Note that, when constructing nested queries, parameters must be bound against the
 * parent query - binding parameters or applying Mappers against a nested query has no effect.
 */
class SelectQuery extends Query implements MapperProvider, Countable
{
    use Mappers;
    use Conditions;
    use Order;
    use Range;

    /**
     * @var Driver
     */
    protected $driver;

    /**
     * @var Table root Table of this query (from which JOIN clauses may extend the projection)
     */
    protected $root;

    /**
     * @var string[] list of JOIN clauses extending from the root Table of this query
     */
    protected $joins = [];

    /**
     * @var bool[] map where flag => true
     */
    private $flags = [];

    /**
     * @var ReturnVars
     */
    protected $return_vars;

    /**
     * @var string[] list of GROUP BY expressions
     */
    protected $group_by = [];

    /**
     * @var string[] list of HAVING expressions
     */
    protected $having = [];

    /**
     * @param Table        $root
     * @param Driver       $driver
     * @param TypeProvider $types
     */
    public function __construct(Table $root, Driver $driver, TypeProvider $types)
    {
        parent::__construct($types);

        $this->root = $root;
        $this->driver = $driver;
        $this->return_vars = new ReturnVars($root, $driver, $types);
    }

    /**
     * Add all the Columns of a full Table to be selected and returned
     *
     * @param Table $table Table to select and return
     *
     * @return $this
     */
    public function table(Table $table)
    {
        $this->return_vars->addTable($table);

        return $this;
    }

    /**
     * Add one or more Columns to select and return
     *
     * @param Column|Column[] $cols one or more Columns to select and return
     *
     * @return $this
     */
    public function columns(Column|array $cols)
    {
        $this->return_vars->addColumns($cols);

        return $this;
    }

    /**
     * Add an SQL expression to select and return
     *
     * @param string           $expr return expression
     * @param string|null      $name return variable name (optional, but usually required)
     * @param Type|string|null $type optional Type (or Type class-name)
     *
     * @return $this
     */
    public function value(string $expr, string|null $name = null, Type|string|null $type = null)
    {
        $this->return_vars->addValue($expr, $name, $type);

        return $this;
    }

    /**
     * Add an expression to apply to a GROUP BY clause
     *
     * @param Column|string $expr SQL expression (or Column object) to apply to the GROUP BY clause
     *
     * @return $this
     */
    public function groupBy($expr)
    {
        $this->group_by[] = (string) $expr;

        return $this;
    }

    /**
     * @param string|string[] $exprs one or more condition expressions to apply to the HAVING clause
     *
     * @return $this
     */
    public function having($exprs)
    {
        foreach ((array) $exprs as $expr) {
            $this->having[] = $expr;
        }

        return $this;
    }

    /**
     * @inheritdoc
     */
    public function getMappers(): array
    {
        return array_merge([$this->return_vars->createTypeMapper()], $this->mappers);
    }

    /**
     * @internal do not call this method directly from client-code (see `Countable` interface)
     *
     * @ignore
     *
     * @see Connection::count()
     *
     * @return SelectQuery
     */
    public function createCountStatement(): SelectQuery
    {
        $query = clone $this;

        $query->return_vars = new ReturnVars($this->root, $this->driver, $this->types);

        $query->return_vars->addValue("COUNT(*)", "count", IntType::class);

        $query->mappers = []; // remove existing mappers not applicable to the COUNT result

        $query->limit = null;
        $query->offset = null;

        $query->order = [];

        return $query;
    }

    /**
     * @ignore string magic (enables creation of nested SELECT queries)
     */
    public function __toString(): string
    {
        return "(" . $this->getSQL() . ")";
    }

    /**
     * @param Table  $table
     * @param string $expr join condition
     *
     * @return $this
     */
    public function innerJoin(Table $table, $expr): static
    {
        return $this->addJoin("INNER", $table, $expr);
    }

    /**
     * @param Table  $table
     * @param string $expr join condition
     *
     * @return $this
     */
    public function leftJoin(Table $table, $expr): static
    {
        return $this->addJoin("LEFT", $table, $expr);
    }

    /**
     * @param Table  $table
     * @param string $expr join condition
     *
     * @return $this
     */
    public function rightJoin(Table $table, $expr): static
    {
        return $this->addJoin("RIGHT", $table, $expr);
    }

    /**
     * @inheritdoc
     */
    public function getSQL(): string
    {
        $flags = $this->buildFlags();

        $select = "SELECT " . ($flags ? "{$flags} " : "")
            . $this->return_vars->buildReturnVars();

        $from = "\nFROM " . $this->buildNodes();

        $where = count($this->conditions)
            ? "\nWHERE " . $this->buildConditions()
            : ''; // no conditions present

        $group_by = count($this->group_by)
            ? "\nGROUP BY " . implode(", ", $this->group_by)
            : ""; // no group-by expressions

        $having = count($this->having)
            ? "\nHAVING " . $this->buildHaving()
            : ''; // no having clause present

        $order = count($this->order)
            ? "\nORDER BY " . $this->buildOrderTerms()
            : ''; // no order terms

        $limit = $this->limit !== null
            ? "\nLIMIT {$this->limit}"
            . ($this->offset !== null ? " OFFSET {$this->offset}" : '')
            : ''; // no limit or offset

        return "{$select}{$from}{$where}{$group_by}{$having}{$order}{$limit}";
    }

    /**
     * @param string $type join type ("INNER", "LEFT", etc.)
     * @param Table  $table
     * @param string $expr join condition
     *
     * @return $this
     */
    protected function addJoin($type, Table $table, $expr): static
    {
        $table_expr = $table->getNode();

        $this->joins[] = "{$type} JOIN {$table_expr} ON {$expr}";

        return $this;
    }

    /**
     * @param string $flag
     * @param bool   $state
     */
    protected function setFlag($flag, $state = true): void
    {
        if ($state) {
            $this->flags[$flag] = true;
        } else {
            unset($this->flags[$flag]);
        }
    }

    /**
     * @return string root table expression and JOIN clauses (for use in the FROM clause of an SQL statement)
     */
    protected function buildNodes(): string
    {
        return implode("\n", array_merge([$this->root->getNode()], $this->joins));
    }

    /**
     * @return string query flags (such as "SQL_CALC_FOUND_ROWS" in a MySQL SELECT query)
     */
    protected function buildFlags(): string
    {
        return implode(" ", array_keys($this->flags));
    }

    /**
     * @return string combined condition expression (for use in the HAVING clause of an SQL statement)
     */
    protected function buildHaving(): string
    {
        return expr::all($this->having);
    }
}


1		<?php
2
3		namespace mindplay\sql\model\query;
4
5		use mindplay\sql\framework\Countable;
6		use mindplay\sql\framework\MapperProvider;
7		use mindplay\sql\model\components\Conditions;
8		use mindplay\sql\model\components\Mappers;
9		use mindplay\sql\model\components\Order;
10		use mindplay\sql\model\components\Range;
11		use mindplay\sql\model\components\ReturnVars;
12		use mindplay\sql\model\Driver;
13		use mindplay\sql\model\expr;
14		use mindplay\sql\model\schema\Column;
15		use mindplay\sql\model\schema\Table;
16		use mindplay\sql\model\schema\Type;
17		use mindplay\sql\model\TypeProvider;
18		use mindplay\sql\model\types\IntType;
19
20		/**
21		* This class represents a SELECT query.
22		*
23		* This class implements `__toString()` magic, enabling the use of this query builder
24		* in the SELECT, WHERE or ORDER BY clause of a parent SELECT (or other type of) query.
25		*
26		* Note that, when constructing nested queries, parameters must be bound against the
27		* parent query - binding parameters or applying Mappers against a nested query has no effect.
28		*/
29		class SelectQuery extends Query implements MapperProvider, Countable
30		{
31		use Mappers;
32		use Conditions;
33		use Order;
34		use Range;
35
36		/**
37		* @var Driver
38		*/
39		protected $driver;
40
41		/**
42		* @var Table root Table of this query (from which JOIN clauses may extend the projection)
43		*/
44		protected $root;
45
46		/**
47		* @var string[] list of JOIN clauses extending from the root Table of this query
48		*/
49		protected $joins = [];
50
51		/**
52		* @var bool[] map where flag => true
53		*/
54		private $flags = [];
55
56		/**
57		* @var ReturnVars
58		*/
59		protected $return_vars;
60
61		/**
62		* @var string[] list of GROUP BY expressions
63		*/
64		protected $group_by = [];
65
66		/**
67		* @var string[] list of HAVING expressions
68		*/
69		protected $having = [];
70
71		/**
72		* @param Table $root
73		* @param Driver $driver
74		* @param TypeProvider $types
75		*/
76	1	public function __construct(Table $root, Driver $driver, TypeProvider $types)
77		{
78	1	parent::__construct($types);
79
80	1	$this->root = $root;
81	1	$this->driver = $driver;
82	1	$this->return_vars = new ReturnVars($root, $driver, $types);
83		}
84
85		/**
86		* Add all the Columns of a full Table to be selected and returned
87		*
88		* @param Table $table Table to select and return
89		*
90		* @return $this
91		*/
92	1	public function table(Table $table)
93		{
94	1	$this->return_vars->addTable($table);
95
96	1	return $this;
97		}
98
99		/**
100		* Add one or more Columns to select and return
101		*
102		* @param Column\|Column[] $cols one or more Columns to select and return
103		*
104		* @return $this
105		*/
106	1	public function columns(Column\|array $cols)
107		{
108	1	$this->return_vars->addColumns($cols);
109
110	1	return $this;
111		}
112
113		/**
114		* Add an SQL expression to select and return
115		*
116		* @param string $expr return expression
117		* @param string\|null $name return variable name (optional, but usually required)
118		* @param Type\|string\|null $type optional Type (or Type class-name)
119		*
120		* @return $this
121		*/
122	1	public function value(string $expr, string\|null $name = null, Type\|string\|null $type = null)
123		{
124	1	$this->return_vars->addValue($expr, $name, $type);
125
126	1	return $this;
127		}
128
129		/**
130		* Add an expression to apply to a GROUP BY clause
131		*
132		* @param Column\|string $expr SQL expression (or Column object) to apply to the GROUP BY clause
133		*
134		* @return $this
135		*/
136	1	public function groupBy($expr)
137		{
138	1	$this->group_by[] = (string) $expr;
139
140	1	return $this;
141		}
142
143		/**
144		* @param string\|string[] $exprs one or more condition expressions to apply to the HAVING clause
145		*
146		* @return $this
147		*/
148	1	public function having($exprs)
149		{
150	1	foreach ((array) $exprs as $expr) {
151	1	$this->having[] = $expr;
152		}
153
154	1	return $this;
155		}
156
157		/**
158		* @inheritdoc
159		*/
160	1	public function getMappers(): array
161		{
162	1	return array_merge([$this->return_vars->createTypeMapper()], $this->mappers);
163		}
164
165		/**
166		* @internal do not call this method directly from client-code (see `Countable` interface)
167		*
168		* @ignore
169		*
170		* @see Connection::count()
171		*
172		* @return SelectQuery
173		*/
174	1	public function createCountStatement(): SelectQuery
175		{
176	1	$query = clone $this;
177
178	1	$query->return_vars = new ReturnVars($this->root, $this->driver, $this->types);
179
180	1	$query->return_vars->addValue("COUNT(*)", "count", IntType::class);
181
182	1	$query->mappers = []; // remove existing mappers not applicable to the COUNT result
183
184	1	$query->limit = null;
185	1	$query->offset = null;
186
187	1	$query->order = [];
188
189	1	return $query;
190		}
191
192		/**
193		* @ignore string magic (enables creation of nested SELECT queries)
194		*/
195	1	public function __toString(): string
196		{
197	1	return "(" . $this->getSQL() . ")";
198		}
199
200		/**
201		* @param Table $table
202		* @param string $expr join condition
203		*
204		* @return $this
205		*/
206	1	public function innerJoin(Table $table, $expr): static
207		{
208	1	return $this->addJoin("INNER", $table, $expr);
209		}
210
211		/**
212		* @param Table $table
213		* @param string $expr join condition
214		*
215		* @return $this
216		*/
217		public function leftJoin(Table $table, $expr): static
218		{
219		return $this->addJoin("LEFT", $table, $expr);
220		}
221
222		/**
223		* @param Table $table
224		* @param string $expr join condition
225		*
226		* @return $this
227		*/
228		public function rightJoin(Table $table, $expr): static
229		{
230		return $this->addJoin("RIGHT", $table, $expr);
231		}
232
233		/**
234		* @inheritdoc
235		*/
236	1	public function getSQL(): string
237		{
238	1	$flags = $this->buildFlags();
239
240	1	$select = "SELECT " . ($flags ? "{$flags} " : "")
241	1	. $this->return_vars->buildReturnVars();
242
243	1	$from = "\nFROM " . $this->buildNodes();
244
245	1	$where = count($this->conditions)
246	1	? "\nWHERE " . $this->buildConditions()
247	1	: ''; // no conditions present
248
249	1	$group_by = count($this->group_by)
250	1	? "\nGROUP BY " . implode(", ", $this->group_by)
251	1	: ""; // no group-by expressions
252
253	1	$having = count($this->having)
254	1	? "\nHAVING " . $this->buildHaving()
255	1	: ''; // no having clause present
256
257	1	$order = count($this->order)
258	1	? "\nORDER BY " . $this->buildOrderTerms()
259	1	: ''; // no order terms
260
261	1	$limit = $this->limit !== null
262	1	? "\nLIMIT {$this->limit}"
263	1	. ($this->offset !== null ? " OFFSET {$this->offset}" : '')
264	1	: ''; // no limit or offset
265
266	1	return "{$select}{$from}{$where}{$group_by}{$having}{$order}{$limit}";
267		}
268
269		/**
270		* @param string $type join type ("INNER", "LEFT", etc.)
271		* @param Table $table
272		* @param string $expr join condition
273		*
274		* @return $this
275		*/
276	1	protected function addJoin($type, Table $table, $expr): static
277		{
278	1	$table_expr = $table->getNode();
279
280	1	$this->joins[] = "{$type} JOIN {$table_expr} ON {$expr}";
281
282	1	return $this;
283		}
284
285		/**
286		* @param string $flag
287		* @param bool $state
288		*/
289	1	protected function setFlag($flag, $state = true): void
290		{
291	1	if ($state) {
292	1	$this->flags[$flag] = true;
293		} else {
294		unset($this->flags[$flag]);
295		}
296		}
297
298		/**
299		* @return string root table expression and JOIN clauses (for use in the FROM clause of an SQL statement)
300		*/
301	1	protected function buildNodes(): string
302		{
303	1	return implode("\n", array_merge([$this->root->getNode()], $this->joins));
304		}
305
306		/**
307		* @return string query flags (such as "SQL_CALC_FOUND_ROWS" in a MySQL SELECT query)
308		*/
309	1	protected function buildFlags(): string
310		{
311	1	return implode(" ", array_keys($this->flags));
312		}
313
314		/**
315		* @return string combined condition expression (for use in the HAVING clause of an SQL statement)
316		*/
317	1	protected function buildHaving(): string
318		{
319	1	return expr::all($this->having);
320		}
321		}
322

mindplay-dk / sql

SelectQuery::getSQL() B last analyzed 2024-10-27 08:46 UTC

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like

SelectQuery::getSQL() B
last analyzed 2024-10-27 08:46 UTC