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

QueryBuilderTest::testIssue15653() A

↳ Parent: QueryBuilderTest

Complexity

Conditions	1
Paths	1

Size

Total Lines	17
Code Lines	11

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
cc	1
eloc	11
c	1
b	0
f	0
nc	1
nop	0
dl	0
loc	17
rs	9.9

<?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('sqlite');
        $this->queryBuilder = $this->mock->queryBuilder('`', '`');
    }

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

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

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

yiisoft / db

Pull Request — master (#377)

QueryBuilderTest::testIssue15653() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like