QueryBuilder::replaceParameters() - Code Metrics - Inspection of "QueryBuilder: add params type support" - apicart/php-utils - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( cbb9d8...dc8c5b )

by Tomáš

created 2019-04-17 10:00 UTC

QueryBuilder::replaceParameters() B

↳ Parent: QueryBuilder

Complexity

Conditions	6
Paths	1

Size

Total Lines	38
Code Lines	22

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	20
CRAP Score	6.0798

Importance

Changes

Metric	Value
cc	6
eloc	22
nc	1
nop	2
dl	0
loc	38
ccs	20
cts	23
cp	0.8696
crap	6.0798
rs	8.9457
c	0
b	0
f	0

<?php declare(strict_types = 1);

namespace Apicart\Utils\Sql;

use Apicart\Utils\Exception\Sql\InvalidParamsFormaterForQueryBuilderException;

final class QueryBuilder
{

	public const PARAMS_FORMAT_BIND = 'bind';
	public const PARAMS_FORMAT_DOCTRINE = 'doctrine';

	/**
	 * @var string|int|null
	 */
	private $offset;

	/**
	 * @var string|int|null
	 */
	private $limit;

	/**
	 * @var array
	 */
	private $parameters = [];

	/**
	 * @var string[]
	 */
	private $select = [];

	/**
	 * @var string[]
	 */
	private $join = [];

	/**
	 * @var string[]
	 */
	private $where = [];

	/**
	 * @var string[]
	 */
	private $groupBy = [];

	/**
	 * @var string[]
	 */
	private $orderBy = [];


	public static function create(): self
	{
		return new self;
	}


	public function select(string $columns): self
	{
		$this->select = [$columns];

		return $this;
	}


	public function addSelect(string $columns): self
	{
		$this->select[] = $columns;

		return $this;
	}


	public function from(string $tableName, string $alias = ''): self
	{
		$this->join[] = sprintf('FROM %s %s', $tableName, $alias);

		return $this;
	}


	public function addJoinDirectly(string $join): self
	{
		$this->join[] = $join;

		return $this;
	}


	public function join(string $tableName, string $alias, string $condition): self
	{
		return $this->innerJoin($tableName, $alias, $condition);
	}


	public function innerJoin(string $tableName, string $alias, string $condition): self
	{
		$this->join[$alias] = sprintf('INNER JOIN %s %s ON %s', $tableName, $alias, $condition);

		return $this;
	}


	public function leftJoin(string $tableName, string $alias, string $condition): self
	{
		$this->join[$alias] = sprintf('LEFT JOIN %s %s ON %s', $tableName, $alias, $condition);

		return $this;
	}


	public function addWhereDirectly(string $where): self
	{
		$this->where[] = $where;

		return $this;
	}


	public function where(string $condition): self
	{
		$this->where = [$condition];

		return $this;
	}


	public function andWhere(string $condition): self
	{
		$this->where[] = sprintf(' AND (%s)', $condition);

		return $this;
	}


	public function orWhere(string $condition): self
	{
		$this->where[] = sprintf(' OR (%s)', $condition);

		return $this;
	}


	public function groupBy(string $column): self
	{
		$this->groupBy = [$column];

		return $this;
	}


	public function addGroupBy(string $column): self
	{
		$this->groupBy[] = $column;

		return $this;
	}


	public function orderBy(string $column, string $order = 'DESC'): self
	{
		$this->orderBy = [$column . ' ' . $order];

		return $this;
	}


	public function addOrderBy(string $column, string $order = 'DESC'): self
	{
		$this->orderBy[] = $column . ' ' . $order;

		return $this;
	}


	/**
	 * @param string|int $limit
	 */
	public function setLimit($limit): self
	{
		$this->limit = $limit;

		return $this;
	}


	/**
	 * @param string|int $offset
	 */
	public function setOffset($offset): self
	{
		$this->offset = $offset;

		return $this;
	}


	public function setParameters(array $parameters): self
	{
		$this->parameters = $parameters;

		return $this;
	}


	/**
	 * @param int|string|bool|float $value
	 */
	public function setParameter(string $key, $value): self
	{
		$this->parameters[$key] = $value;

		return $this;
	}


	public function getSql(string $paramsFormat = self::PARAMS_FORMAT_BIND): string
	{
		$sql = 'SELECT ';

		// apply columns
		$sql .= implode(', ', $this->select);
		$sql .= PHP_EOL;

		// apply joins
		$sql .= implode(PHP_EOL, $this->join);
		$sql .= PHP_EOL;

		// apply conditions
		if ($this->where !== []) {
			$sql .= 'WHERE ';

			for ($index = 0; $index < count($this->where); $index++) {
for ($i=0; $i<count($array); $i++) { // calls count() on each iteration
}

// Better
for ($i=0, $c=count($array); $i<$c; $i++) { // calls count() just once
}
				$condition = $this->where[$index];

				if ($index === 0) {
					$condition = preg_replace('/^ (AND|OR) /', '', $condition);
				}

				$sql .= $condition;
			}

			$sql .= PHP_EOL;
		}

		// apply group by
		if ($this->groupBy !== []) {
			$sql .= 'GROUP BY ';
			$sql .= implode(',', $this->groupBy);
			$sql .= PHP_EOL;
		}

		// apply orders
		if ($this->orderBy !== []) {
			$sql .= 'ORDER BY ';
			$sql .= implode(',', $this->orderBy);
			$sql .= PHP_EOL;
		}

		if ($this->limit) {
			$sql .= 'LIMIT ' . $this->limit;
			$sql .= PHP_EOL;
		}
		if ($this->offset) {
			$sql .= 'OFFSET ' . $this->offset;
		}

		return $this->replaceParameters($sql, $paramsFormat);
	}


	public function getParameters(): array
	{
		return $this->parameters;
	}


	/**
	 * @param mixed $default
	 * @return mixed
	 */
	public function getParameter(string $key, $default = null)
	{
		return $this->parameters[$key] ?? $default;
	}


	/**
	 * @return string[]
	 */
	public function getSelect(): array
	{
		return $this->select;
	}


	/**
	 * @return string[]
	 */
	public function getJoin(): array
	{
		return $this->join;
	}


	/**
	 * @return string[]
	 */
	public function getWhere(): array
	{
		return $this->where;
	}


	/**
	 * @return string[]
	 */
	public function getGroupBy(): array
	{
		return $this->groupBy;
	}


	/**
	 * @return string[]
	 */
	public function getOrderBy(): array
	{
		return $this->orderBy;
	}


	/**
	 * @return int|string|null
	 */
	public function getLimit()
	{
		return $this->limit;
	}


	/**
	 * @return int|string|null
	 */
	public function getOffset()
	{
		return $this->offset;
	}


	private function replaceParameters(string $sql, string $paramsFormat): string
	{
		$userParameters = $this->getParameters();
		$newParameters = [];

		$parametrizedSql = preg_replace_callback(
			'#([ (]+):([a-zA-Z0-9]+)#',
			function ($match) use (&$newParameters, $userParameters, $paramsFormat) {
				$newParameter = $userParameters[$match[2]];

				if ($paramsFormat === self::PARAMS_FORMAT_BIND && is_array($newParameter)) {
					// expand array parameter into string of parameters e.g. $1,$2,$3...
					$output = $match[1];
					foreach ($newParameter as $data) {
						$newParameters[] = $data;
						$output .= sprintf('$%d,', count($newParameters));
					}
					$output = substr($output, 0, -1);

					return $output;
				}

				$newParameters[] = $newParameter;

				if ($paramsFormat === self::PARAMS_FORMAT_BIND) {
					return sprintf('%s$%d', $match[1], count($newParameters));
				} elseif ($paramsFormat === self::PARAMS_FORMAT_DOCTRINE) {
					return sprintf('%s?', $match[1]);
				}
					throw new InvalidParamsFormaterForQueryBuilderException;

			},
			$sql
		);

		$this->parameters = $newParameters;

		return $parametrizedSql;
	}

}


1		<?php declare(strict_types = 1);
2
3		namespace Apicart\Utils\Sql;
4
5		use Apicart\Utils\Exception\Sql\InvalidParamsFormaterForQueryBuilderException;
6
7		final class QueryBuilder
8		{
9
10		public const PARAMS_FORMAT_BIND = 'bind';
11		public const PARAMS_FORMAT_DOCTRINE = 'doctrine';
12
13		/**
14		* @var string\|int\|null
15		*/
16		private $offset;
17
18		/**
19		* @var string\|int\|null
20		*/
21		private $limit;
22
23		/**
24		* @var array
25		*/
26		private $parameters = [];
27
28		/**
29		* @var string[]
30		*/
31		private $select = [];
32
33		/**
34		* @var string[]
35		*/
36		private $join = [];
37
38		/**
39		* @var string[]
40		*/
41		private $where = [];
42
43		/**
44		* @var string[]
45		*/
46		private $groupBy = [];
47
48		/**
49		* @var string[]
50		*/
51		private $orderBy = [];
52
53
54	2	public static function create(): self
55		{
56	2	return new self;
57		}
58
59
60	2	public function select(string $columns): self
61		{
62	2	$this->select = [$columns];
63
64	2	return $this;
65		}
66
67
68	2	public function addSelect(string $columns): self
69		{
70	2	$this->select[] = $columns;
71
72	2	return $this;
73		}
74
75
76	2	public function from(string $tableName, string $alias = ''): self
77		{
78	2	$this->join[] = sprintf('FROM %s %s', $tableName, $alias);
79
80	2	return $this;
81		}
82
83
84		public function addJoinDirectly(string $join): self
85		{
86		$this->join[] = $join;
87
88		return $this;
89		}
90
91
92		public function join(string $tableName, string $alias, string $condition): self
93		{
94		return $this->innerJoin($tableName, $alias, $condition);
95		}
96
97
98	2	public function innerJoin(string $tableName, string $alias, string $condition): self
99		{
100	2	$this->join[$alias] = sprintf('INNER JOIN %s %s ON %s', $tableName, $alias, $condition);
101
102	2	return $this;
103		}
104
105
106	2	public function leftJoin(string $tableName, string $alias, string $condition): self
107		{
108	2	$this->join[$alias] = sprintf('LEFT JOIN %s %s ON %s', $tableName, $alias, $condition);
109
110	2	return $this;
111		}
112
113
114		public function addWhereDirectly(string $where): self
115		{
116		$this->where[] = $where;
117
118		return $this;
119		}
120
121
122	2	public function where(string $condition): self
123		{
124	2	$this->where = [$condition];
125
126	2	return $this;
127		}
128
129
130	2	public function andWhere(string $condition): self
131		{
132	2	$this->where[] = sprintf(' AND (%s)', $condition);
133
134	2	return $this;
135		}
136
137
138	2	public function orWhere(string $condition): self
139		{
140	2	$this->where[] = sprintf(' OR (%s)', $condition);
141
142	2	return $this;
143		}
144
145
146		public function groupBy(string $column): self
147		{
148		$this->groupBy = [$column];
149
150		return $this;
151		}
152
153
154		public function addGroupBy(string $column): self
155		{
156		$this->groupBy[] = $column;
157
158		return $this;
159		}
160
161
162		public function orderBy(string $column, string $order = 'DESC'): self
163		{
164		$this->orderBy = [$column . ' ' . $order];
165
166		return $this;
167		}
168
169
170		public function addOrderBy(string $column, string $order = 'DESC'): self
171		{
172		$this->orderBy[] = $column . ' ' . $order;
173
174		return $this;
175		}
176
177
178		/**
179		* @param string\|int $limit
180		*/
181		public function setLimit($limit): self
182		{
183		$this->limit = $limit;
184
185		return $this;
186		}
187
188
189		/**
190		* @param string\|int $offset
191		*/
192		public function setOffset($offset): self
193		{
194		$this->offset = $offset;
195
196		return $this;
197		}
198
199
200	2	public function setParameters(array $parameters): self
201		{
202	2	$this->parameters = $parameters;
203
204	2	return $this;
205		}
206
207
208		/**
209		* @param int\|string\|bool\|float $value
210		*/
211	2	public function setParameter(string $key, $value): self
212		{
213	2	$this->parameters[$key] = $value;
214
215	2	return $this;
216		}
217
218
219	1	public function getSql(string $paramsFormat = self::PARAMS_FORMAT_BIND): string
220		{
221	1	$sql = 'SELECT ';
222
223		// apply columns
224	1	$sql .= implode(', ', $this->select);
225	1	$sql .= PHP_EOL;
226
227		// apply joins
228	1	$sql .= implode(PHP_EOL, $this->join);
229	1	$sql .= PHP_EOL;
230
231		// apply conditions
232	1	if ($this->where !== []) {
233	1	$sql .= 'WHERE ';
234
235	1	for ($index = 0; $index < count($this->where); $index++) {
		0 ignored issues – show Performance Best Practice introduced 2019-04-16 18:37 UTC by Report Bug Copy Issue Report It seems like you are calling the size function `count()` as part of the test condition. You might want to compute the size beforehand, and not on each iteration. If the size of the collection does not change during the iteration, it is generally a good practice to compute it beforehand, and not on each iteration: for ($i=0; $i<count($array); $i++) { // calls count() on each iteration } // Better for ($i=0, $c=count($array); $i<$c; $i++) { // calls count() just once } Loading history...
236	1	$condition = $this->where[$index];
237
238	1	if ($index === 0) {
239	1	$condition = preg_replace('/^ (AND\|OR) /', '', $condition);
240		}
241
242	1	$sql .= $condition;
243		}
244
245	1	$sql .= PHP_EOL;
246		}
247
248		// apply group by
249	1	if ($this->groupBy !== []) {
250		$sql .= 'GROUP BY ';
251		$sql .= implode(',', $this->groupBy);
252		$sql .= PHP_EOL;
253		}
254
255		// apply orders
256	1	if ($this->orderBy !== []) {
257		$sql .= 'ORDER BY ';
258		$sql .= implode(',', $this->orderBy);
259		$sql .= PHP_EOL;
260		}
261
262	1	if ($this->limit) {
263		$sql .= 'LIMIT ' . $this->limit;
264		$sql .= PHP_EOL;
265		}
266	1	if ($this->offset) {
267		$sql .= 'OFFSET ' . $this->offset;
268		}
269
270	1	return $this->replaceParameters($sql, $paramsFormat);
271		}
272
273
274	2	public function getParameters(): array
275		{
276	2	return $this->parameters;
277		}
278
279
280		/**
281		* @param mixed $default
282		* @return mixed
283		*/
284		public function getParameter(string $key, $default = null)
285		{
286		return $this->parameters[$key] ?? $default;
287		}
288
289
290		/**
291		* @return string[]
292		*/
293		public function getSelect(): array
294		{
295		return $this->select;
296		}
297
298
299		/**
300		* @return string[]
301		*/
302		public function getJoin(): array
303		{
304		return $this->join;
305		}
306
307
308		/**
309		* @return string[]
310		*/
311		public function getWhere(): array
312		{
313		return $this->where;
314		}
315
316
317		/**
318		* @return string[]
319		*/
320		public function getGroupBy(): array
321		{
322		return $this->groupBy;
323		}
324
325
326		/**
327		* @return string[]
328		*/
329		public function getOrderBy(): array
330		{
331		return $this->orderBy;
332		}
333
334
335		/**
336		* @return int\|string\|null
337		*/
338		public function getLimit()
339		{
340		return $this->limit;
341		}
342
343
344		/**
345		* @return int\|string\|null
346		*/
347		public function getOffset()
348		{
349		return $this->offset;
350		}
351
352
353	1	private function replaceParameters(string $sql, string $paramsFormat): string
354		{
355	1	$userParameters = $this->getParameters();
356	1	$newParameters = [];
357
358	1	$parametrizedSql = preg_replace_callback(
359	1	'#([ (]+):([a-zA-Z0-9]+)#',
360		function ($match) use (&$newParameters, $userParameters, $paramsFormat) {
361	1	$newParameter = $userParameters[$match[2]];
362
363	1	if ($paramsFormat === self::PARAMS_FORMAT_BIND && is_array($newParameter)) {
364		// expand array parameter into string of parameters e.g. $1,$2,$3...
365	1	$output = $match[1];
366	1	foreach ($newParameter as $data) {
367	1	$newParameters[] = $data;
368	1	$output .= sprintf('$%d,', count($newParameters));
369		}
370	1	$output = substr($output, 0, -1);
371
372	1	return $output;
373		}
374
375	1	$newParameters[] = $newParameter;
376
377	1	if ($paramsFormat === self::PARAMS_FORMAT_BIND) {
378	1	return sprintf('%s$%d', $match[1], count($newParameters));
379		} elseif ($paramsFormat === self::PARAMS_FORMAT_DOCTRINE) {
380		return sprintf('%s?', $match[1]);
381		}
382		throw new InvalidParamsFormaterForQueryBuilderException;
383
384	1	},
385	1	$sql
386		);
387
388	1	$this->parameters = $newParameters;
389
390	1	return $parametrizedSql;
391		}
392
393		}
394

apicart / php-utils

Push — master ( cbb9d8...dc8c5b )

QueryBuilder::replaceParameters() B

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like