QueryBuilderTest::testBuildFrom() - 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:41 UTC

QueryBuilderTest::testBuildFrom() A

↳ Parent: QueryBuilderTest

Complexity

Conditions	1
Paths	1

Size

Total Lines	8
Code Lines	5

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

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

<?php

declare(strict_types=1);

namespace Yiisoft\Db\Tests;

use Closure;
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);
    }

    /**
     * @dataProvider \Yiisoft\Db\Tests\Provider\QueryBuilderProvider::createDropIndex()
     */
    public function testCreateDropIndex(string $sql, Closure $builder): void
    {
        $db = $this->mock->connection(true);
        $this->assertSame($db->getQuoter()->quoteSql($sql), $builder($db->getQueryBuilder()));
    }

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

yiisoft / db

Pull Request — master (#380)

QueryBuilderTest::testBuildFrom() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like