SelectQuery::innerJoin() - Code Metrics - Inspection of "restore support for `LIMIT` and `ORDER BY` in MySQ..." - mindplay-dk/sql - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( 509987...79a3d2 )

by Rasmus

created 2019-03-26 14:30 UTC

SelectQuery::innerJoin() A

↳ Parent: SelectQuery

Complexity

Conditions	1
Paths	1

Size

Total Lines

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	2
CRAP Score	1

Importance

Changes

Metric	Value
dl	0
loc	4
ccs	2
cts	2
cp	1
rs	10
c	0
b	0
f	0
cc	1
nc	1
nop	2
crap	1

<?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\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[] one or more Columns to select and return
     *
     * @return $this
     */
    public function columns($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($expr, $name = 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()
    {
        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()
    {
        $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()
    {
        return "(" . $this->getSQL() . ")";
    }

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

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

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

    /**
     * @inheritdoc
     */
    public function getSQL()
    {
        $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)
    {
        $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)
    {
        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()
    {
        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()
    {
        return implode(" ", array_keys($this->flags));
    }

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

mindplay-dk / sql

Push — master ( 509987...79a3d2 )

SelectQuery::innerJoin() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like