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

created 2022-11-03 00:32 UTC

QueryBuilderTest::testFromSubquery() A

↳ Parent: QueryBuilderTest

Complexity

Conditions	1
Paths	1

Size

Total Lines	52
Code Lines	30

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
cc	1
eloc	30
c	1
b	0
f	0
nc	1
nop	0
dl	0
loc	52
rs	9.44

How to fix Long Method

<?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 = [];
        $db = $this->mock->connection(true);
        $sql = $db->getQueryBuilder()->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);
    }

    /**
     * @dataProvider \Yiisoft\Db\Tests\Provider\QueryBuilderProvider::buildFrom()
     */
    public function testBuildFrom(string $table, string $expected): void
    {
        $params = [];
        $sql = $this->queryBuilder->buildFrom([$table], $params);
        $replacedQuotes = DbHelper::replaceQuotes($expected, $this->mock->getDriverName());

        $this->assertIsString($replacedQuotes);
        $this->assertSame('FROM ' . $replacedQuotes, $sql);
    }

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

yiisoft / db

Pull Request — master (#380)

QueryBuilderTest::testFromSubquery() A

Complexity

Size

Duplication

Importance

How to fix Long Method

Long Method

Duplication Side-by-Side

Filter issues like