BaseQueryBuilderProvider::buildConditions() - Code Metrics - Inspection of "Rework tests #2, add tests `src/QueryBuilder/condi..." - yiisoft/db - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Pull Request — master (#377)

by Alexander

created 2022-11-02 22:33 UTC

BaseQueryBuilderProvider::buildConditions() B

↳ Parent: BaseQueryBuilderProvider

Complexity

Conditions	1
Paths	1

Size

Total Lines	309
Code Lines	176

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
cc	1
eloc	176
c	1
b	0
f	0
nc	1
nop	0
dl	0
loc	309
rs	8

How to fix Long Method

1			<?php
2
3			declare(strict_types=1);
4
5			namespace Yiisoft\Db\Tests\Provider;
6
7			use Yiisoft\Db\Expression\Expression;
8			use Yiisoft\Db\QueryBuilder\Conditions\BetweenColumnsCondition;
9			use Yiisoft\Db\QueryBuilder\Conditions\InCondition;
10			use Yiisoft\Db\Tests\Support\DbHelper;
11			use Yiisoft\Db\Tests\Support\Mock;
12			use Yiisoft\Db\Tests\Support\TraversableObject;
13
14			final class BaseQueryBuilderProvider
15			{
16			public function __construct(private Mock $mock)
17			{
18			}
19
20			public function buildConditions(): array
21			{
22			$conditions = [
23			/* empty values */
24			[['like', 'name', []], '0=1', []],
25			[['not like', 'name', []], '', []],
26			[['or like', 'name', []], '0=1', []],
27			[['or not like', 'name', []], '', []],
28
29			/* not */
30			[['not', ''], '', []],
31			[['not', 'name'], 'NOT (name)', []],
32			[
33			[
34			'not',
35			$this->mock->query()->select('exists')->from('some_table'),
36			],
37			'NOT ((SELECT [[exists]] FROM [[some_table]]))', [],
38			],
39
40			/* and */
41			[['and', '', ''], '', []],
42			[['and', '', 'id=2'], 'id=2', []],
43			[['and', 'id=1', 'id=2'], '(id=1) AND (id=2)', []],
44			[['and', 'type=1', ['or', 'id=1', 'id=2']], '(type=1) AND ((id=1) OR (id=2))', []],
45			[['and', 'id=1', new Expression('id=:qp0', [':qp0' => 2])], '(id=1) AND (id=:qp0)', [':qp0' => 2]],
46			[
47			[
48			'and',
49			['expired' => false],
50			$this->mock->query()->select('count(*) > 1')->from('queue'),
51			],
52			'([[expired]]=:qp0) AND ((SELECT count(*) > 1 FROM [[queue]]))',
53			[':qp0' => false],
54			],
55
56			/* or */
57			[['or', 'id=1', 'id=2'], '(id=1) OR (id=2)', []],
58			[['or', 'type=1', ['or', 'id=1', 'id=2']], '(type=1) OR ((id=1) OR (id=2))', []],
59			[['or', 'type=1', new Expression('id=:qp0', [':qp0' => 1])], '(type=1) OR (id=:qp0)', [':qp0' => 1]],
60
61			/* between */
62			[['between', 'id', 1, 10], '[[id]] BETWEEN :qp0 AND :qp1', [':qp0' => 1, ':qp1' => 10]],
63			[['not between', 'id', 1, 10], '[[id]] NOT BETWEEN :qp0 AND :qp1', [':qp0' => 1, ':qp1' => 10]],
64			[
65			['between', 'date', new Expression('(NOW() - INTERVAL 1 MONTH)'), new Expression('NOW()')],
66			'[[date]] BETWEEN (NOW() - INTERVAL 1 MONTH) AND NOW()',
67			[],
68			],
69			[
70			['between', 'date', new Expression('(NOW() - INTERVAL 1 MONTH)'), 123],
71			'[[date]] BETWEEN (NOW() - INTERVAL 1 MONTH) AND :qp0',
72			[':qp0' => 123],
73			],
74			[
75			['not between', 'date', new Expression('(NOW() - INTERVAL 1 MONTH)'), new Expression('NOW()')],
76			'[[date]] NOT BETWEEN (NOW() - INTERVAL 1 MONTH) AND NOW()',
77			[],
78			],
79			[
80			['not between', 'date', new Expression('(NOW() - INTERVAL 1 MONTH)'), 123],
81			'[[date]] NOT BETWEEN (NOW() - INTERVAL 1 MONTH) AND :qp0',
82			[':qp0' => 123],
83			],
84			[
85			new BetweenColumnsCondition('2018-02-11', 'BETWEEN', 'create_time', 'update_time'),
86			':qp0 BETWEEN [[create_time]] AND [[update_time]]',
87			[':qp0' => '2018-02-11'],
88			],
89			[
90			new BetweenColumnsCondition('2018-02-11', 'NOT BETWEEN', 'NOW()', 'update_time'),
91			':qp0 NOT BETWEEN NOW() AND [[update_time]]',
92			[':qp0' => '2018-02-11'],
93			],
94			[
95			new BetweenColumnsCondition(new Expression('NOW()'), 'BETWEEN', 'create_time', 'update_time'),
96			'NOW() BETWEEN [[create_time]] AND [[update_time]]',
97			[],
98			],
99			[
100			new BetweenColumnsCondition(new Expression('NOW()'), 'NOT BETWEEN', 'create_time', 'update_time'),
101			'NOW() NOT BETWEEN [[create_time]] AND [[update_time]]',
102			[],
103			],
104			[
105			new BetweenColumnsCondition(
106			new Expression('NOW()'),
107			'NOT BETWEEN',
108			$this->mock->query()->select('min_date')->from('some_table'),
109			'max_date'
110			),
111			'NOW() NOT BETWEEN (SELECT [[min_date]] FROM [[some_table]]) AND [[max_date]]',
112			[],
113			],
114			[
115			new BetweenColumnsCondition(
116			new Expression('NOW()'),
117			'NOT BETWEEN',
118			new Expression('min_date'),
119			$this->mock->query()->select('max_date')->from('some_table'),
120			),
121			'NOW() NOT BETWEEN min_date AND (SELECT [[max_date]] FROM [[some_table]])',
122			[],
123			],
124
125			/* in */
126			[
127			['in', 'id', [1, 2, $this->mock->query()->select('three')->from('digits')]],
128			'[[id]] IN (:qp0, :qp1, (SELECT [[three]] FROM [[digits]]))',
129			[':qp0' => 1, ':qp1' => 2],
130			],
131			[
132			['not in', 'id', [1, 2, 3]],
133			'[[id]] NOT IN (:qp0, :qp1, :qp2)',
134			[':qp0' => 1, ':qp1' => 2, ':qp2' => 3],
135			],
136			[
137			['in', 'id', $this->mock->query()->select('id')->from('users')->where(['active' => 1])],
138			'[[id]] IN (SELECT [[id]] FROM [[users]] WHERE [[active]]=:qp0)',
139			[':qp0' => 1],
140			],
141			[
142			['not in', 'id', $this->mock->query()->select('id')->from('users')->where(['active' => 1])],
143			'[[id]] NOT IN (SELECT [[id]] FROM [[users]] WHERE [[active]]=:qp0)',
144			[':qp0' => 1],
145			],
146			[['in', 'id', 1], '[[id]]=:qp0', [':qp0' => 1]],
147			[['in', 'id', [1]], '[[id]]=:qp0', [':qp0' => 1]],
148			[['in', 'id', new TraversableObject([1])], '[[id]]=:qp0', [':qp0' => 1]],
149			'composite in' => [
150			['in', ['id', 'name'], [['id' => 1, 'name' => 'oy']]],
151			'([[id]], [[name]]) IN ((:qp0, :qp1))',
152			[':qp0' => 1, ':qp1' => 'oy'],
153			],
154			'composite in (just one column)' => [
155			['in', ['id'], [['id' => 1, 'name' => 'Name1'], ['id' => 2, 'name' => 'Name2']]],
156			'[[id]] IN (:qp0, :qp1)',
157			[':qp0' => 1, ':qp1' => 2],
158			],
159			'composite in using array objects (just one column)' => [
160			[
161			'in',
162			new TraversableObject(['id']),
163			new TraversableObject([['id' => 1, 'name' => 'Name1'], ['id' => 2, 'name' => 'Name2']]),
164			],
165			'[[id]] IN (:qp0, :qp1)',
166			[':qp0' => 1, ':qp1' => 2],
167			],
168
169			/* in using array objects. */
170			[['id' => new TraversableObject([1, 2])], '[[id]] IN (:qp0, :qp1)', [':qp0' => 1, ':qp1' => 2]],
171			[
172			['in', 'id', new TraversableObject([1, 2, 3])],
173			'[[id]] IN (:qp0, :qp1, :qp2)',
174			[':qp0' => 1, ':qp1' => 2, ':qp2' => 3],
175			],
176
177			/* in using array objects containing null value */
178			[['in', 'id', new TraversableObject([1, null])], '[[id]]=:qp0 OR [[id]] IS NULL', [':qp0' => 1]],
179			[
180			['in', 'id', new TraversableObject([1, 2, null])],
181			'[[id]] IN (:qp0, :qp1) OR [[id]] IS NULL', [':qp0' => 1, ':qp1' => 2],
182			],
183
184			/* not in using array object containing null value */
185			[
186			['not in', 'id', new TraversableObject([1, null])],
187			'[[id]]<>:qp0 AND [[id]] IS NOT NULL', [':qp0' => 1],
188			],
189			[
190			['not in', 'id', new TraversableObject([1, 2, null])],
191			'[[id]] NOT IN (:qp0, :qp1) AND [[id]] IS NOT NULL',
192			[':qp0' => 1, ':qp1' => 2],
193			],
194
195			/* in using array object containing only null value */
196			[['in', 'id', new TraversableObject([null])], '[[id]] IS NULL', []],
197			[['not in', 'id', new TraversableObject([null])], '[[id]] IS NOT NULL', []],
198			'composite in using array objects' => [
199			[
200			'in',
201			new TraversableObject(['id', 'name']),
202			new TraversableObject([['id' => 1, 'name' => 'oy'], ['id' => 2, 'name' => 'yo']]),
203			],
204			'([[id]], [[name]]) IN ((:qp0, :qp1), (:qp2, :qp3))',
205			[':qp0' => 1, ':qp1' => 'oy', ':qp2' => 2, ':qp3' => 'yo'],
206			],
207
208			/* in object conditions */
209			[new InCondition('id', 'in', 1), '[[id]]=:qp0', [':qp0' => 1]],
210			[new InCondition('id', 'in', [1]), '[[id]]=:qp0', [':qp0' => 1]],
211			[new InCondition('id', 'not in', 1), '[[id]]<>:qp0', [':qp0' => 1]],
212			[new InCondition('id', 'not in', [1]), '[[id]]<>:qp0', [':qp0' => 1]],
213			[new InCondition('id', 'in', [1, 2]), '[[id]] IN (:qp0, :qp1)', [':qp0' => 1, ':qp1' => 2]],
214			[new InCondition('id', 'not in', [1, 2]), '[[id]] NOT IN (:qp0, :qp1)', [':qp0' => 1, ':qp1' => 2]],
215			[new InCondition([], 'in', 1), '0=1', []],
216			[new InCondition([], 'in', [1]), '0=1', []],
217			[new InCondition(['id', 'name'], 'in', []), '0=1', []],
218			[
219			new InCondition(
220			['id'],
221			'in',
222			$this->mock->query()->select('id')->from('users')->where(['active' => 1]),
223			),
224			'([[id]]) IN (SELECT [[id]] FROM [[users]] WHERE [[active]]=:qp0)',
225			[':qp0' => 1],
226			],
227			[
228			new InCondition(['id', 'name'], 'in', [['id' => 1]]),
229			'([[id]], [[name]]) IN ((:qp0, NULL))',
230			[':qp0' => 1],
231			],
232			[
233			new InCondition(['id', 'name'], 'in', [['name' => 'oy']]),
234			'([[id]], [[name]]) IN ((NULL, :qp0))',
235			[':qp0' => 'oy'],
236			],
237			[
238			new InCondition(['id', 'name'], 'in', [['id' => 1, 'name' => 'oy']]),
239			'([[id]], [[name]]) IN ((:qp0, :qp1))',
240			[':qp0' => 1, ':qp1' => 'oy'],
241			],
242
243			/* exists */
244			[
245			[
246			'exists',
247			$this->mock->query()->select('id')->from('users')->where(['active' => 1]),
248			],
249			'EXISTS (SELECT [[id]] FROM [[users]] WHERE [[active]]=:qp0)',
250			[':qp0' => 1],
251			],
252			[
253			['not exists', $this->mock->query()->select('id')->from('users')->where(['active' => 1])],
254			'NOT EXISTS (SELECT [[id]] FROM [[users]] WHERE [[active]]=:qp0)', [':qp0' => 1],
255			],
256
257			/* simple conditions */
258			[['=', 'a', 'b'], '[[a]] = :qp0', [':qp0' => 'b']],
259			[['>', 'a', 1], '[[a]] > :qp0', [':qp0' => 1]],
260			[['>=', 'a', 'b'], '[[a]] >= :qp0', [':qp0' => 'b']],
261			[['<', 'a', 2], '[[a]] < :qp0', [':qp0' => 2]],
262			[['<=', 'a', 'b'], '[[a]] <= :qp0', [':qp0' => 'b']],
263			[['<>', 'a', 3], '[[a]] <> :qp0', [':qp0' => 3]],
264			[['!=', 'a', 'b'], '[[a]] != :qp0', [':qp0' => 'b']],
265			[
266			['>=', 'date', new Expression('DATE_SUB(NOW(), INTERVAL 1 MONTH)')],
267			'[[date]] >= DATE_SUB(NOW(), INTERVAL 1 MONTH)',
268			[],
269			],
270			[
271			['>=', 'date', new Expression('DATE_SUB(NOW(), INTERVAL :month MONTH)', [':month' => 2])],
272			'[[date]] >= DATE_SUB(NOW(), INTERVAL :month MONTH)',
273			[':month' => 2],
274			],
275			[
276			['=', 'date', $this->mock->query()->select('max(date)')->from('test')->where(['id' => 5])],
277			'[[date]] = (SELECT max(date) FROM [[test]] WHERE [[id]]=:qp0)',
278			[':qp0' => 5],
279			],
280			[['=', 'a', null], '[[a]] = NULL', []],
281
282			/* operand1 is Expression */
283			[
284			['=', new Expression('date'), '2019-08-01'],
285			'date = :qp0',
286			[':qp0' => '2019-08-01'],
287			],
288			[
289			['=', $this->mock->query()->select('COUNT(*)')->from('test')->where(['id' => 6]), 0],
290			'(SELECT COUNT(*) FROM [[test]] WHERE [[id]]=:qp0) = :qp1',
291			[':qp0' => 6, ':qp1' => 0],
292			],
293
294			/* hash condition */
295			[['a' => 1, 'b' => 2], '([[a]]=:qp0) AND ([[b]]=:qp1)', [':qp0' => 1, ':qp1' => 2]],
296			[
297			['a' => new Expression('CONCAT(col1, col2)'), 'b' => 2],
298			'([[a]]=CONCAT(col1, col2)) AND ([[b]]=:qp0)',
299			[':qp0' => 2],
300			],
301			[['a' => null], '[[a]] IS NULL', []],
302
303			/* direct conditions */
304			['a = CONCAT(col1, col2)', 'a = CONCAT(col1, col2)', []],
305			[
306			new Expression('a = CONCAT(col1, :param1)', ['param1' => 'value1']),
307			'a = CONCAT(col1, :param1)',
308			['param1' => 'value1'],
309			],
310
311			/* Expression with params as operand of 'not' */
312			[
313			['not', new Expression('any_expression(:a)', [':a' => 1])],
314			'NOT (any_expression(:a))', [':a' => 1],
315			],
316			[new Expression('NOT (any_expression(:a))', [':a' => 1]), 'NOT (any_expression(:a))', [':a' => 1]],
317
318			/* like */
319			[['like', 'a', 'b'], '[[a]] LIKE :qp0', [':qp0' => '%b%']],
320			[
321			['like', 'a', new Expression(':qp0', [':qp0' => '%b%'])],
322			'[[a]] LIKE :qp0',
323			[':qp0' => '%b%'],
324			],
325			[['like', new Expression('CONCAT(col1, col2)'), 'b'], 'CONCAT(col1, col2) LIKE :qp0', [':qp0' => '%b%']],
326			];
327
328			return $this->replaceQuotes($conditions);
329			}
330
331			public function buildFilterCondition(): array
332			{
333			$conditions = [
334			/* like */
335			[['like', 'name', []], '', []],
336			[['not like', 'name', []], '', []],
337			[['or like', 'name', []], '', []],
338			[['or not like', 'name', []], '', []],
339
340			/* not */
341			[['not', ''], '', []],
342
343			/* and */
344			[['and', '', ''], '', []],
345			[['and', '', 'id=2'], 'id=2', []],
346			[['and', 'id=1', ''], 'id=1', []],
347			[['and', 'type=1', ['or', '', 'id=2']], '(type=1) AND (id=2)', []],
348
349			/* or */
350			[['or', 'id=1', ''], 'id=1', []],
351			[['or', 'type=1', ['or', '', 'id=2']], '(type=1) OR (id=2)', []],
352
353			/* between */
354			[['between', 'id', 1, null], '', []],
355			[['not between', 'id', null, 10], '', []],
356
357			/* in */
358			[['in', 'id', []], '', []],
359			[['not in', 'id', []], '', []],
360
361			/* simple conditions */
362			[['=', 'a', ''], '', []],
363			[['>', 'a', ''], '', []],
364			[['>=', 'a', ''], '', []],
365			[['<', 'a', ''], '', []],
366			[['<=', 'a', ''], '', []],
367			[['<>', 'a', ''], '', []],
368			[['!=', 'a', ''], '', []],
369			];
370
371			return $this->replaceQuotes($conditions);
372			}
373
374			public function buildWhereExists(): array
375			{
376			return [
377			[
378			'exists',
379			DbHelper::replaceQuotes(
380			<<<SQL
381			SELECT [[id]] FROM [[TotalExample]] [[t]] WHERE EXISTS (SELECT [[1]] FROM [[Website]] [[w]])
382			SQL,
383			$this->mock->getDriverName(),
384			),
385			],
386			[
387			'not exists',
388			DbHelper::replaceQuotes(
389			<<<SQL
390			SELECT [[id]] FROM [[TotalExample]] [[t]] WHERE NOT EXISTS (SELECT [[1]] FROM [[Website]] [[w]])
391			SQL,
392			$this->mock->getDriverName(),
393			),
394			],
395			];
396			}
397
398			private function replaceQuotes(array $conditions): array
399			{
400			/* adjust dbms specific escaping */
401			foreach ($conditions as $i => $condition) {
402			$conditions[$i][1] = DbHelper::replaceQuotes($condition[1], $this->mock->getDriverName());
403			}
404
405			return $conditions;
406			}
407			}
408

yiisoft / db

Pull Request — master (#377)

BaseQueryBuilderProvider::buildConditions() B

Complexity

Size

Duplication

Importance

How to fix Long Method

Long Method

Duplication Side-by-Side

Filter issues like