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

Passed

Pull Request — master (#380)

by Alexander

created 2022-11-02 23:57 UTC

BaseQueryBuilderProvider::__construct() A

↳ Parent: BaseQueryBuilderProvider

Complexity

Conditions	1
Paths	1

Size

Total Lines	2
Code Lines	0

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
cc	1
eloc	0
c	1
b	0
f	0
nc	1
nop	1
dl	0
loc	2
rs	10

<?php

declare(strict_types=1);

namespace Yiisoft\Db\Tests\Provider;

use Yiisoft\Db\Expression\Expression;
use Yiisoft\Db\QueryBuilder\Conditions\BetweenColumnsCondition;
use Yiisoft\Db\QueryBuilder\Conditions\InCondition;
use Yiisoft\Db\Tests\Support\DbHelper;
use Yiisoft\Db\Tests\Support\Mock;
use Yiisoft\Db\Tests\Support\TraversableObject;

final class BaseQueryBuilderProvider
{
    public function __construct(private Mock $mock)
    {
    }

    public function batchInsert(): array
    {
        return [
            'simple' => [
                'customer',
                ['email', 'name', 'address'],
                [['[email protected]', 'silverfire', 'Kyiv {{city}}, Ukraine']],
                'expected' => DbHelper::replaceQuotes(
                    <<<SQL
                    INSERT INTO [[customer]] ([[email]], [[name]], [[address]]) VALUES (:qp0, :qp1, :qp2)
                    SQL,
                    $this->mock->getDriverName(),
                ),
                [
                    ':qp0' => '[email protected]',
                    ':qp1' => 'silverfire',
                    ':qp2' => 'Kyiv {{city}}, Ukraine',
                ],
            ],
            'escape-danger-chars' => [
                'customer',
                ['address'],
                [["SQL-danger chars are escaped: '); --"]],
                'expected' => DbHelper::replaceQuotes(
                    <<<SQL
                    INSERT INTO [[customer]] ([[address]]) VALUES (:qp0)
                    SQL,
                    $this->mock->getDriverName(),
                ),
                [
                    ':qp0' => "SQL-danger chars are escaped: '); --",
                ],
            ],
            'customer2' => [
                'customer',
                ['address'],
                [],
                '',
            ],
            'customer3' => [
                'customer',
                [],
                [['no columns passed']],
                'expected' => DbHelper::replaceQuotes(
                    <<<SQL
                    INSERT INTO [[customer]] () VALUES (:qp0)
                    SQL,
                    $this->mock->getDriverName(),
                ),
                [
                    ':qp0' => 'no columns passed',
                ],
            ],
            'bool-false, bool2-null' => [
                'type',
                ['bool_col', 'bool_col2'],
                [[false, null]],
                'expected' => DbHelper::replaceQuotes(
                    <<<SQL
                    INSERT INTO [[type]] ([[bool_col]], [[bool_col2]]) VALUES (:qp0, :qp1)
                    SQL,
                    $this->mock->getDriverName(),
                ),
                [
                    ':qp0' => false,
                    ':qp1' => null,
                ],
            ],
            'wrong' => [
                '{{%type}}',
                ['{{%type}}.[[float_col]]', '[[time]]'],
                [[null, new Expression('now()')], [null, new Expression('now()')]],
                'expected' => 'INSERT INTO {{%type}} ({{%type}}.[[float_col]], [[time]]) VALUES (:qp0, now()), (:qp1, now())',
                [
                    ':qp0' => null,
                    ':qp1' => null,
                ],
            ],
            'bool-false, time-now()' => [
                '{{%type}}',
                ['{{%type}}.[[bool_col]]', '[[time]]'],
                [[false, new Expression('now()')]],
                'expected' => 'INSERT INTO {{%type}} ({{%type}}.[[bool_col]], [[time]]) VALUES (:qp0, now())',
                [
                    ':qp0' => false,
                ],
            ],
        ];
    }

    public function buildConditions(): array
    {
        $conditions = [
            /* empty values */
            [['like', 'name', []], '0=1', []],
            [['not like', 'name', []], '', []],
            [['or like', 'name', []], '0=1', []],
            [['or not like', 'name', []], '', []],

            /* not */
            [['not', ''], '', []],
            [['not', 'name'], 'NOT (name)', []],
            [
                [
                    'not',
                    $this->mock->query()->select('exists')->from('some_table'),
                ],
                'NOT ((SELECT [[exists]] FROM [[some_table]]))', [],
            ],

            /* and */
            [['and', '', ''], '', []],
            [['and', '', 'id=2'], 'id=2', []],
            [['and', 'id=1', 'id=2'], '(id=1) AND (id=2)', []],
            [['and', 'type=1', ['or', 'id=1', 'id=2']], '(type=1) AND ((id=1) OR (id=2))', []],
            [['and', 'id=1', new Expression('id=:qp0', [':qp0' => 2])], '(id=1) AND (id=:qp0)', [':qp0' => 2]],
            [
                [
                    'and',
                    ['expired' => false],
                    $this->mock->query()->select('count(*) > 1')->from('queue'),
                ],
                '([[expired]]=:qp0) AND ((SELECT count(*) > 1 FROM [[queue]]))',
                [':qp0' => false],
            ],

            /* or */
            [['or', 'id=1', 'id=2'], '(id=1) OR (id=2)', []],
            [['or', 'type=1', ['or', 'id=1', 'id=2']], '(type=1) OR ((id=1) OR (id=2))', []],
            [['or', 'type=1', new Expression('id=:qp0', [':qp0' => 1])], '(type=1) OR (id=:qp0)', [':qp0' => 1]],

            /* between */
            [['between', 'id', 1, 10], '[[id]] BETWEEN :qp0 AND :qp1', [':qp0' => 1, ':qp1' => 10]],
            [['not between', 'id', 1, 10], '[[id]] NOT BETWEEN :qp0 AND :qp1', [':qp0' => 1, ':qp1' => 10]],
            [
                ['between', 'date', new Expression('(NOW() - INTERVAL 1 MONTH)'), new Expression('NOW()')],
                '[[date]] BETWEEN (NOW() - INTERVAL 1 MONTH) AND NOW()',
                [],
            ],
            [
                ['between', 'date', new Expression('(NOW() - INTERVAL 1 MONTH)'), 123],
                '[[date]] BETWEEN (NOW() - INTERVAL 1 MONTH) AND :qp0',
                [':qp0' => 123],
            ],
            [
                ['not between', 'date', new Expression('(NOW() - INTERVAL 1 MONTH)'), new Expression('NOW()')],
                '[[date]] NOT BETWEEN (NOW() - INTERVAL 1 MONTH) AND NOW()',
                [],
            ],
            [
                ['not between', 'date', new Expression('(NOW() - INTERVAL 1 MONTH)'), 123],
                '[[date]] NOT BETWEEN (NOW() - INTERVAL 1 MONTH) AND :qp0',
                [':qp0' => 123],
            ],
            [
                new BetweenColumnsCondition('2018-02-11', 'BETWEEN', 'create_time', 'update_time'),
                ':qp0 BETWEEN [[create_time]] AND [[update_time]]',
                [':qp0' => '2018-02-11'],
            ],
            [
                new BetweenColumnsCondition('2018-02-11', 'NOT BETWEEN', 'NOW()', 'update_time'),
                ':qp0 NOT BETWEEN NOW() AND [[update_time]]',
                [':qp0' => '2018-02-11'],
            ],
            [
                new BetweenColumnsCondition(new Expression('NOW()'), 'BETWEEN', 'create_time', 'update_time'),
                'NOW() BETWEEN [[create_time]] AND [[update_time]]',
                [],
            ],
            [
                new BetweenColumnsCondition(new Expression('NOW()'), 'NOT BETWEEN', 'create_time', 'update_time'),
                'NOW() NOT BETWEEN [[create_time]] AND [[update_time]]',
                [],
            ],
            [
                new BetweenColumnsCondition(
                    new Expression('NOW()'),
                    'NOT BETWEEN',
                    $this->mock->query()->select('min_date')->from('some_table'),
                    'max_date'
                ),
                'NOW() NOT BETWEEN (SELECT [[min_date]] FROM [[some_table]]) AND [[max_date]]',
                [],
            ],
            [
                new BetweenColumnsCondition(
                    new Expression('NOW()'),
                    'NOT BETWEEN',
                    new Expression('min_date'),
                    $this->mock->query()->select('max_date')->from('some_table'),
                ),
                'NOW() NOT BETWEEN min_date AND (SELECT [[max_date]] FROM [[some_table]])',
                [],
            ],

            /* in */
            [
                ['in', 'id', [1, 2, $this->mock->query()->select('three')->from('digits')]],
                '[[id]] IN (:qp0, :qp1, (SELECT [[three]] FROM [[digits]]))',
                [':qp0' => 1, ':qp1' => 2],
            ],
            [
                ['not in', 'id', [1, 2, 3]],
                '[[id]] NOT IN (:qp0, :qp1, :qp2)',
                [':qp0' => 1, ':qp1' => 2, ':qp2' => 3],
            ],
            [
                ['in', 'id', $this->mock->query()->select('id')->from('users')->where(['active' => 1])],
                '[[id]] IN (SELECT [[id]] FROM [[users]] WHERE [[active]]=:qp0)',
                [':qp0' => 1],
            ],
            [
                ['not in', 'id', $this->mock->query()->select('id')->from('users')->where(['active' => 1])],
                '[[id]] NOT IN (SELECT [[id]] FROM [[users]] WHERE [[active]]=:qp0)',
                [':qp0' => 1],
            ],
            [['in', 'id', 1], '[[id]]=:qp0', [':qp0' => 1]],
            [['in', 'id', [1]], '[[id]]=:qp0', [':qp0' => 1]],
            [['in', 'id', new TraversableObject([1])], '[[id]]=:qp0', [':qp0' => 1]],
            'composite in' => [
                ['in', ['id', 'name'], [['id' => 1, 'name' => 'oy']]],
                '([[id]], [[name]]) IN ((:qp0, :qp1))',
                [':qp0' => 1, ':qp1' => 'oy'],
            ],
            'composite in (just one column)' => [
                ['in', ['id'], [['id' => 1, 'name' => 'Name1'], ['id' => 2, 'name' => 'Name2']]],
                '[[id]] IN (:qp0, :qp1)',
                [':qp0' => 1, ':qp1' => 2],
            ],
            'composite in using array objects (just one column)' => [
                [
                    'in',
                    new TraversableObject(['id']),
                    new TraversableObject([['id' => 1, 'name' => 'Name1'], ['id' => 2, 'name' => 'Name2']]),
                ],
                '[[id]] IN (:qp0, :qp1)',
                [':qp0' => 1, ':qp1' => 2],
            ],

            /* in using array objects. */
            [['id' => new TraversableObject([1, 2])], '[[id]] IN (:qp0, :qp1)', [':qp0' => 1, ':qp1' => 2]],
            [
                ['in', 'id', new TraversableObject([1, 2, 3])],
                '[[id]] IN (:qp0, :qp1, :qp2)',
                [':qp0' => 1, ':qp1' => 2, ':qp2' => 3],
            ],

            /* in using array objects containing null value */
            [['in', 'id', new TraversableObject([1, null])], '[[id]]=:qp0 OR [[id]] IS NULL', [':qp0' => 1]],
            [
                ['in', 'id', new TraversableObject([1, 2, null])],
                '[[id]] IN (:qp0, :qp1) OR [[id]] IS NULL', [':qp0' => 1, ':qp1' => 2],
            ],

            /* not in using array object containing null value */
            [
                ['not in', 'id', new TraversableObject([1, null])],
                '[[id]]<>:qp0 AND [[id]] IS NOT NULL', [':qp0' => 1],
            ],
            [
                ['not in', 'id', new TraversableObject([1, 2, null])],
                '[[id]] NOT IN (:qp0, :qp1) AND [[id]] IS NOT NULL',
                [':qp0' => 1, ':qp1' => 2],
            ],

            /* in using array object containing only null value */
            [['in', 'id', new TraversableObject([null])], '[[id]] IS NULL', []],
            [['not in', 'id', new TraversableObject([null])], '[[id]] IS NOT NULL', []],
            'composite in using array objects' => [
                [
                    'in',
                    new TraversableObject(['id', 'name']),
                    new TraversableObject([['id' => 1, 'name' => 'oy'], ['id' => 2, 'name' => 'yo']]),
                ],
                '([[id]], [[name]]) IN ((:qp0, :qp1), (:qp2, :qp3))',
                [':qp0' => 1, ':qp1' => 'oy', ':qp2' => 2, ':qp3' => 'yo'],
            ],

            /* in object conditions */
            [new InCondition('id', 'in', 1), '[[id]]=:qp0', [':qp0' => 1]],
            [new InCondition('id', 'in', [1]), '[[id]]=:qp0', [':qp0' => 1]],
            [new InCondition('id', 'not in', 1), '[[id]]<>:qp0', [':qp0' => 1]],
            [new InCondition('id', 'not in', [1]), '[[id]]<>:qp0', [':qp0' => 1]],
            [new InCondition('id', 'in', [1, 2]), '[[id]] IN (:qp0, :qp1)', [':qp0' => 1, ':qp1' => 2]],
            [new InCondition('id', 'not in', [1, 2]), '[[id]] NOT IN (:qp0, :qp1)', [':qp0' => 1, ':qp1' => 2]],
            [new InCondition([], 'in', 1), '0=1', []],
            [new InCondition([], 'in', [1]), '0=1', []],
            [new InCondition(['id', 'name'], 'in', []), '0=1', []],
            [
                new InCondition(
                    ['id'],
                    'in',
                    $this->mock->query()->select('id')->from('users')->where(['active' => 1]),
                ),
                '([[id]]) IN (SELECT [[id]] FROM [[users]] WHERE [[active]]=:qp0)',
                [':qp0' => 1],
            ],
            [
                new InCondition(['id', 'name'], 'in', [['id' => 1]]),
                '([[id]], [[name]]) IN ((:qp0, NULL))',
                [':qp0' => 1],
            ],
            [
                new InCondition(['id', 'name'], 'in', [['name' => 'oy']]),
                '([[id]], [[name]]) IN ((NULL, :qp0))',
                [':qp0' => 'oy'],
            ],
            [
                new InCondition(['id', 'name'], 'in', [['id' => 1, 'name' => 'oy']]),
                '([[id]], [[name]]) IN ((:qp0, :qp1))',
                [':qp0' => 1, ':qp1' => 'oy'],
            ],

            /* exists */
            [
                [
                    'exists',
                    $this->mock->query()->select('id')->from('users')->where(['active' => 1]),
                ],
                'EXISTS (SELECT [[id]] FROM [[users]] WHERE [[active]]=:qp0)',
                [':qp0' => 1],
            ],
            [
                ['not exists', $this->mock->query()->select('id')->from('users')->where(['active' => 1])],
                'NOT EXISTS (SELECT [[id]] FROM [[users]] WHERE [[active]]=:qp0)', [':qp0' => 1],
            ],

            /* simple conditions */
            [['=', 'a', 'b'], '[[a]] = :qp0', [':qp0' => 'b']],
            [['>', 'a', 1], '[[a]] > :qp0', [':qp0' => 1]],
            [['>=', 'a', 'b'], '[[a]] >= :qp0', [':qp0' => 'b']],
            [['<', 'a', 2], '[[a]] < :qp0', [':qp0' => 2]],
            [['<=', 'a', 'b'], '[[a]] <= :qp0', [':qp0' => 'b']],
            [['<>', 'a', 3], '[[a]] <> :qp0', [':qp0' => 3]],
            [['!=', 'a', 'b'], '[[a]] != :qp0', [':qp0' => 'b']],
            [
                ['>=', 'date', new Expression('DATE_SUB(NOW(), INTERVAL 1 MONTH)')],
                '[[date]] >= DATE_SUB(NOW(), INTERVAL 1 MONTH)',
                [],
            ],
            [
                ['>=', 'date', new Expression('DATE_SUB(NOW(), INTERVAL :month MONTH)', [':month' => 2])],
                '[[date]] >= DATE_SUB(NOW(), INTERVAL :month MONTH)',
                [':month' => 2],
            ],
            [
                ['=', 'date', $this->mock->query()->select('max(date)')->from('test')->where(['id' => 5])],
                '[[date]] = (SELECT max(date) FROM [[test]] WHERE [[id]]=:qp0)',
                [':qp0' => 5],
            ],
            [['=', 'a', null], '[[a]] = NULL', []],

            /* operand1 is Expression */
            [
                ['=', new Expression('date'), '2019-08-01'],
                'date = :qp0',
                [':qp0' => '2019-08-01'],
            ],
            [
                ['=', $this->mock->query()->select('COUNT(*)')->from('test')->where(['id' => 6]), 0],
                '(SELECT COUNT(*) FROM [[test]] WHERE [[id]]=:qp0) = :qp1',
                [':qp0' => 6, ':qp1' => 0],
            ],

            /* hash condition */
            [['a' => 1, 'b' => 2], '([[a]]=:qp0) AND ([[b]]=:qp1)', [':qp0' => 1, ':qp1' => 2]],
            [
                ['a' => new Expression('CONCAT(col1, col2)'), 'b' => 2],
                '([[a]]=CONCAT(col1, col2)) AND ([[b]]=:qp0)',
                [':qp0' => 2],
            ],
            [['a' => null], '[[a]] IS NULL', []],

            /* direct conditions */
            ['a = CONCAT(col1, col2)', 'a = CONCAT(col1, col2)', []],
            [
                new Expression('a = CONCAT(col1, :param1)', ['param1' => 'value1']),
                'a = CONCAT(col1, :param1)',
                ['param1' => 'value1'],
            ],

            /* Expression with params as operand of 'not' */
            [
                ['not', new Expression('any_expression(:a)', [':a' => 1])],
                'NOT (any_expression(:a))', [':a' => 1],
            ],
            [new Expression('NOT (any_expression(:a))', [':a' => 1]), 'NOT (any_expression(:a))', [':a' => 1]],

            /* like */
            [['like', 'a', 'b'], '[[a]] LIKE :qp0', [':qp0' => '%b%']],
            [
                ['like', 'a', new Expression(':qp0', [':qp0' => '%b%'])],
                '[[a]] LIKE :qp0',
                [':qp0' => '%b%'],
            ],
            [['like', new Expression('CONCAT(col1, col2)'), 'b'], 'CONCAT(col1, col2) LIKE :qp0', [':qp0' => '%b%']],
        ];

        return $this->replaceQuotes($conditions);
    }

    public function buildFilterCondition(): array
    {
        $conditions = [
            /* like */
            [['like', 'name', []], '', []],
            [['not like', 'name', []], '', []],
            [['or like', 'name', []], '', []],
            [['or not like', 'name', []], '', []],

            /* not */
            [['not', ''], '', []],

            /* and */
            [['and', '', ''], '', []],
            [['and', '', 'id=2'], 'id=2', []],
            [['and', 'id=1', ''], 'id=1', []],
            [['and', 'type=1', ['or', '', 'id=2']], '(type=1) AND (id=2)', []],

            /* or */
            [['or', 'id=1', ''], 'id=1', []],
            [['or', 'type=1', ['or', '', 'id=2']], '(type=1) OR (id=2)', []],

            /* between */
            [['between', 'id', 1, null], '', []],
            [['not between', 'id', null, 10], '', []],

            /* in */
            [['in', 'id', []], '', []],
            [['not in', 'id', []], '', []],

            /* simple conditions */
            [['=', 'a', ''], '', []],
            [['>', 'a', ''], '', []],
            [['>=', 'a', ''], '', []],
            [['<', 'a', ''], '', []],
            [['<=', 'a', ''], '', []],
            [['<>', 'a', ''], '', []],
            [['!=', 'a', ''], '', []],
        ];

        return $this->replaceQuotes($conditions);
    }

    public function buildWhereExists(): array
    {
        return [
            [
                'exists',
                DbHelper::replaceQuotes(
                    <<<SQL
                    SELECT [[id]] FROM [[TotalExample]] [[t]] WHERE EXISTS (SELECT [[1]] FROM [[Website]] [[w]])
                    SQL,
                    $this->mock->getDriverName(),
                ),
            ],
            [
                'not exists',
                DbHelper::replaceQuotes(
                    <<<SQL
                    SELECT [[id]] FROM [[TotalExample]] [[t]] WHERE NOT EXISTS (SELECT [[1]] FROM [[Website]] [[w]])
                    SQL,
                    $this->mock->getDriverName(),
                ),
            ],
        ];
    }

    private function replaceQuotes(array $conditions): array
    {
        /* adjust dbms specific escaping */
        foreach ($conditions as $i => $condition) {
            $conditions[$i][1] = DbHelper::replaceQuotes($condition[1], $this->mock->getDriverName());
        }

        return $conditions;
    }
}


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 batchInsert(): array
21			{
22			return [
23			'simple' => [
24			'customer',
25			['email', 'name', 'address'],
26			[['[email protected]', 'silverfire', 'Kyiv {{city}}, Ukraine']],
27			'expected' => DbHelper::replaceQuotes(
28			<<<SQL
29			INSERT INTO [[customer]] ([[email]], [[name]], [[address]]) VALUES (:qp0, :qp1, :qp2)
30			SQL,
31			$this->mock->getDriverName(),
32			),
33			[
34			':qp0' => '[email protected]',
35			':qp1' => 'silverfire',
36			':qp2' => 'Kyiv {{city}}, Ukraine',
37			],
38			],
39			'escape-danger-chars' => [
40			'customer',
41			['address'],
42			[["SQL-danger chars are escaped: '); --"]],
43			'expected' => DbHelper::replaceQuotes(
44			<<<SQL
45			INSERT INTO [[customer]] ([[address]]) VALUES (:qp0)
46			SQL,
47			$this->mock->getDriverName(),
48			),
49			[
50			':qp0' => "SQL-danger chars are escaped: '); --",
51			],
52			],
53			'customer2' => [
54			'customer',
55			['address'],
56			[],
57			'',
58			],
59			'customer3' => [
60			'customer',
61			[],
62			[['no columns passed']],
63			'expected' => DbHelper::replaceQuotes(
64			<<<SQL
65			INSERT INTO [[customer]] () VALUES (:qp0)
66			SQL,
67			$this->mock->getDriverName(),
68			),
69			[
70			':qp0' => 'no columns passed',
71			],
72			],
73			'bool-false, bool2-null' => [
74			'type',
75			['bool_col', 'bool_col2'],
76			[[false, null]],
77			'expected' => DbHelper::replaceQuotes(
78			<<<SQL
79			INSERT INTO [[type]] ([[bool_col]], [[bool_col2]]) VALUES (:qp0, :qp1)
80			SQL,
81			$this->mock->getDriverName(),
82			),
83			[
84			':qp0' => false,
85			':qp1' => null,
86			],
87			],
88			'wrong' => [
89			'{{%type}}',
90			['{{%type}}.[[float_col]]', '[[time]]'],
91			[[null, new Expression('now()')], [null, new Expression('now()')]],
92			'expected' => 'INSERT INTO {{%type}} ({{%type}}.[[float_col]], [[time]]) VALUES (:qp0, now()), (:qp1, now())',
93			[
94			':qp0' => null,
95			':qp1' => null,
96			],
97			],
98			'bool-false, time-now()' => [
99			'{{%type}}',
100			['{{%type}}.[[bool_col]]', '[[time]]'],
101			[[false, new Expression('now()')]],
102			'expected' => 'INSERT INTO {{%type}} ({{%type}}.[[bool_col]], [[time]]) VALUES (:qp0, now())',
103			[
104			':qp0' => false,
105			],
106			],
107			];
108			}
109
110			public function buildConditions(): array
111			{
112			$conditions = [
113			/* empty values */
114			[['like', 'name', []], '0=1', []],
115			[['not like', 'name', []], '', []],
116			[['or like', 'name', []], '0=1', []],
117			[['or not like', 'name', []], '', []],
118
119			/* not */
120			[['not', ''], '', []],
121			[['not', 'name'], 'NOT (name)', []],
122			[
123			[
124			'not',
125			$this->mock->query()->select('exists')->from('some_table'),
126			],
127			'NOT ((SELECT [[exists]] FROM [[some_table]]))', [],
128			],
129
130			/* and */
131			[['and', '', ''], '', []],
132			[['and', '', 'id=2'], 'id=2', []],
133			[['and', 'id=1', 'id=2'], '(id=1) AND (id=2)', []],
134			[['and', 'type=1', ['or', 'id=1', 'id=2']], '(type=1) AND ((id=1) OR (id=2))', []],
135			[['and', 'id=1', new Expression('id=:qp0', [':qp0' => 2])], '(id=1) AND (id=:qp0)', [':qp0' => 2]],
136			[
137			[
138			'and',
139			['expired' => false],
140			$this->mock->query()->select('count(*) > 1')->from('queue'),
141			],
142			'([[expired]]=:qp0) AND ((SELECT count(*) > 1 FROM [[queue]]))',
143			[':qp0' => false],
144			],
145
146			/* or */
147			[['or', 'id=1', 'id=2'], '(id=1) OR (id=2)', []],
148			[['or', 'type=1', ['or', 'id=1', 'id=2']], '(type=1) OR ((id=1) OR (id=2))', []],
149			[['or', 'type=1', new Expression('id=:qp0', [':qp0' => 1])], '(type=1) OR (id=:qp0)', [':qp0' => 1]],
150
151			/* between */
152			[['between', 'id', 1, 10], '[[id]] BETWEEN :qp0 AND :qp1', [':qp0' => 1, ':qp1' => 10]],
153			[['not between', 'id', 1, 10], '[[id]] NOT BETWEEN :qp0 AND :qp1', [':qp0' => 1, ':qp1' => 10]],
154			[
155			['between', 'date', new Expression('(NOW() - INTERVAL 1 MONTH)'), new Expression('NOW()')],
156			'[[date]] BETWEEN (NOW() - INTERVAL 1 MONTH) AND NOW()',
157			[],
158			],
159			[
160			['between', 'date', new Expression('(NOW() - INTERVAL 1 MONTH)'), 123],
161			'[[date]] BETWEEN (NOW() - INTERVAL 1 MONTH) AND :qp0',
162			[':qp0' => 123],
163			],
164			[
165			['not between', 'date', new Expression('(NOW() - INTERVAL 1 MONTH)'), new Expression('NOW()')],
166			'[[date]] NOT BETWEEN (NOW() - INTERVAL 1 MONTH) AND NOW()',
167			[],
168			],
169			[
170			['not between', 'date', new Expression('(NOW() - INTERVAL 1 MONTH)'), 123],
171			'[[date]] NOT BETWEEN (NOW() - INTERVAL 1 MONTH) AND :qp0',
172			[':qp0' => 123],
173			],
174			[
175			new BetweenColumnsCondition('2018-02-11', 'BETWEEN', 'create_time', 'update_time'),
176			':qp0 BETWEEN [[create_time]] AND [[update_time]]',
177			[':qp0' => '2018-02-11'],
178			],
179			[
180			new BetweenColumnsCondition('2018-02-11', 'NOT BETWEEN', 'NOW()', 'update_time'),
181			':qp0 NOT BETWEEN NOW() AND [[update_time]]',
182			[':qp0' => '2018-02-11'],
183			],
184			[
185			new BetweenColumnsCondition(new Expression('NOW()'), 'BETWEEN', 'create_time', 'update_time'),
186			'NOW() BETWEEN [[create_time]] AND [[update_time]]',
187			[],
188			],
189			[
190			new BetweenColumnsCondition(new Expression('NOW()'), 'NOT BETWEEN', 'create_time', 'update_time'),
191			'NOW() NOT BETWEEN [[create_time]] AND [[update_time]]',
192			[],
193			],
194			[
195			new BetweenColumnsCondition(
196			new Expression('NOW()'),
197			'NOT BETWEEN',
198			$this->mock->query()->select('min_date')->from('some_table'),
199			'max_date'
200			),
201			'NOW() NOT BETWEEN (SELECT [[min_date]] FROM [[some_table]]) AND [[max_date]]',
202			[],
203			],
204			[
205			new BetweenColumnsCondition(
206			new Expression('NOW()'),
207			'NOT BETWEEN',
208			new Expression('min_date'),
209			$this->mock->query()->select('max_date')->from('some_table'),
210			),
211			'NOW() NOT BETWEEN min_date AND (SELECT [[max_date]] FROM [[some_table]])',
212			[],
213			],
214
215			/* in */
216			[
217			['in', 'id', [1, 2, $this->mock->query()->select('three')->from('digits')]],
218			'[[id]] IN (:qp0, :qp1, (SELECT [[three]] FROM [[digits]]))',
219			[':qp0' => 1, ':qp1' => 2],
220			],
221			[
222			['not in', 'id', [1, 2, 3]],
223			'[[id]] NOT IN (:qp0, :qp1, :qp2)',
224			[':qp0' => 1, ':qp1' => 2, ':qp2' => 3],
225			],
226			[
227			['in', 'id', $this->mock->query()->select('id')->from('users')->where(['active' => 1])],
228			'[[id]] IN (SELECT [[id]] FROM [[users]] WHERE [[active]]=:qp0)',
229			[':qp0' => 1],
230			],
231			[
232			['not in', 'id', $this->mock->query()->select('id')->from('users')->where(['active' => 1])],
233			'[[id]] NOT IN (SELECT [[id]] FROM [[users]] WHERE [[active]]=:qp0)',
234			[':qp0' => 1],
235			],
236			[['in', 'id', 1], '[[id]]=:qp0', [':qp0' => 1]],
237			[['in', 'id', [1]], '[[id]]=:qp0', [':qp0' => 1]],
238			[['in', 'id', new TraversableObject([1])], '[[id]]=:qp0', [':qp0' => 1]],
239			'composite in' => [
240			['in', ['id', 'name'], [['id' => 1, 'name' => 'oy']]],
241			'([[id]], [[name]]) IN ((:qp0, :qp1))',
242			[':qp0' => 1, ':qp1' => 'oy'],
243			],
244			'composite in (just one column)' => [
245			['in', ['id'], [['id' => 1, 'name' => 'Name1'], ['id' => 2, 'name' => 'Name2']]],
246			'[[id]] IN (:qp0, :qp1)',
247			[':qp0' => 1, ':qp1' => 2],
248			],
249			'composite in using array objects (just one column)' => [
250			[
251			'in',
252			new TraversableObject(['id']),
253			new TraversableObject([['id' => 1, 'name' => 'Name1'], ['id' => 2, 'name' => 'Name2']]),
254			],
255			'[[id]] IN (:qp0, :qp1)',
256			[':qp0' => 1, ':qp1' => 2],
257			],
258
259			/* in using array objects. */
260			[['id' => new TraversableObject([1, 2])], '[[id]] IN (:qp0, :qp1)', [':qp0' => 1, ':qp1' => 2]],
261			[
262			['in', 'id', new TraversableObject([1, 2, 3])],
263			'[[id]] IN (:qp0, :qp1, :qp2)',
264			[':qp0' => 1, ':qp1' => 2, ':qp2' => 3],
265			],
266
267			/* in using array objects containing null value */
268			[['in', 'id', new TraversableObject([1, null])], '[[id]]=:qp0 OR [[id]] IS NULL', [':qp0' => 1]],
269			[
270			['in', 'id', new TraversableObject([1, 2, null])],
271			'[[id]] IN (:qp0, :qp1) OR [[id]] IS NULL', [':qp0' => 1, ':qp1' => 2],
272			],
273
274			/* not in using array object containing null value */
275			[
276			['not in', 'id', new TraversableObject([1, null])],
277			'[[id]]<>:qp0 AND [[id]] IS NOT NULL', [':qp0' => 1],
278			],
279			[
280			['not in', 'id', new TraversableObject([1, 2, null])],
281			'[[id]] NOT IN (:qp0, :qp1) AND [[id]] IS NOT NULL',
282			[':qp0' => 1, ':qp1' => 2],
283			],
284
285			/* in using array object containing only null value */
286			[['in', 'id', new TraversableObject([null])], '[[id]] IS NULL', []],
287			[['not in', 'id', new TraversableObject([null])], '[[id]] IS NOT NULL', []],
288			'composite in using array objects' => [
289			[
290			'in',
291			new TraversableObject(['id', 'name']),
292			new TraversableObject([['id' => 1, 'name' => 'oy'], ['id' => 2, 'name' => 'yo']]),
293			],
294			'([[id]], [[name]]) IN ((:qp0, :qp1), (:qp2, :qp3))',
295			[':qp0' => 1, ':qp1' => 'oy', ':qp2' => 2, ':qp3' => 'yo'],
296			],
297
298			/* in object conditions */
299			[new InCondition('id', 'in', 1), '[[id]]=:qp0', [':qp0' => 1]],
300			[new InCondition('id', 'in', [1]), '[[id]]=:qp0', [':qp0' => 1]],
301			[new InCondition('id', 'not in', 1), '[[id]]<>:qp0', [':qp0' => 1]],
302			[new InCondition('id', 'not in', [1]), '[[id]]<>:qp0', [':qp0' => 1]],
303			[new InCondition('id', 'in', [1, 2]), '[[id]] IN (:qp0, :qp1)', [':qp0' => 1, ':qp1' => 2]],
304			[new InCondition('id', 'not in', [1, 2]), '[[id]] NOT IN (:qp0, :qp1)', [':qp0' => 1, ':qp1' => 2]],
305			[new InCondition([], 'in', 1), '0=1', []],
306			[new InCondition([], 'in', [1]), '0=1', []],
307			[new InCondition(['id', 'name'], 'in', []), '0=1', []],
308			[
309			new InCondition(
310			['id'],
311			'in',
312			$this->mock->query()->select('id')->from('users')->where(['active' => 1]),
313			),
314			'([[id]]) IN (SELECT [[id]] FROM [[users]] WHERE [[active]]=:qp0)',
315			[':qp0' => 1],
316			],
317			[
318			new InCondition(['id', 'name'], 'in', [['id' => 1]]),
319			'([[id]], [[name]]) IN ((:qp0, NULL))',
320			[':qp0' => 1],
321			],
322			[
323			new InCondition(['id', 'name'], 'in', [['name' => 'oy']]),
324			'([[id]], [[name]]) IN ((NULL, :qp0))',
325			[':qp0' => 'oy'],
326			],
327			[
328			new InCondition(['id', 'name'], 'in', [['id' => 1, 'name' => 'oy']]),
329			'([[id]], [[name]]) IN ((:qp0, :qp1))',
330			[':qp0' => 1, ':qp1' => 'oy'],
331			],
332
333			/* exists */
334			[
335			[
336			'exists',
337			$this->mock->query()->select('id')->from('users')->where(['active' => 1]),
338			],
339			'EXISTS (SELECT [[id]] FROM [[users]] WHERE [[active]]=:qp0)',
340			[':qp0' => 1],
341			],
342			[
343			['not exists', $this->mock->query()->select('id')->from('users')->where(['active' => 1])],
344			'NOT EXISTS (SELECT [[id]] FROM [[users]] WHERE [[active]]=:qp0)', [':qp0' => 1],
345			],
346
347			/* simple conditions */
348			[['=', 'a', 'b'], '[[a]] = :qp0', [':qp0' => 'b']],
349			[['>', 'a', 1], '[[a]] > :qp0', [':qp0' => 1]],
350			[['>=', 'a', 'b'], '[[a]] >= :qp0', [':qp0' => 'b']],
351			[['<', 'a', 2], '[[a]] < :qp0', [':qp0' => 2]],
352			[['<=', 'a', 'b'], '[[a]] <= :qp0', [':qp0' => 'b']],
353			[['<>', 'a', 3], '[[a]] <> :qp0', [':qp0' => 3]],
354			[['!=', 'a', 'b'], '[[a]] != :qp0', [':qp0' => 'b']],
355			[
356			['>=', 'date', new Expression('DATE_SUB(NOW(), INTERVAL 1 MONTH)')],
357			'[[date]] >= DATE_SUB(NOW(), INTERVAL 1 MONTH)',
358			[],
359			],
360			[
361			['>=', 'date', new Expression('DATE_SUB(NOW(), INTERVAL :month MONTH)', [':month' => 2])],
362			'[[date]] >= DATE_SUB(NOW(), INTERVAL :month MONTH)',
363			[':month' => 2],
364			],
365			[
366			['=', 'date', $this->mock->query()->select('max(date)')->from('test')->where(['id' => 5])],
367			'[[date]] = (SELECT max(date) FROM [[test]] WHERE [[id]]=:qp0)',
368			[':qp0' => 5],
369			],
370			[['=', 'a', null], '[[a]] = NULL', []],
371
372			/* operand1 is Expression */
373			[
374			['=', new Expression('date'), '2019-08-01'],
375			'date = :qp0',
376			[':qp0' => '2019-08-01'],
377			],
378			[
379			['=', $this->mock->query()->select('COUNT(*)')->from('test')->where(['id' => 6]), 0],
380			'(SELECT COUNT(*) FROM [[test]] WHERE [[id]]=:qp0) = :qp1',
381			[':qp0' => 6, ':qp1' => 0],
382			],
383
384			/* hash condition */
385			[['a' => 1, 'b' => 2], '([[a]]=:qp0) AND ([[b]]=:qp1)', [':qp0' => 1, ':qp1' => 2]],
386			[
387			['a' => new Expression('CONCAT(col1, col2)'), 'b' => 2],
388			'([[a]]=CONCAT(col1, col2)) AND ([[b]]=:qp0)',
389			[':qp0' => 2],
390			],
391			[['a' => null], '[[a]] IS NULL', []],
392
393			/* direct conditions */
394			['a = CONCAT(col1, col2)', 'a = CONCAT(col1, col2)', []],
395			[
396			new Expression('a = CONCAT(col1, :param1)', ['param1' => 'value1']),
397			'a = CONCAT(col1, :param1)',
398			['param1' => 'value1'],
399			],
400
401			/* Expression with params as operand of 'not' */
402			[
403			['not', new Expression('any_expression(:a)', [':a' => 1])],
404			'NOT (any_expression(:a))', [':a' => 1],
405			],
406			[new Expression('NOT (any_expression(:a))', [':a' => 1]), 'NOT (any_expression(:a))', [':a' => 1]],
407
408			/* like */
409			[['like', 'a', 'b'], '[[a]] LIKE :qp0', [':qp0' => '%b%']],
410			[
411			['like', 'a', new Expression(':qp0', [':qp0' => '%b%'])],
412			'[[a]] LIKE :qp0',
413			[':qp0' => '%b%'],
414			],
415			[['like', new Expression('CONCAT(col1, col2)'), 'b'], 'CONCAT(col1, col2) LIKE :qp0', [':qp0' => '%b%']],
416			];
417
418			return $this->replaceQuotes($conditions);
419			}
420
421			public function buildFilterCondition(): array
422			{
423			$conditions = [
424			/* like */
425			[['like', 'name', []], '', []],
426			[['not like', 'name', []], '', []],
427			[['or like', 'name', []], '', []],
428			[['or not like', 'name', []], '', []],
429
430			/* not */
431			[['not', ''], '', []],
432
433			/* and */
434			[['and', '', ''], '', []],
435			[['and', '', 'id=2'], 'id=2', []],
436			[['and', 'id=1', ''], 'id=1', []],
437			[['and', 'type=1', ['or', '', 'id=2']], '(type=1) AND (id=2)', []],
438
439			/* or */
440			[['or', 'id=1', ''], 'id=1', []],
441			[['or', 'type=1', ['or', '', 'id=2']], '(type=1) OR (id=2)', []],
442
443			/* between */
444			[['between', 'id', 1, null], '', []],
445			[['not between', 'id', null, 10], '', []],
446
447			/* in */
448			[['in', 'id', []], '', []],
449			[['not in', 'id', []], '', []],
450
451			/* simple conditions */
452			[['=', 'a', ''], '', []],
453			[['>', 'a', ''], '', []],
454			[['>=', 'a', ''], '', []],
455			[['<', 'a', ''], '', []],
456			[['<=', 'a', ''], '', []],
457			[['<>', 'a', ''], '', []],
458			[['!=', 'a', ''], '', []],
459			];
460
461			return $this->replaceQuotes($conditions);
462			}
463
464			public function buildWhereExists(): array
465			{
466			return [
467			[
468			'exists',
469			DbHelper::replaceQuotes(
470			<<<SQL
471			SELECT [[id]] FROM [[TotalExample]] [[t]] WHERE EXISTS (SELECT [[1]] FROM [[Website]] [[w]])
472			SQL,
473			$this->mock->getDriverName(),
474			),
475			],
476			[
477			'not exists',
478			DbHelper::replaceQuotes(
479			<<<SQL
480			SELECT [[id]] FROM [[TotalExample]] [[t]] WHERE NOT EXISTS (SELECT [[1]] FROM [[Website]] [[w]])
481			SQL,
482			$this->mock->getDriverName(),
483			),
484			],
485			];
486			}
487
488			private function replaceQuotes(array $conditions): array
489			{
490			/* adjust dbms specific escaping */
491			foreach ($conditions as $i => $condition) {
492			$conditions[$i][1] = DbHelper::replaceQuotes($condition[1], $this->mock->getDriverName());
493			}
494
495			return $conditions;
496			}
497			}
498

yiisoft / db

Pull Request — master (#380)

BaseQueryBuilderProvider::__construct() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like