QueryBuilderTest::setUp() - 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

QueryBuilderTest::setUp() A

↳ Parent: QueryBuilderTest

Complexity

Conditions	1
Paths	1

Size

Total Lines	6
Code Lines	3

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

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

<?php

declare(strict_types=1);

namespace Yiisoft\Db\Tests;

use PHPUnit\Framework\TestCase;
use Yiisoft\Db\Expression\Expression;
use Yiisoft\Db\Expression\ExpressionInterface;
use Yiisoft\Db\QueryBuilder\QueryBuilderInterface;
use Yiisoft\Db\Tests\Support\DbHelper;
use Yiisoft\Db\Tests\Support\Mock;

/**
 * @group db
 */
final class QueryBuilderTest extends TestCase
{
    private QueryBuilderInterface $queryBuilder;
    private Mock $mock;

    public function setUp(): void
    {
        parent::setUp();

        $this->mock = new Mock();
        $this->queryBuilder = $this->mock->queryBuilder('`', '`');
    }

    public function tearDown(): void
    {
        parent::tearDown();

        unset($this->queryBuilder, $this->mock);
    }

    /**
     * @dataProvider \Yiisoft\Db\Tests\Provider\QueryBuilderProvider::batchInsert()
     */
    public function testBatchInsert(
        string $table,
        array $columns,
        array $value,
        string|null $expected,
        array $expectedParams = []
    ): void {
        $params = [];
        $sql = $this->queryBuilder->batchInsert($table, $columns, $value, $params);

        $this->assertSame($expected, $sql);
        $this->assertSame($expectedParams, $params);
    }

    /**
     * @dataProvider \Yiisoft\Db\Tests\Provider\QueryBuilderProvider::buildConditions()
     */
    public function testBuildCondition(
        array|ExpressionInterface|string $conditions,
        string $expected,
        array $expectedParams = []
    ): void {
        $query = $this->mock->query()->where($conditions);
        [$sql, $params] = $this->queryBuilder->build($query);

        $this->assertSame(
            'SELECT *' . (
                empty($expected) ? '' : ' WHERE ' . DbHelper::replaceQuotes(
                    $expected,
                    $this->mock->getDriverName(),
                )
            ),
            $sql,
        );
        $this->assertSame($expectedParams, $params);
    }

    /**
     * @dataProvider \Yiisoft\Db\Tests\Provider\QueryBuilderProvider::buildFilterCondition()
     */
    public function testBuildFilterCondition(array $condition, string $expected, array $expectedParams): void
    {
        $query = $this->mock->query()->filterWhere($condition);
        [$sql, $params] = $this->queryBuilder->build($query);

        $this->assertSame(
            'SELECT *' . (
                empty($expected) ? '' : ' WHERE ' . DbHelper::replaceQuotes(
                    $expected,
                    $this->mock->getDriverName(),
                )
            ),
            $sql,
        );
        $this->assertSame($expectedParams, $params);
    }

    /**
     * This test contains three select queries connected with UNION and UNION ALL constructions.
     * It could be useful to use "phpunit --group=db --filter testBuildUnion" command for run it.
     */
    public function testBuildUnion(): void
    {
        $expectedQuerySql = DbHelper::replaceQuotes(
            <<<SQL
            (SELECT [[id]] FROM [[TotalExample]] [[t1]] WHERE (w > 0) AND (x < 2)) UNION ( SELECT [[id]] FROM [[TotalTotalExample]] [[t2]] WHERE w > 5 ) UNION ALL ( SELECT [[id]] FROM [[TotalTotalExample]] [[t3]] WHERE w = 3 )
            SQL,
            $this->mock->getDriverName(),
        );

        $secondQuery = $this->mock
            ->query()
            ->select('id')
            ->from('TotalTotalExample t2')
            ->where('w > 5');

        $thirdQuery = $this->mock
            ->query()
            ->select('id')
            ->from('TotalTotalExample t3')
            ->where('w = 3');

        $query = $this->mock
            ->query()
            ->select('id')
            ->from('TotalExample t1')
            ->where(['and', 'w > 0', 'x < 2'])
            ->union($secondQuery)
            ->union($thirdQuery, true);

        [$actualQuerySql, $queryParams] = $this->queryBuilder->build($query);

        $this->assertSame($expectedQuerySql, $actualQuerySql);
        $this->assertSame([], $queryParams);
    }

    public function testBuildWithQuery(): void
    {
        $expectedQuerySql = DbHelper::replaceQuotes(
            <<<SQL
            WITH a1 AS (SELECT [[id]] FROM [[t1]] WHERE expr = 1), a2 AS ((SELECT [[id]] FROM [[t2]] INNER JOIN [[a1]] ON t2.id = a1.id WHERE expr = 2) UNION ( SELECT [[id]] FROM [[t3]] WHERE expr = 3 )) SELECT * FROM [[a2]]
            SQL,
            $this->mock->getDriverName(),
        );

        $with1Query = $this->mock
            ->query()
            ->select('id')
            ->from('t1')
            ->where('expr = 1');

        $with2Query = $this->mock
            ->query()
            ->select('id')
            ->from('t2')
            ->innerJoin('a1', 't2.id = a1.id')
            ->where('expr = 2');

        $with3Query = $this->mock
            ->query()
            ->select('id')
            ->from('t3')
            ->where('expr = 3');

        $query = $this->mock
            ->query()
            ->withQuery($with1Query, 'a1')
            ->withQuery($with2Query->union($with3Query), 'a2')
            ->from('a2');

        [$actualQuerySql, $queryParams] = $this->queryBuilder->build($query);

        $this->assertSame($expectedQuerySql, $actualQuerySql);
        $this->assertSame([], $queryParams);
    }

    public function testBuildWithQueryRecursive(): void
    {
        $expectedQuerySql = DbHelper::replaceQuotes(
            <<<SQL
            WITH RECURSIVE a1 AS (SELECT [[id]] FROM [[t1]] WHERE expr = 1) SELECT * FROM [[a1]]
            SQL,
            $this->mock->getDriverName(),
        );

        $with1Query = $this->mock
            ->query()
            ->select('id')
            ->from('t1')
            ->where('expr = 1');

        $query = $this->mock
            ->query()
            ->withQuery($with1Query, 'a1', true)
            ->from('a1');

        [$actualQuerySql, $queryParams] = $this->queryBuilder->build($query);

        $this->assertSame($expectedQuerySql, $actualQuerySql);
        $this->assertSame([], $queryParams);
    }

    /**
     * @dataProvider \Yiisoft\Db\Tests\Provider\QueryBuilderProvider::buildWhereExists()
     */
    public function testBuildWhereExists(string $cond, string $expectedQuerySql): void
    {
        $expectedQueryParams = [];
        $subQuery = $this->mock->query()->select('1')->from('Website w');
        $query = $this->mock->query()->select('id')->from('TotalExample t')->where([$cond, $subQuery]);

        [$actualQuerySql, $actualQueryParams] = $this->queryBuilder->build($query);

        $this->assertSame($expectedQuerySql, $actualQuerySql);
        $this->assertSame($expectedQueryParams, $actualQueryParams);
    }

    public function testBuildWhereExistsWithArrayParameters(): void
    {
        $expectedQuerySql = DbHelper::replaceQuotes(
            <<<SQL
            SELECT [[id]] FROM [[TotalExample]] [[t]] WHERE (EXISTS (SELECT [[1]] FROM [[Website]] [[w]] WHERE (w.id = t.website_id) AND (([[w]].[[merchant_id]]=:qp0) AND ([[w]].[[user_id]]=:qp1)))) AND ([[t]].[[some_column]]=:qp2)
            SQL,
            $this->mock->getDriverName(),
        );

        $expectedQueryParams = [':qp0' => 6, ':qp1' => 210, ':qp2' => 'asd'];

        $subQuery = $this->mock
            ->query()
            ->select('1')
            ->from('Website w')
            ->where('w.id = t.website_id')
            ->andWhere(['w.merchant_id' => 6, 'w.user_id' => 210]);

        $query = $this->mock
            ->query()
            ->select('id')
            ->from('TotalExample t')
            ->where(['exists', $subQuery])
            ->andWhere(['t.some_column' => 'asd']);

        [$actualQuerySql, $queryParams] = $this->queryBuilder->build($query);

        $this->assertSame($expectedQuerySql, $actualQuerySql);
        $this->assertSame($expectedQueryParams, $queryParams);
    }

    public function testBuildWhereExistsWithParameters(): void
    {
        $expectedQuerySql = DbHelper::replaceQuotes(
            <<<SQL
            SELECT [[id]] FROM [[TotalExample]] [[t]] WHERE (EXISTS (SELECT [[1]] FROM [[Website]] [[w]] WHERE (w.id = t.website_id) AND (w.merchant_id = :merchant_id))) AND (t.some_column = :some_value)
            SQL,
            $this->mock->getDriverName(),
        );

        $expectedQueryParams = [':some_value' => 'asd', ':merchant_id' => 6];

        $subQuery = $this->mock
            ->query()
            ->select('1')
            ->from('Website w')
            ->where('w.id = t.website_id')
            ->andWhere('w.merchant_id = :merchant_id', [':merchant_id' => 6]);

        $query = $this->mock
            ->query()
            ->select('id')
            ->from('TotalExample t')
            ->where(['exists', $subQuery])
            ->andWhere('t.some_column = :some_value', [':some_value' => 'asd']);

        [$actualQuerySql, $queryParams] = $this->queryBuilder->build($query);

        $this->assertSame($expectedQuerySql, $actualQuerySql);
        $this->assertSame($expectedQueryParams, $queryParams);
    }

    public function testComplexSelect(): void
    {
        $expressionString = DbHelper::replaceQuotes(
            <<<SQL
            case t.Status_Id when 1 then 'Acknowledge' when 2 then 'No Action' else 'Unknown Action' END as [[Next Action]]
            SQL,
            $this->mock->getDriverName(),
        );

        $expected = DbHelper::replaceQuotes(
            <<<SQL
            SELECT [[t]].[[id]] AS [[ID]], [[gsm]].[[username]] AS [[GSM]], [[part]].[[Part]], [[t]].[[Part_Cost]] AS [[Part Cost]], st_x(location::geometry) AS [[lon]], case t.Status_Id when 1 then 'Acknowledge' when 2 then 'No Action' else 'Unknown Action' END as [[Next Action]] FROM [[tablename]]
            SQL,
            $this->mock->getDriverName(),
        );

        $this->assertIsString($expressionString);

        $query = $this->mock
            ->query()
            ->select(
                [
                    'ID' => 't.id',
                    'gsm.username as GSM',
                    'part.Part',
                    'Part Cost' => 't.Part_Cost',
                    'st_x(location::geometry) as lon',
                    new Expression($expressionString),
                ]
            )
            ->from('tablename');

        [$sql, $params] = $this->queryBuilder->build($query);

        $this->assertSame($expected, $sql);
        $this->assertEmpty($params);
    }

    /**
     * {@see https://github.com/yiisoft/yii2/issues/10869}
     */
    public function testFromIndexHint(): void
    {
        $query = $this->mock->query()->from([new Expression('{{%user}} USE INDEX (primary)')]);

        [$sql, $params] = $this->queryBuilder->build($query);

        $expected = DbHelper::replaceQuotes(
            <<<SQL
            SELECT * FROM {{%user}} USE INDEX (primary)
            SQL,
            $this->mock->getDriverName(),
        );

        $this->assertSame($expected, $sql);
        $this->assertEmpty($params);

        $query = $this->mock
            ->query()
            ->from([new Expression('{{user}} {{t}} FORCE INDEX (primary) IGNORE INDEX FOR ORDER BY (i1)')])
            ->leftJoin(['p' => 'profile'], 'user.id = profile.user_id USE INDEX (i2)');

        [$sql, $params] = $this->queryBuilder->build($query);

        $expected = DbHelper::replaceQuotes(
            <<<SQL
            SELECT * FROM {{user}} {{t}} FORCE INDEX (primary) IGNORE INDEX FOR ORDER BY (i1) LEFT JOIN [[profile]] [[p]] ON user.id = profile.user_id USE INDEX (i2)
            SQL,
            $this->mock->getDriverName(),
        );

        $this->assertSame($expected, $sql);
        $this->assertEmpty($params);
    }

    public function testFromSubquery(): void
    {
        /* subquery */
        $subquery = $this->mock->query()->from('user')->where('account_id = accounts.id');
        $query = $this->mock->query()->from(['activeusers' => $subquery]);

        /* SELECT * FROM (SELECT * FROM [[user]] WHERE [[active]] = 1) [[activeusers]]; */
        [$sql, $params] = $this->queryBuilder->build($query);

        $expected = DbHelper::replaceQuotes(
            <<<SQL
            SELECT * FROM (SELECT * FROM [[user]] WHERE account_id = accounts.id) [[activeusers]]
            SQL,
            $this->mock->getDriverName(),
        );

        $this->assertSame($expected, $sql);
        $this->assertEmpty($params);

        /* subquery with params */
        $subquery = $this->mock->query()->from('user')->where('account_id = :id', ['id' => 1]);
        $query = $this->mock->query()->from(['activeusers' => $subquery])->where('abc = :abc', ['abc' => 'abc']);

        /* SELECT * FROM (SELECT * FROM [[user]] WHERE [[active]] = 1) [[activeusers]]; */
        [$sql, $params] = $this->queryBuilder->build($query);

        $expected = DbHelper::replaceQuotes(
            <<<SQL
            SELECT * FROM (SELECT * FROM [[user]] WHERE account_id = :id) [[activeusers]] WHERE abc = :abc
            SQL,
            $this->mock->getDriverName(),
        );

        $this->assertSame($expected, $sql);
        $this->assertSame(['abc' => 'abc', 'id' => 1], $params);

        /* simple subquery */
        $subquery = '(SELECT * FROM user WHERE account_id = accounts.id)';
        $query = $this->mock->query()->from(['activeusers' => $subquery]);

        /* SELECT * FROM (SELECT * FROM [[user]] WHERE [[active]] = 1) [[activeusers]]; */
        [$sql, $params] = $this->queryBuilder->build($query);

        $expected = DbHelper::replaceQuotes(
            <<<SQL
            SELECT * FROM (SELECT * FROM user WHERE account_id = accounts.id) [[activeusers]]
            SQL,
            $this->mock->getDriverName(),
        );

        $this->assertSame($expected, $sql);
        $this->assertEmpty($params);
    }

    public function testGroupBy(): void
    {
        /* simple string */
        $query = $this->mock->query()->select('*')->from('operations')->groupBy('name, date');

        [$sql, $params] = $this->queryBuilder->build($query);

        $expected = DbHelper::replaceQuotes(
            <<<SQL
            SELECT * FROM [[operations]] GROUP BY [[name]], [[date]]
            SQL,
            $this->mock->getDriverName(),
        );

        $this->assertSame($expected, $sql);
        $this->assertEmpty($params);

        /* array syntax */
        $query = $this->mock->query()->select('*')->from('operations')->groupBy(['name', 'date']);

        [$sql, $params] = $this->queryBuilder->build($query);

        $expected = DbHelper::replaceQuotes(
            <<<SQL
            SELECT * FROM [[operations]] GROUP BY [[name]], [[date]]
            SQL,
            $this->mock->getDriverName(),
        );

        $this->assertSame($expected, $sql);
        $this->assertEmpty($params);

        /* expression */
        $query = $this->mock
            ->query()
            ->select('*')
            ->from('operations')
            ->where('account_id = accounts.id')
            ->groupBy(new Expression('SUBSTR(name, 0, 1), x'));

        [$sql, $params] = $this->queryBuilder->build($query);

        $expected = DbHelper::replaceQuotes(
            <<<SQL
            SELECT * FROM [[operations]] WHERE account_id = accounts.id GROUP BY SUBSTR(name, 0, 1), x
            SQL,
            $this->mock->getDriverName(),
        );

        $this->assertSame($expected, $sql);
        $this->assertEmpty($params);

        /* expression with params */
        $query = $this->mock
            ->query()
            ->select('*')
            ->from('operations')
            ->groupBy(new Expression('SUBSTR(name, 0, :to), x', [':to' => 4]));

        [$sql, $params] = $this->queryBuilder->build($query);

        $expected = DbHelper::replaceQuotes(
            <<<SQL
            SELECT * FROM [[operations]] GROUP BY SUBSTR(name, 0, :to), x
            SQL,
            $this->mock->getDriverName(),
        );

        $this->assertSame($expected, $sql);
        $this->assertSame([':to' => 4], $params);
    }

    /**
     * {@see https://github.com/yiisoft/yii2/issues/15653}
     */
    public function testIssue15653(): void
    {
        $query = $this->mock->query()->from('admin_user')->where(['is_deleted' => false]);
        $query->where([])->andWhere(['in', 'id', ['1', '0']]);

        [$sql, $params] = $this->queryBuilder->build($query);

        $this->assertSame(
            DbHelper::replaceQuotes(
                <<<SQL
                SELECT * FROM [[admin_user]] WHERE [[id]] IN (:qp0, :qp1)
                SQL,
                $this->mock->getDriverName(),
            ),
            $sql,
        );
        $this->assertSame([':qp0' => '1', ':qp1' => '0'], $params);
    }

    public function testOrderBy(): void
    {
        /* simple string */
        $query = $this->mock->query()->select('*')->from('operations')->orderBy('name ASC, date DESC');

        [$sql, $params] = $this->queryBuilder->build($query);

        $expected = DbHelper::replaceQuotes(
            <<<SQL
            SELECT * FROM [[operations]] ORDER BY [[name]], [[date]] DESC
            SQL,
            $this->mock->getDriverName(),
        );

        $this->assertSame($expected, $sql);
        $this->assertEmpty($params);

        /* array syntax */
        $query = $this->mock->query()->select('*')->from('operations')->orderBy(['name' => SORT_ASC, 'date' => SORT_DESC]);

        [$sql, $params] = $this->queryBuilder->build($query);

        $expected = DbHelper::replaceQuotes(
            <<<SQL
            SELECT * FROM [[operations]] ORDER BY [[name]], [[date]] DESC
            SQL,
            $this->mock->getDriverName(),
        );

        $this->assertSame($expected, $sql);
        $this->assertEmpty($params);

        /* expression */
        $query = $this->mock
            ->query()
            ->select('*')
            ->from('operations')
            ->where('account_id = accounts.id')
            ->orderBy(new Expression('SUBSTR(name, 3, 4) DESC, x ASC'));

        [$sql, $params] = $this->queryBuilder->build($query);

        $expected = DbHelper::replaceQuotes(
            <<<SQL
            SELECT * FROM [[operations]] WHERE account_id = accounts.id ORDER BY SUBSTR(name, 3, 4) DESC, x ASC
            SQL,
            $this->mock->getDriverName(),
        );

        $this->assertSame($expected, $sql);
        $this->assertEmpty($params);

        /* expression with params */
        $query = $this->mock
            ->query()
            ->select('*')
            ->from('operations')
            ->orderBy(new Expression('SUBSTR(name, 3, :to) DESC, x ASC', [':to' => 4]));

        [$sql, $params] = $this->queryBuilder->build($query);

        $expected = DbHelper::replaceQuotes(
            <<<SQL
            SELECT * FROM [[operations]] ORDER BY SUBSTR(name, 3, :to) DESC, x ASC
            SQL,
            $this->mock->getDriverName(),
        );

        $this->assertSame($expected, $sql);
        $this->assertSame([':to' => 4], $params);
    }

    public function testSelectExpression(): void
    {
        $query = $this->mock->query()->select(new Expression('1 AS ab'))->from('tablename');

        [$sql, $params] = $this->queryBuilder->build($query);

        $expected = DbHelper::replaceQuotes(
            <<<SQL
            SELECT 1 AS ab FROM [[tablename]]
            SQL,
            $this->mock->getDriverName(),
        );

        $this->assertSame($expected, $sql);
        $this->assertEmpty($params);

        $query = $this->mock
            ->query()
            ->select(new Expression('1 AS ab'))
            ->addSelect(new Expression('2 AS cd'))
            ->addSelect(['ef' => new Expression('3')])
            ->from('tablename');

        [$sql, $params] = $this->queryBuilder->build($query);

        $expected = DbHelper::replaceQuotes(
            <<<SQL
            SELECT 1 AS ab, 2 AS cd, 3 AS [[ef]] FROM [[tablename]]
            SQL,
            $this->mock->getDriverName(),
        );

        $this->assertSame($expected, $sql);
        $this->assertEmpty($params);

        $query = $this->mock
            ->query()
            ->select(new Expression('SUBSTR(name, 0, :len)', [':len' => 4]))
            ->from('tablename');

        [$sql, $params] = $this->queryBuilder->build($query);

        $expected = DbHelper::replaceQuotes(
            <<<SQL
            SELECT SUBSTR(name, 0, :len) FROM [[tablename]]
            SQL,
            $this->mock->getDriverName(),
        );

        $this->assertSame($expected, $sql);
        $this->assertSame([':len' => 4], $params);
    }

    public function testSelectSubquery(): void
    {
        $expected = DbHelper::replaceQuotes(
            <<<SQL
            SELECT *, (SELECT COUNT(*) FROM [[operations]] WHERE account_id = accounts.id) AS [[operations_count]] FROM [[accounts]]
            SQL,
            $this->mock->getDriverName(),
        );

        $subquery = $this->mock
            ->query()
            ->select('COUNT(*)')
            ->from('operations')
            ->where('account_id = accounts.id');

        $query = $this->mock
            ->query()
            ->select('*')
            ->from('accounts')
            ->addSelect(['operations_count' => $subquery]);

        [$sql, $params] = $this->queryBuilder->build($query);

        $this->assertSame($expected, $sql);
        $this->assertEmpty($params);
    }
}


1			<?php
2
3			declare(strict_types=1);
4
5			namespace Yiisoft\Db\Tests;
6
7			use PHPUnit\Framework\TestCase;
8			use Yiisoft\Db\Expression\Expression;
9			use Yiisoft\Db\Expression\ExpressionInterface;
10			use Yiisoft\Db\QueryBuilder\QueryBuilderInterface;
11			use Yiisoft\Db\Tests\Support\DbHelper;
12			use Yiisoft\Db\Tests\Support\Mock;
13
14			/**
15			* @group db
16			*/
17			final class QueryBuilderTest extends TestCase
18			{
19			private QueryBuilderInterface $queryBuilder;
20			private Mock $mock;
21
22			public function setUp(): void
23			{
24			parent::setUp();
25
26			$this->mock = new Mock();
27			$this->queryBuilder = $this->mock->queryBuilder('`', '`');
28			}
29
30			public function tearDown(): void
31			{
32			parent::tearDown();
33
34			unset($this->queryBuilder, $this->mock);
35			}
36
37			/**
38			* @dataProvider \Yiisoft\Db\Tests\Provider\QueryBuilderProvider::batchInsert()
39			*/
40			public function testBatchInsert(
41			string $table,
42			array $columns,
43			array $value,
44			string\|null $expected,
45			array $expectedParams = []
46			): void {
47			$params = [];
48			$sql = $this->queryBuilder->batchInsert($table, $columns, $value, $params);
49
50			$this->assertSame($expected, $sql);
51			$this->assertSame($expectedParams, $params);
52			}
53
54			/**
55			* @dataProvider \Yiisoft\Db\Tests\Provider\QueryBuilderProvider::buildConditions()
56			*/
57			public function testBuildCondition(
58			array\|ExpressionInterface\|string $conditions,
59			string $expected,
60			array $expectedParams = []
61			): void {
62			$query = $this->mock->query()->where($conditions);
63			[$sql, $params] = $this->queryBuilder->build($query);
64
65			$this->assertSame(
66			'SELECT *' . (
67			empty($expected) ? '' : ' WHERE ' . DbHelper::replaceQuotes(
68			$expected,
69			$this->mock->getDriverName(),
70			)
71			),
72			$sql,
73			);
74			$this->assertSame($expectedParams, $params);
75			}
76
77			/**
78			* @dataProvider \Yiisoft\Db\Tests\Provider\QueryBuilderProvider::buildFilterCondition()
79			*/
80			public function testBuildFilterCondition(array $condition, string $expected, array $expectedParams): void
81			{
82			$query = $this->mock->query()->filterWhere($condition);
83			[$sql, $params] = $this->queryBuilder->build($query);
84
85			$this->assertSame(
86			'SELECT *' . (
87			empty($expected) ? '' : ' WHERE ' . DbHelper::replaceQuotes(
88			$expected,
89			$this->mock->getDriverName(),
90			)
91			),
92			$sql,
93			);
94			$this->assertSame($expectedParams, $params);
95			}
96
97			/**
98			* This test contains three select queries connected with UNION and UNION ALL constructions.
99			* It could be useful to use "phpunit --group=db --filter testBuildUnion" command for run it.
100			*/
101			public function testBuildUnion(): void
102			{
103			$expectedQuerySql = DbHelper::replaceQuotes(
104			<<<SQL
105			(SELECT [[id]] FROM [[TotalExample]] [[t1]] WHERE (w > 0) AND (x < 2)) UNION ( SELECT [[id]] FROM [[TotalTotalExample]] [[t2]] WHERE w > 5 ) UNION ALL ( SELECT [[id]] FROM [[TotalTotalExample]] [[t3]] WHERE w = 3 )
106			SQL,
107			$this->mock->getDriverName(),
108			);
109
110			$secondQuery = $this->mock
111			->query()
112			->select('id')
113			->from('TotalTotalExample t2')
114			->where('w > 5');
115
116			$thirdQuery = $this->mock
117			->query()
118			->select('id')
119			->from('TotalTotalExample t3')
120			->where('w = 3');
121
122			$query = $this->mock
123			->query()
124			->select('id')
125			->from('TotalExample t1')
126			->where(['and', 'w > 0', 'x < 2'])
127			->union($secondQuery)
128			->union($thirdQuery, true);
129
130			[$actualQuerySql, $queryParams] = $this->queryBuilder->build($query);
131
132			$this->assertSame($expectedQuerySql, $actualQuerySql);
133			$this->assertSame([], $queryParams);
134			}
135
136			public function testBuildWithQuery(): void
137			{
138			$expectedQuerySql = DbHelper::replaceQuotes(
139			<<<SQL
140			WITH a1 AS (SELECT [[id]] FROM [[t1]] WHERE expr = 1), a2 AS ((SELECT [[id]] FROM [[t2]] INNER JOIN [[a1]] ON t2.id = a1.id WHERE expr = 2) UNION ( SELECT [[id]] FROM [[t3]] WHERE expr = 3 )) SELECT * FROM [[a2]]
141			SQL,
142			$this->mock->getDriverName(),
143			);
144
145			$with1Query = $this->mock
146			->query()
147			->select('id')
148			->from('t1')
149			->where('expr = 1');
150
151			$with2Query = $this->mock
152			->query()
153			->select('id')
154			->from('t2')
155			->innerJoin('a1', 't2.id = a1.id')
156			->where('expr = 2');
157
158			$with3Query = $this->mock
159			->query()
160			->select('id')
161			->from('t3')
162			->where('expr = 3');
163
164			$query = $this->mock
165			->query()
166			->withQuery($with1Query, 'a1')
167			->withQuery($with2Query->union($with3Query), 'a2')
168			->from('a2');
169
170			[$actualQuerySql, $queryParams] = $this->queryBuilder->build($query);
171
172			$this->assertSame($expectedQuerySql, $actualQuerySql);
173			$this->assertSame([], $queryParams);
174			}
175
176			public function testBuildWithQueryRecursive(): void
177			{
178			$expectedQuerySql = DbHelper::replaceQuotes(
179			<<<SQL
180			WITH RECURSIVE a1 AS (SELECT [[id]] FROM [[t1]] WHERE expr = 1) SELECT * FROM [[a1]]
181			SQL,
182			$this->mock->getDriverName(),
183			);
184
185			$with1Query = $this->mock
186			->query()
187			->select('id')
188			->from('t1')
189			->where('expr = 1');
190
191			$query = $this->mock
192			->query()
193			->withQuery($with1Query, 'a1', true)
194			->from('a1');
195
196			[$actualQuerySql, $queryParams] = $this->queryBuilder->build($query);
197
198			$this->assertSame($expectedQuerySql, $actualQuerySql);
199			$this->assertSame([], $queryParams);
200			}
201
202			/**
203			* @dataProvider \Yiisoft\Db\Tests\Provider\QueryBuilderProvider::buildWhereExists()
204			*/
205			public function testBuildWhereExists(string $cond, string $expectedQuerySql): void
206			{
207			$expectedQueryParams = [];
208			$subQuery = $this->mock->query()->select('1')->from('Website w');
209			$query = $this->mock->query()->select('id')->from('TotalExample t')->where([$cond, $subQuery]);
210
211			[$actualQuerySql, $actualQueryParams] = $this->queryBuilder->build($query);
212
213			$this->assertSame($expectedQuerySql, $actualQuerySql);
214			$this->assertSame($expectedQueryParams, $actualQueryParams);
215			}
216
217			public function testBuildWhereExistsWithArrayParameters(): void
218			{
219			$expectedQuerySql = DbHelper::replaceQuotes(
220			<<<SQL
221			SELECT [[id]] FROM [[TotalExample]] [[t]] WHERE (EXISTS (SELECT [[1]] FROM [[Website]] [[w]] WHERE (w.id = t.website_id) AND (([[w]].[[merchant_id]]=:qp0) AND ([[w]].[[user_id]]=:qp1)))) AND ([[t]].[[some_column]]=:qp2)
222			SQL,
223			$this->mock->getDriverName(),
224			);
225
226			$expectedQueryParams = [':qp0' => 6, ':qp1' => 210, ':qp2' => 'asd'];
227
228			$subQuery = $this->mock
229			->query()
230			->select('1')
231			->from('Website w')
232			->where('w.id = t.website_id')
233			->andWhere(['w.merchant_id' => 6, 'w.user_id' => 210]);
234
235			$query = $this->mock
236			->query()
237			->select('id')
238			->from('TotalExample t')
239			->where(['exists', $subQuery])
240			->andWhere(['t.some_column' => 'asd']);
241
242			[$actualQuerySql, $queryParams] = $this->queryBuilder->build($query);
243
244			$this->assertSame($expectedQuerySql, $actualQuerySql);
245			$this->assertSame($expectedQueryParams, $queryParams);
246			}
247
248			public function testBuildWhereExistsWithParameters(): void
249			{
250			$expectedQuerySql = DbHelper::replaceQuotes(
251			<<<SQL
252			SELECT [[id]] FROM [[TotalExample]] [[t]] WHERE (EXISTS (SELECT [[1]] FROM [[Website]] [[w]] WHERE (w.id = t.website_id) AND (w.merchant_id = :merchant_id))) AND (t.some_column = :some_value)
253			SQL,
254			$this->mock->getDriverName(),
255			);
256
257			$expectedQueryParams = [':some_value' => 'asd', ':merchant_id' => 6];
258
259			$subQuery = $this->mock
260			->query()
261			->select('1')
262			->from('Website w')
263			->where('w.id = t.website_id')
264			->andWhere('w.merchant_id = :merchant_id', [':merchant_id' => 6]);
265
266			$query = $this->mock
267			->query()
268			->select('id')
269			->from('TotalExample t')
270			->where(['exists', $subQuery])
271			->andWhere('t.some_column = :some_value', [':some_value' => 'asd']);
272
273			[$actualQuerySql, $queryParams] = $this->queryBuilder->build($query);
274
275			$this->assertSame($expectedQuerySql, $actualQuerySql);
276			$this->assertSame($expectedQueryParams, $queryParams);
277			}
278
279			public function testComplexSelect(): void
280			{
281			$expressionString = DbHelper::replaceQuotes(
282			<<<SQL
283			case t.Status_Id when 1 then 'Acknowledge' when 2 then 'No Action' else 'Unknown Action' END as [[Next Action]]
284			SQL,
285			$this->mock->getDriverName(),
286			);
287
288			$expected = DbHelper::replaceQuotes(
289			<<<SQL
290			SELECT [[t]].[[id]] AS [[ID]], [[gsm]].[[username]] AS [[GSM]], [[part]].[[Part]], [[t]].[[Part_Cost]] AS [[Part Cost]], st_x(location::geometry) AS [[lon]], case t.Status_Id when 1 then 'Acknowledge' when 2 then 'No Action' else 'Unknown Action' END as [[Next Action]] FROM [[tablename]]
291			SQL,
292			$this->mock->getDriverName(),
293			);
294
295			$this->assertIsString($expressionString);
296
297			$query = $this->mock
298			->query()
299			->select(
300			[
301			'ID' => 't.id',
302			'gsm.username as GSM',
303			'part.Part',
304			'Part Cost' => 't.Part_Cost',
305			'st_x(location::geometry) as lon',
306			new Expression($expressionString),
307			]
308			)
309			->from('tablename');
310
311			[$sql, $params] = $this->queryBuilder->build($query);
312
313			$this->assertSame($expected, $sql);
314			$this->assertEmpty($params);
315			}
316
317			/**
318			* {@see https://github.com/yiisoft/yii2/issues/10869}
319			*/
320			public function testFromIndexHint(): void
321			{
322			$query = $this->mock->query()->from([new Expression('{{%user}} USE INDEX (primary)')]);
323
324			[$sql, $params] = $this->queryBuilder->build($query);
325
326			$expected = DbHelper::replaceQuotes(
327			<<<SQL
328			SELECT * FROM {{%user}} USE INDEX (primary)
329			SQL,
330			$this->mock->getDriverName(),
331			);
332
333			$this->assertSame($expected, $sql);
334			$this->assertEmpty($params);
335
336			$query = $this->mock
337			->query()
338			->from([new Expression('{{user}} {{t}} FORCE INDEX (primary) IGNORE INDEX FOR ORDER BY (i1)')])
339			->leftJoin(['p' => 'profile'], 'user.id = profile.user_id USE INDEX (i2)');
340
341			[$sql, $params] = $this->queryBuilder->build($query);
342
343			$expected = DbHelper::replaceQuotes(
344			<<<SQL
345			SELECT * FROM {{user}} {{t}} FORCE INDEX (primary) IGNORE INDEX FOR ORDER BY (i1) LEFT JOIN [[profile]] [[p]] ON user.id = profile.user_id USE INDEX (i2)
346			SQL,
347			$this->mock->getDriverName(),
348			);
349
350			$this->assertSame($expected, $sql);
351			$this->assertEmpty($params);
352			}
353
354			public function testFromSubquery(): void
355			{
356			/* subquery */
357			$subquery = $this->mock->query()->from('user')->where('account_id = accounts.id');
358			$query = $this->mock->query()->from(['activeusers' => $subquery]);
359
360			/* SELECT * FROM (SELECT * FROM [[user]] WHERE [[active]] = 1) [[activeusers]]; */
361			[$sql, $params] = $this->queryBuilder->build($query);
362
363			$expected = DbHelper::replaceQuotes(
364			<<<SQL
365			SELECT * FROM (SELECT * FROM [[user]] WHERE account_id = accounts.id) [[activeusers]]
366			SQL,
367			$this->mock->getDriverName(),
368			);
369
370			$this->assertSame($expected, $sql);
371			$this->assertEmpty($params);
372
373			/* subquery with params */
374			$subquery = $this->mock->query()->from('user')->where('account_id = :id', ['id' => 1]);
375			$query = $this->mock->query()->from(['activeusers' => $subquery])->where('abc = :abc', ['abc' => 'abc']);
376
377			/* SELECT * FROM (SELECT * FROM [[user]] WHERE [[active]] = 1) [[activeusers]]; */
378			[$sql, $params] = $this->queryBuilder->build($query);
379
380			$expected = DbHelper::replaceQuotes(
381			<<<SQL
382			SELECT * FROM (SELECT * FROM [[user]] WHERE account_id = :id) [[activeusers]] WHERE abc = :abc
383			SQL,
384			$this->mock->getDriverName(),
385			);
386
387			$this->assertSame($expected, $sql);
388			$this->assertSame(['abc' => 'abc', 'id' => 1], $params);
389
390			/* simple subquery */
391			$subquery = '(SELECT * FROM user WHERE account_id = accounts.id)';
392			$query = $this->mock->query()->from(['activeusers' => $subquery]);
393
394			/* SELECT * FROM (SELECT * FROM [[user]] WHERE [[active]] = 1) [[activeusers]]; */
395			[$sql, $params] = $this->queryBuilder->build($query);
396
397			$expected = DbHelper::replaceQuotes(
398			<<<SQL
399			SELECT * FROM (SELECT * FROM user WHERE account_id = accounts.id) [[activeusers]]
400			SQL,
401			$this->mock->getDriverName(),
402			);
403
404			$this->assertSame($expected, $sql);
405			$this->assertEmpty($params);
406			}
407
408			public function testGroupBy(): void
409			{
410			/* simple string */
411			$query = $this->mock->query()->select('*')->from('operations')->groupBy('name, date');
412
413			[$sql, $params] = $this->queryBuilder->build($query);
414
415			$expected = DbHelper::replaceQuotes(
416			<<<SQL
417			SELECT * FROM [[operations]] GROUP BY [[name]], [[date]]
418			SQL,
419			$this->mock->getDriverName(),
420			);
421
422			$this->assertSame($expected, $sql);
423			$this->assertEmpty($params);
424
425			/* array syntax */
426			$query = $this->mock->query()->select('*')->from('operations')->groupBy(['name', 'date']);
427
428			[$sql, $params] = $this->queryBuilder->build($query);
429
430			$expected = DbHelper::replaceQuotes(
431			<<<SQL
432			SELECT * FROM [[operations]] GROUP BY [[name]], [[date]]
433			SQL,
434			$this->mock->getDriverName(),
435			);
436
437			$this->assertSame($expected, $sql);
438			$this->assertEmpty($params);
439
440			/* expression */
441			$query = $this->mock
442			->query()
443			->select('*')
444			->from('operations')
445			->where('account_id = accounts.id')
446			->groupBy(new Expression('SUBSTR(name, 0, 1), x'));
447
448			[$sql, $params] = $this->queryBuilder->build($query);
449
450			$expected = DbHelper::replaceQuotes(
451			<<<SQL
452			SELECT * FROM [[operations]] WHERE account_id = accounts.id GROUP BY SUBSTR(name, 0, 1), x
453			SQL,
454			$this->mock->getDriverName(),
455			);
456
457			$this->assertSame($expected, $sql);
458			$this->assertEmpty($params);
459
460			/* expression with params */
461			$query = $this->mock
462			->query()
463			->select('*')
464			->from('operations')
465			->groupBy(new Expression('SUBSTR(name, 0, :to), x', [':to' => 4]));
466
467			[$sql, $params] = $this->queryBuilder->build($query);
468
469			$expected = DbHelper::replaceQuotes(
470			<<<SQL
471			SELECT * FROM [[operations]] GROUP BY SUBSTR(name, 0, :to), x
472			SQL,
473			$this->mock->getDriverName(),
474			);
475
476			$this->assertSame($expected, $sql);
477			$this->assertSame([':to' => 4], $params);
478			}
479
480			/**
481			* {@see https://github.com/yiisoft/yii2/issues/15653}
482			*/
483			public function testIssue15653(): void
484			{
485			$query = $this->mock->query()->from('admin_user')->where(['is_deleted' => false]);
486			$query->where([])->andWhere(['in', 'id', ['1', '0']]);
487
488			[$sql, $params] = $this->queryBuilder->build($query);
489
490			$this->assertSame(
491			DbHelper::replaceQuotes(
492			<<<SQL
493			SELECT * FROM [[admin_user]] WHERE [[id]] IN (:qp0, :qp1)
494			SQL,
495			$this->mock->getDriverName(),
496			),
497			$sql,
498			);
499			$this->assertSame([':qp0' => '1', ':qp1' => '0'], $params);
500			}
501
502			public function testOrderBy(): void
503			{
504			/* simple string */
505			$query = $this->mock->query()->select('*')->from('operations')->orderBy('name ASC, date DESC');
506
507			[$sql, $params] = $this->queryBuilder->build($query);
508
509			$expected = DbHelper::replaceQuotes(
510			<<<SQL
511			SELECT * FROM [[operations]] ORDER BY [[name]], [[date]] DESC
512			SQL,
513			$this->mock->getDriverName(),
514			);
515
516			$this->assertSame($expected, $sql);
517			$this->assertEmpty($params);
518
519			/* array syntax */
520			$query = $this->mock->query()->select('*')->from('operations')->orderBy(['name' => SORT_ASC, 'date' => SORT_DESC]);
521
522			[$sql, $params] = $this->queryBuilder->build($query);
523
524			$expected = DbHelper::replaceQuotes(
525			<<<SQL
526			SELECT * FROM [[operations]] ORDER BY [[name]], [[date]] DESC
527			SQL,
528			$this->mock->getDriverName(),
529			);
530
531			$this->assertSame($expected, $sql);
532			$this->assertEmpty($params);
533
534			/* expression */
535			$query = $this->mock
536			->query()
537			->select('*')
538			->from('operations')
539			->where('account_id = accounts.id')
540			->orderBy(new Expression('SUBSTR(name, 3, 4) DESC, x ASC'));
541
542			[$sql, $params] = $this->queryBuilder->build($query);
543
544			$expected = DbHelper::replaceQuotes(
545			<<<SQL
546			SELECT * FROM [[operations]] WHERE account_id = accounts.id ORDER BY SUBSTR(name, 3, 4) DESC, x ASC
547			SQL,
548			$this->mock->getDriverName(),
549			);
550
551			$this->assertSame($expected, $sql);
552			$this->assertEmpty($params);
553
554			/* expression with params */
555			$query = $this->mock
556			->query()
557			->select('*')
558			->from('operations')
559			->orderBy(new Expression('SUBSTR(name, 3, :to) DESC, x ASC', [':to' => 4]));
560
561			[$sql, $params] = $this->queryBuilder->build($query);
562
563			$expected = DbHelper::replaceQuotes(
564			<<<SQL
565			SELECT * FROM [[operations]] ORDER BY SUBSTR(name, 3, :to) DESC, x ASC
566			SQL,
567			$this->mock->getDriverName(),
568			);
569
570			$this->assertSame($expected, $sql);
571			$this->assertSame([':to' => 4], $params);
572			}
573
574			public function testSelectExpression(): void
575			{
576			$query = $this->mock->query()->select(new Expression('1 AS ab'))->from('tablename');
577
578			[$sql, $params] = $this->queryBuilder->build($query);
579
580			$expected = DbHelper::replaceQuotes(
581			<<<SQL
582			SELECT 1 AS ab FROM [[tablename]]
583			SQL,
584			$this->mock->getDriverName(),
585			);
586
587			$this->assertSame($expected, $sql);
588			$this->assertEmpty($params);
589
590			$query = $this->mock
591			->query()
592			->select(new Expression('1 AS ab'))
593			->addSelect(new Expression('2 AS cd'))
594			->addSelect(['ef' => new Expression('3')])
595			->from('tablename');
596
597			[$sql, $params] = $this->queryBuilder->build($query);
598
599			$expected = DbHelper::replaceQuotes(
600			<<<SQL
601			SELECT 1 AS ab, 2 AS cd, 3 AS [[ef]] FROM [[tablename]]
602			SQL,
603			$this->mock->getDriverName(),
604			);
605
606			$this->assertSame($expected, $sql);
607			$this->assertEmpty($params);
608
609			$query = $this->mock
610			->query()
611			->select(new Expression('SUBSTR(name, 0, :len)', [':len' => 4]))
612			->from('tablename');
613
614			[$sql, $params] = $this->queryBuilder->build($query);
615
616			$expected = DbHelper::replaceQuotes(
617			<<<SQL
618			SELECT SUBSTR(name, 0, :len) FROM [[tablename]]
619			SQL,
620			$this->mock->getDriverName(),
621			);
622
623			$this->assertSame($expected, $sql);
624			$this->assertSame([':len' => 4], $params);
625			}
626
627			public function testSelectSubquery(): void
628			{
629			$expected = DbHelper::replaceQuotes(
630			<<<SQL
631			SELECT , (SELECT COUNT() FROM [[operations]] WHERE account_id = accounts.id) AS [[operations_count]] FROM [[accounts]]
632			SQL,
633			$this->mock->getDriverName(),
634			);
635
636			$subquery = $this->mock
637			->query()
638			->select('COUNT(*)')
639			->from('operations')
640			->where('account_id = accounts.id');
641
642			$query = $this->mock
643			->query()
644			->select('*')
645			->from('accounts')
646			->addSelect(['operations_count' => $subquery]);
647
648			[$sql, $params] = $this->queryBuilder->build($query);
649
650			$this->assertSame($expected, $sql);
651			$this->assertEmpty($params);
652			}
653			}
654

yiisoft / db

Pull Request — master (#380)

QueryBuilderTest::setUp() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like