QueryTest.php 10.7 KB
Newer Older
Jens Segers's avatar
Jens Segers committed
1
<?php
Simon Schaufelberger's avatar
Simon Schaufelberger committed
2
declare(strict_types=1);
Jens Segers's avatar
Jens Segers committed
3

Jens Segers's avatar
Jens Segers committed
4 5
class QueryTest extends TestCase
{
Jens Segers's avatar
Jens Segers committed
6
    protected static $started = false;
7

Stas's avatar
Stas committed
8
    public function setUp(): void
Jens Segers's avatar
Jens Segers committed
9 10 11 12 13 14 15 16 17 18 19 20 21
    {
        parent::setUp();
        User::create(['name' => 'John Doe', 'age' => 35, 'title' => 'admin']);
        User::create(['name' => 'Jane Doe', 'age' => 33, 'title' => 'admin']);
        User::create(['name' => 'Harry Hoe', 'age' => 13, 'title' => 'user']);
        User::create(['name' => 'Robert Roe', 'age' => 37, 'title' => 'user']);
        User::create(['name' => 'Mark Moe', 'age' => 23, 'title' => 'user']);
        User::create(['name' => 'Brett Boe', 'age' => 35, 'title' => 'user']);
        User::create(['name' => 'Tommy Toe', 'age' => 33, 'title' => 'user']);
        User::create(['name' => 'Yvonne Yoe', 'age' => 35, 'title' => 'admin']);
        User::create(['name' => 'Error', 'age' => null, 'title' => null]);
    }

Stas's avatar
Stas committed
22
    public function tearDown(): void
Jens Segers's avatar
Jens Segers committed
23 24
    {
        User::truncate();
25
        Scoped::truncate();
Jens Segers's avatar
Jens Segers committed
26 27 28
        parent::tearDown();
    }

Simon Schaufelberger's avatar
Simon Schaufelberger committed
29
    public function testWhere(): void
Jens Segers's avatar
Jens Segers committed
30 31
    {
        $users = User::where('age', 35)->get();
Gabriel Caruso's avatar
Gabriel Caruso committed
32
        $this->assertCount(3, $users);
33

Jens Segers's avatar
Jens Segers committed
34
        $users = User::where('age', '=', 35)->get();
Gabriel Caruso's avatar
Gabriel Caruso committed
35
        $this->assertCount(3, $users);
Jens Segers's avatar
Jens Segers committed
36 37

        $users = User::where('age', '>=', 35)->get();
Gabriel Caruso's avatar
Gabriel Caruso committed
38
        $this->assertCount(4, $users);
Jens Segers's avatar
Jens Segers committed
39 40

        $users = User::where('age', '<=', 18)->get();
Gabriel Caruso's avatar
Gabriel Caruso committed
41
        $this->assertCount(1, $users);
Jens Segers's avatar
Jens Segers committed
42 43

        $users = User::where('age', '!=', 35)->get();
Gabriel Caruso's avatar
Gabriel Caruso committed
44
        $this->assertCount(6, $users);
Jens Segers's avatar
Jens Segers committed
45 46

        $users = User::where('age', '<>', 35)->get();
Gabriel Caruso's avatar
Gabriel Caruso committed
47
        $this->assertCount(6, $users);
Jens Segers's avatar
Jens Segers committed
48 49
    }

Simon Schaufelberger's avatar
Simon Schaufelberger committed
50
    public function testAndWhere(): void
Jens Segers's avatar
Jens Segers committed
51 52
    {
        $users = User::where('age', 35)->where('title', 'admin')->get();
Gabriel Caruso's avatar
Gabriel Caruso committed
53
        $this->assertCount(2, $users);
Jens Segers's avatar
Jens Segers committed
54 55

        $users = User::where('age', '>=', 35)->where('title', 'user')->get();
Gabriel Caruso's avatar
Gabriel Caruso committed
56
        $this->assertCount(2, $users);
Jens Segers's avatar
Jens Segers committed
57 58
    }

Simon Schaufelberger's avatar
Simon Schaufelberger committed
59
    public function testLike(): void
Jens Segers's avatar
Jens Segers committed
60 61
    {
        $users = User::where('name', 'like', '%doe')->get();
Gabriel Caruso's avatar
Gabriel Caruso committed
62
        $this->assertCount(2, $users);
Jens Segers's avatar
Jens Segers committed
63 64

        $users = User::where('name', 'like', '%y%')->get();
Gabriel Caruso's avatar
Gabriel Caruso committed
65
        $this->assertCount(3, $users);
Jens Segers's avatar
Jens Segers committed
66 67

        $users = User::where('name', 'LIKE', '%y%')->get();
Gabriel Caruso's avatar
Gabriel Caruso committed
68
        $this->assertCount(3, $users);
Jens Segers's avatar
Jens Segers committed
69 70

        $users = User::where('name', 'like', 't%')->get();
Gabriel Caruso's avatar
Gabriel Caruso committed
71
        $this->assertCount(1, $users);
Jens Segers's avatar
Jens Segers committed
72 73
    }

Simon Schaufelberger's avatar
Simon Schaufelberger committed
74
    public function testSelect(): void
Jens Segers's avatar
Jens Segers committed
75 76 77 78
    {
        $user = User::where('name', 'John Doe')->select('name')->first();

        $this->assertEquals('John Doe', $user->name);
Gabriel Caruso's avatar
Gabriel Caruso committed
79 80
        $this->assertNull($user->age);
        $this->assertNull($user->title);
Jens Segers's avatar
Jens Segers committed
81

Jens Segers's avatar
Jens Segers committed
82 83 84 85
        $user = User::where('name', 'John Doe')->select('name', 'title')->first();

        $this->assertEquals('John Doe', $user->name);
        $this->assertEquals('admin', $user->title);
Gabriel Caruso's avatar
Gabriel Caruso committed
86
        $this->assertNull($user->age);
Jens Segers's avatar
Jens Segers committed
87 88

        $user = User::where('name', 'John Doe')->select(['name', 'title'])->get()->first();
89

Jens Segers's avatar
Jens Segers committed
90 91
        $this->assertEquals('John Doe', $user->name);
        $this->assertEquals('admin', $user->title);
Gabriel Caruso's avatar
Gabriel Caruso committed
92
        $this->assertNull($user->age);
Jens Segers's avatar
Jens Segers committed
93 94 95 96

        $user = User::where('name', 'John Doe')->get(['name'])->first();

        $this->assertEquals('John Doe', $user->name);
Gabriel Caruso's avatar
Gabriel Caruso committed
97
        $this->assertNull($user->age);
Jens Segers's avatar
Jens Segers committed
98 99
    }

Simon Schaufelberger's avatar
Simon Schaufelberger committed
100
    public function testOrWhere(): void
Jens Segers's avatar
Jens Segers committed
101 102
    {
        $users = User::where('age', 13)->orWhere('title', 'admin')->get();
Gabriel Caruso's avatar
Gabriel Caruso committed
103
        $this->assertCount(4, $users);
Jens Segers's avatar
Jens Segers committed
104 105

        $users = User::where('age', 13)->orWhere('age', 23)->get();
Gabriel Caruso's avatar
Gabriel Caruso committed
106
        $this->assertCount(2, $users);
Jens Segers's avatar
Jens Segers committed
107 108
    }

Simon Schaufelberger's avatar
Simon Schaufelberger committed
109
    public function testBetween(): void
Jens Segers's avatar
Jens Segers committed
110 111
    {
        $users = User::whereBetween('age', [0, 25])->get();
Gabriel Caruso's avatar
Gabriel Caruso committed
112
        $this->assertCount(2, $users);
Jens Segers's avatar
Jens Segers committed
113 114

        $users = User::whereBetween('age', [13, 23])->get();
Gabriel Caruso's avatar
Gabriel Caruso committed
115
        $this->assertCount(2, $users);
Jens Segers's avatar
Jens Segers committed
116 117 118

        // testing whereNotBetween for version 4.1
        $users = User::whereBetween('age', [0, 25], 'and', true)->get();
Gabriel Caruso's avatar
Gabriel Caruso committed
119
        $this->assertCount(6, $users);
Jens Segers's avatar
Jens Segers committed
120
    }
Jens Segers's avatar
Jens Segers committed
121

Simon Schaufelberger's avatar
Simon Schaufelberger committed
122
    public function testIn(): void
Jens Segers's avatar
Jens Segers committed
123 124
    {
        $users = User::whereIn('age', [13, 23])->get();
Gabriel Caruso's avatar
Gabriel Caruso committed
125
        $this->assertCount(2, $users);
Jens Segers's avatar
Jens Segers committed
126 127

        $users = User::whereIn('age', [33, 35, 13])->get();
Gabriel Caruso's avatar
Gabriel Caruso committed
128
        $this->assertCount(6, $users);
Jens Segers's avatar
Jens Segers committed
129 130

        $users = User::whereNotIn('age', [33, 35])->get();
Gabriel Caruso's avatar
Gabriel Caruso committed
131
        $this->assertCount(4, $users);
Jens Segers's avatar
Jens Segers committed
132

Jens Segers's avatar
Jens Segers committed
133
        $users = User::whereNotNull('age')
Jens Segers's avatar
Jens Segers committed
134
            ->whereNotIn('age', [33, 35])->get();
Gabriel Caruso's avatar
Gabriel Caruso committed
135
        $this->assertCount(3, $users);
Jens Segers's avatar
Jens Segers committed
136 137
    }

Simon Schaufelberger's avatar
Simon Schaufelberger committed
138
    public function testWhereNull(): void
Jens Segers's avatar
Jens Segers committed
139 140
    {
        $users = User::whereNull('age')->get();
Gabriel Caruso's avatar
Gabriel Caruso committed
141
        $this->assertCount(1, $users);
Jens Segers's avatar
Jens Segers committed
142 143
    }

Simon Schaufelberger's avatar
Simon Schaufelberger committed
144
    public function testWhereNotNull(): void
Jens Segers's avatar
Jens Segers committed
145 146
    {
        $users = User::whereNotNull('age')->get();
Gabriel Caruso's avatar
Gabriel Caruso committed
147
        $this->assertCount(8, $users);
Jens Segers's avatar
Jens Segers committed
148 149
    }

Simon Schaufelberger's avatar
Simon Schaufelberger committed
150
    public function testOrder(): void
Jens Segers's avatar
Jens Segers committed
151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170
    {
        $user = User::whereNotNull('age')->orderBy('age', 'asc')->first();
        $this->assertEquals(13, $user->age);

        $user = User::whereNotNull('age')->orderBy('age', 'ASC')->first();
        $this->assertEquals(13, $user->age);

        $user = User::whereNotNull('age')->orderBy('age', 'desc')->first();
        $this->assertEquals(37, $user->age);

        $user = User::whereNotNull('age')->orderBy('natural', 'asc')->first();
        $this->assertEquals(35, $user->age);

        $user = User::whereNotNull('age')->orderBy('natural', 'ASC')->first();
        $this->assertEquals(35, $user->age);

        $user = User::whereNotNull('age')->orderBy('natural', 'desc')->first();
        $this->assertEquals(35, $user->age);
    }

Simon Schaufelberger's avatar
Simon Schaufelberger committed
171
    public function testGroupBy(): void
Jens Segers's avatar
Jens Segers committed
172 173
    {
        $users = User::groupBy('title')->get();
Gabriel Caruso's avatar
Gabriel Caruso committed
174
        $this->assertCount(3, $users);
Jens Segers's avatar
Jens Segers committed
175 176

        $users = User::groupBy('age')->get();
Gabriel Caruso's avatar
Gabriel Caruso committed
177
        $this->assertCount(6, $users);
Jens Segers's avatar
Jens Segers committed
178 179

        $users = User::groupBy('age')->skip(1)->get();
Gabriel Caruso's avatar
Gabriel Caruso committed
180
        $this->assertCount(5, $users);
Jens Segers's avatar
Jens Segers committed
181 182

        $users = User::groupBy('age')->take(2)->get();
Gabriel Caruso's avatar
Gabriel Caruso committed
183
        $this->assertCount(2, $users);
Jens Segers's avatar
Jens Segers committed
184 185 186 187 188 189 190

        $users = User::groupBy('age')->orderBy('age', 'desc')->get();
        $this->assertEquals(37, $users[0]->age);
        $this->assertEquals(35, $users[1]->age);
        $this->assertEquals(33, $users[2]->age);

        $users = User::groupBy('age')->skip(1)->take(2)->orderBy('age', 'desc')->get();
Gabriel Caruso's avatar
Gabriel Caruso committed
191
        $this->assertCount(2, $users);
Jens Segers's avatar
Jens Segers committed
192 193 194 195 196
        $this->assertEquals(35, $users[0]->age);
        $this->assertEquals(33, $users[1]->age);
        $this->assertNull($users[0]->name);

        $users = User::select('name')->groupBy('age')->skip(1)->take(2)->orderBy('age', 'desc')->get();
Gabriel Caruso's avatar
Gabriel Caruso committed
197
        $this->assertCount(2, $users);
Jens Segers's avatar
Jens Segers committed
198 199 200
        $this->assertNotNull($users[0]->name);
    }

Simon Schaufelberger's avatar
Simon Schaufelberger committed
201
    public function testCount(): void
Jens Segers's avatar
Jens Segers committed
202 203 204 205 206 207 208 209 210
    {
        $count = User::where('age', '<>', 35)->count();
        $this->assertEquals(6, $count);

        // Test for issue #165
        $count = User::select('_id', 'age', 'title')->where('age', '<>', 35)->count();
        $this->assertEquals(6, $count);
    }

Simon Schaufelberger's avatar
Simon Schaufelberger committed
211
    public function testExists(): void
212 213 214 215 216
    {
        $this->assertFalse(User::where('age', '>', 37)->exists());
        $this->assertTrue(User::where('age', '<', 37)->exists());
    }

Simon Schaufelberger's avatar
Simon Schaufelberger committed
217
    public function testSubQuery(): void
Jens Segers's avatar
Jens Segers committed
218
    {
Jens Segers's avatar
Jens Segers committed
219
        $users = User::where('title', 'admin')->orWhere(function ($query) {
220
            $query->where('name', 'Tommy Toe')
Jens Segers's avatar
Jens Segers committed
221
                ->orWhere('name', 'Error');
222
        })
Jens Segers's avatar
Jens Segers committed
223 224
            ->get();

Gabriel Caruso's avatar
Gabriel Caruso committed
225
        $this->assertCount(5, $users);
Jens Segers's avatar
Jens Segers committed
226

Jens Segers's avatar
Jens Segers committed
227
        $users = User::where('title', 'user')->where(function ($query) {
228
            $query->where('age', 35)
Jens Segers's avatar
Jens Segers committed
229
                ->orWhere('name', 'like', '%harry%');
230
        })
Jens Segers's avatar
Jens Segers committed
231 232
            ->get();

Gabriel Caruso's avatar
Gabriel Caruso committed
233
        $this->assertCount(2, $users);
Jens Segers's avatar
Jens Segers committed
234

Jens Segers's avatar
Jens Segers committed
235
        $users = User::where('age', 35)->orWhere(function ($query) {
236
            $query->where('title', 'admin')
Jens Segers's avatar
Jens Segers committed
237
                ->orWhere('name', 'Error');
238
        })
Jens Segers's avatar
Jens Segers committed
239 240
            ->get();

Gabriel Caruso's avatar
Gabriel Caruso committed
241
        $this->assertCount(5, $users);
Jens Segers's avatar
Jens Segers committed
242 243

        $users = User::whereNull('deleted_at')
Jens Segers's avatar
Jens Segers committed
244 245 246 247 248 249
            ->where('title', 'admin')
            ->where(function ($query) {
                $query->where('age', '>', 15)
                    ->orWhere('name', 'Harry Hoe');
            })
            ->get();
Jens Segers's avatar
Jens Segers committed
250 251 252 253

        $this->assertEquals(3, $users->count());

        $users = User::whereNull('deleted_at')
Jens Segers's avatar
Jens Segers committed
254 255 256 257 258 259 260 261
            ->where(function ($query) {
                $query->where('name', 'Harry Hoe')
                    ->orWhere(function ($query) {
                        $query->where('age', '>', 15)
                            ->where('title', '<>', 'admin');
                    });
            })
            ->get();
Jens Segers's avatar
Jens Segers committed
262 263 264 265

        $this->assertEquals(5, $users->count());
    }

Simon Schaufelberger's avatar
Simon Schaufelberger committed
266
    public function testWhereRaw(): void
Jens Segers's avatar
Jens Segers committed
267 268 269 270
    {
        $where = ['age' => ['$gt' => 30, '$lt' => 40]];
        $users = User::whereRaw($where)->get();

Gabriel Caruso's avatar
Gabriel Caruso committed
271
        $this->assertCount(6, $users);
Jens Segers's avatar
Jens Segers committed
272 273

        $where1 = ['age' => ['$gt' => 30, '$lte' => 35]];
Jens Segers's avatar
Jens Segers committed
274
        $where2 = ['age' => ['$gt' => 35, '$lt' => 40]];
Jens Segers's avatar
Jens Segers committed
275 276
        $users = User::whereRaw($where1)->orWhereRaw($where2)->get();

Gabriel Caruso's avatar
Gabriel Caruso committed
277
        $this->assertCount(6, $users);
Jens Segers's avatar
Jens Segers committed
278 279
    }

Simon Schaufelberger's avatar
Simon Schaufelberger committed
280
    public function testMultipleOr(): void
Jens Segers's avatar
Jens Segers committed
281
    {
Jens Segers's avatar
Jens Segers committed
282
        $users = User::where(function ($query) {
Jens Segers's avatar
Jens Segers committed
283 284
            $query->where('age', 35)->orWhere('age', 33);
        })
Jens Segers's avatar
Jens Segers committed
285 286 287
            ->where(function ($query) {
                $query->where('name', 'John Doe')->orWhere('name', 'Jane Doe');
            })->get();
Jens Segers's avatar
Jens Segers committed
288

Gabriel Caruso's avatar
Gabriel Caruso committed
289
        $this->assertCount(2, $users);
Jens Segers's avatar
Jens Segers committed
290

Jens Segers's avatar
Jens Segers committed
291
        $users = User::where(function ($query) {
Jens Segers's avatar
Jens Segers committed
292 293
            $query->orWhere('age', 35)->orWhere('age', 33);
        })
Jens Segers's avatar
Jens Segers committed
294 295 296
            ->where(function ($query) {
                $query->orWhere('name', 'John Doe')->orWhere('name', 'Jane Doe');
            })->get();
Jens Segers's avatar
Jens Segers committed
297

Gabriel Caruso's avatar
Gabriel Caruso committed
298
        $this->assertCount(2, $users);
Jens Segers's avatar
Jens Segers committed
299 300
    }

Simon Schaufelberger's avatar
Simon Schaufelberger committed
301
    public function testPaginate(): void
Jens Segers's avatar
Jens Segers committed
302 303 304 305 306 307 308 309 310 311 312 313
    {
        $results = User::paginate(2);
        $this->assertEquals(2, $results->count());
        $this->assertNotNull($results->first()->title);
        $this->assertEquals(9, $results->total());

        $results = User::paginate(2, ['name', 'age']);
        $this->assertEquals(2, $results->count());
        $this->assertNull($results->first()->title);
        $this->assertEquals(9, $results->total());
        $this->assertEquals(1, $results->currentPage());
    }
314

Simon Schaufelberger's avatar
Simon Schaufelberger committed
315
    public function testUpdate(): void
316 317 318 319 320 321 322 323 324 325 326 327 328 329 330
    {
        $this->assertEquals(1, User::where(['name' => 'John Doe'])->update(['name' => 'Jim Morrison']));
        $this->assertEquals(1, User::where(['name' => 'Jim Morrison'])->count());

        Scoped::create(['favorite' => true]);
        Scoped::create(['favorite' => false]);

        $this->assertCount(1, Scoped::get());
        $this->assertEquals(1, Scoped::query()->update(['name' => 'Johnny']));
        $this->assertCount(1, Scoped::withoutGlobalScopes()->where(['name' => 'Johnny'])->get());

        $this->assertCount(2, Scoped::withoutGlobalScopes()->get());
        $this->assertEquals(2, Scoped::withoutGlobalScopes()->update(['name' => 'Jimmy']));
        $this->assertCount(2, Scoped::withoutGlobalScopes()->where(['name' => 'Jimmy'])->get());
    }
331
}