QueryTest.php 6.18 KB
Newer Older
Qiang Xue committed
1 2
<?php

Qiang Xue committed
3
namespace yiiunit\framework\db;
Qiang Xue committed
4

Qiang Xue committed
5
use yii\db\Query;
Qiang Xue committed
6

7 8 9 10
/**
 * @group db
 * @group mysql
 */
Alexander Makarov committed
11
class QueryTest extends DatabaseTestCase
Qiang Xue committed
12
{
13 14 15 16 17 18 19 20 21 22 23 24 25 26
    public function testSelect()
    {
        // default
        $query = new Query;
        $query->select('*');
        $this->assertEquals(['*'], $query->select);
        $this->assertNull($query->distinct);
        $this->assertEquals(null, $query->selectOption);

        $query = new Query;
        $query->select('id, name', 'something')->distinct(true);
        $this->assertEquals(['id', 'name'], $query->select);
        $this->assertTrue($query->distinct);
        $this->assertEquals('something', $query->selectOption);
Alex-Code committed
27 28 29 30 31
        
        $query = new Query();
        $query->select('id, name');
        $query->addSelect('email');
        $this->assertEquals(['id', 'name', 'email'], $query->select);
32 33 34 35 36
    }

    public function testFrom()
    {
        $query = new Query;
37 38
        $query->from('user');
        $this->assertEquals(['user'], $query->from);
39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56
    }

    public function testWhere()
    {
        $query = new Query;
        $query->where('id = :id', [':id' => 1]);
        $this->assertEquals('id = :id', $query->where);
        $this->assertEquals([':id' => 1], $query->params);

        $query->andWhere('name = :name', [':name' => 'something']);
        $this->assertEquals(['and', 'id = :id', 'name = :name'], $query->where);
        $this->assertEquals([':id' => 1, ':name' => 'something'], $query->params);

        $query->orWhere('age = :age', [':age' => '30']);
        $this->assertEquals(['or', ['and', 'id = :id', 'name = :name'], 'age = :age'], $query->where);
        $this->assertEquals([':id' => 1, ':name' => 'something', ':age' => '30'], $query->params);
    }

Carsten Brandt committed
57
    public function testFilterWhere()
58
    {
Alexander Makarov committed
59 60
        // should work with hash format
        $query = new Query;
61
        $query->filterWhere([
Alexander Makarov committed
62 63 64 65 66
            'id' => 0,
            'title' => '   ',
            'author_ids' => [],
        ]);
        $this->assertEquals(['id' => 0], $query->where);
67

Carsten Brandt committed
68
        $query->andFilterWhere(['status' => null]);
Alexander Makarov committed
69 70
        $this->assertEquals(['id' => 0], $query->where);

Carsten Brandt committed
71
        $query->orFilterWhere(['name' => '']);
Alexander Makarov committed
72 73 74 75 76
        $this->assertEquals(['id' => 0], $query->where);

        // should work with operator format
        $query = new Query;
        $condition = ['like', 'name', 'Alex'];
77
        $query->filterWhere($condition);
Alexander Makarov committed
78 79
        $this->assertEquals($condition, $query->where);

Carsten Brandt committed
80
        $query->andFilterWhere(['between', 'id', null, null]);
Alexander Makarov committed
81 82
        $this->assertEquals($condition, $query->where);

Carsten Brandt committed
83
        $query->orFilterWhere(['not between', 'id', null, null]);
Alexander Makarov committed
84 85
        $this->assertEquals($condition, $query->where);

Carsten Brandt committed
86
        $query->andFilterWhere(['in', 'id', []]);
Alexander Makarov committed
87 88
        $this->assertEquals($condition, $query->where);

Carsten Brandt committed
89
        $query->andFilterWhere(['not in', 'id', []]);
Alexander Makarov committed
90 91
        $this->assertEquals($condition, $query->where);

Carsten Brandt committed
92
        $query->andFilterWhere(['not in', 'id', []]);
Alexander Makarov committed
93
        $this->assertEquals($condition, $query->where);
94

Carsten Brandt committed
95
        $query->andFilterWhere(['like', 'id', '']);
Alexander Makarov committed
96
        $this->assertEquals($condition, $query->where);
97

Carsten Brandt committed
98
        $query->andFilterWhere(['or like', 'id', '']);
Alexander Makarov committed
99
        $this->assertEquals($condition, $query->where);
100

Carsten Brandt committed
101
        $query->andFilterWhere(['not like', 'id', '   ']);
Alexander Makarov committed
102
        $this->assertEquals($condition, $query->where);
103

Carsten Brandt committed
104
        $query->andFilterWhere(['or not like', 'id', null]);
Alexander Makarov committed
105
        $this->assertEquals($condition, $query->where);
106 107
    }

108 109 110
    public function testFilterRecursively()
    {
        $query = new Query();
111
        $query->filterWhere(['and', ['like', 'name', ''], ['like', 'title', ''], ['id' => 1], ['not', ['like', 'name', '']]]);
Qiang Xue committed
112
        $this->assertEquals(['and', ['id' => 1]], $query->where);
113 114
    }

115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182
    public function testJoin()
    {
    }

    public function testGroup()
    {
        $query = new Query;
        $query->groupBy('team');
        $this->assertEquals(['team'], $query->groupBy);

        $query->addGroupBy('company');
        $this->assertEquals(['team', 'company'], $query->groupBy);

        $query->addGroupBy('age');
        $this->assertEquals(['team', 'company', 'age'], $query->groupBy);
    }

    public function testHaving()
    {
        $query = new Query;
        $query->having('id = :id', [':id' => 1]);
        $this->assertEquals('id = :id', $query->having);
        $this->assertEquals([':id' => 1], $query->params);

        $query->andHaving('name = :name', [':name' => 'something']);
        $this->assertEquals(['and', 'id = :id', 'name = :name'], $query->having);
        $this->assertEquals([':id' => 1, ':name' => 'something'], $query->params);

        $query->orHaving('age = :age', [':age' => '30']);
        $this->assertEquals(['or', ['and', 'id = :id', 'name = :name'], 'age = :age'], $query->having);
        $this->assertEquals([':id' => 1, ':name' => 'something', ':age' => '30'], $query->params);
    }

    public function testOrder()
    {
        $query = new Query;
        $query->orderBy('team');
        $this->assertEquals(['team' => SORT_ASC], $query->orderBy);

        $query->addOrderBy('company');
        $this->assertEquals(['team' => SORT_ASC, 'company' => SORT_ASC], $query->orderBy);

        $query->addOrderBy('age');
        $this->assertEquals(['team' => SORT_ASC, 'company' => SORT_ASC, 'age' => SORT_ASC], $query->orderBy);

        $query->addOrderBy(['age' => SORT_DESC]);
        $this->assertEquals(['team' => SORT_ASC, 'company' => SORT_ASC, 'age' => SORT_DESC], $query->orderBy);

        $query->addOrderBy('age ASC, company DESC');
        $this->assertEquals(['team' => SORT_ASC, 'company' => SORT_DESC, 'age' => SORT_ASC], $query->orderBy);
    }

    public function testLimitOffset()
    {
        $query = new Query;
        $query->limit(10)->offset(5);
        $this->assertEquals(10, $query->limit);
        $this->assertEquals(5, $query->offset);
    }

    public function testUnion()
    {
    }

    public function testOne()
    {
        $db = $this->getConnection();

183
        $result = (new Query)->from('customer')->where(['status' => 2])->one($db);
184 185
        $this->assertEquals('user3', $result['name']);

186
        $result = (new Query)->from('customer')->where(['status' => 3])->one($db);
187 188
        $this->assertFalse($result);
    }
Zander Baldwin committed
189
}