1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
|
<?php
namespace Illuminate\Tests\Integration\Database;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
class QueryBuilderWhereLikeTest extends DatabaseTestCase
{
protected function afterRefreshingDatabase()
{
Schema::create('users', function (Blueprint $table) {
$table->id('id');
$table->string('name', 200);
$table->text('email');
});
}
protected function destroyDatabaseMigrations()
{
Schema::drop('users');
}
protected function setUp(): void
{
parent::setUp();
DB::table('users')->insert([
['name' => 'John Doe', 'email' => 'John.Doe@example.com'],
['name' => 'Jane Doe', 'email' => 'janedoe@example.com'],
['name' => 'Dale doe', 'email' => 'Dale.Doe@example.com'],
['name' => 'Earl Smith', 'email' => 'Earl.Smith@example.com'],
['name' => 'tim smith', 'email' => 'tim.smith@example.com'],
]);
}
public function testWhereLike()
{
$users = DB::table('users')->whereLike('email', 'john.doe@example.com')->get();
$this->assertCount(1, $users);
$this->assertSame('John.Doe@example.com', $users[0]->email);
$this->assertSame(4, DB::table('users')->whereNotLike('email', 'john.doe@example.com')->count());
}
public function testWhereLikeWithPercentWildcard()
{
$this->assertSame(5, DB::table('users')->whereLike('email', '%@example.com')->count());
$this->assertSame(2, DB::table('users')->whereNotLike('email', '%Doe%')->count());
$users = DB::table('users')->whereLike('email', 'john%')->get();
$this->assertCount(1, $users);
$this->assertSame('John.Doe@example.com', $users[0]->email);
}
public function testWhereLikeWithUnderscoreWildcard()
{
$users = DB::table('users')->whereLike('email', '_a_e_%@example.com')->get();
$this->assertCount(2, $users);
$this->assertSame('janedoe@example.com', $users[0]->email);
$this->assertSame('Dale.Doe@example.com', $users[1]->email);
}
public function testWhereLikeCaseSensitive()
{
if ($this->driver === 'sqlsrv') {
$this->markTestSkipped('The case-sensitive whereLike clause is not supported on MSSQL.');
}
$users = DB::table('users')->whereLike('email', 'john.doe@example.com', true)->get();
$this->assertCount(0, $users);
$users = DB::table('users')->whereLike('email', 'tim.smith@example.com', true)->get();
$this->assertCount(1, $users);
$this->assertSame('tim.smith@example.com', $users[0]->email);
$this->assertSame(5, DB::table('users')->whereNotLike('email', 'john.doe@example.com', true)->count());
}
public function testWhereLikeWithPercentWildcardCaseSensitive()
{
if ($this->driver === 'sqlsrv') {
$this->markTestSkipped('The case-sensitive whereLike clause is not supported on MSSQL.');
}
$this->assertSame(2, DB::table('users')->whereLike('email', '%Doe@example.com', true)->count());
$this->assertSame(4, DB::table('users')->whereNotLike('email', '%smith%', true)->count());
$users = DB::table('users')->whereLike('email', '%Doe@example.com', true)->get();
$this->assertCount(2, $users);
$this->assertSame('John.Doe@example.com', $users[0]->email);
$this->assertSame('Dale.Doe@example.com', $users[1]->email);
}
public function testWhereLikeWithUnderscoreWildcardCaseSensitive()
{
if ($this->driver === 'sqlsrv') {
$this->markTestSkipped('The case-sensitive whereLike clause is not supported on MSSQL.');
}
$users = DB::table('users')->whereLike('email', 'j__edoe@example.com', true)->get();
$this->assertCount(1, $users);
$this->assertSame('janedoe@example.com', $users[0]->email);
$users = DB::table('users')->whereNotLike('email', '%_oe@example.com', true)->get();
$this->assertCount(2, $users);
$this->assertSame('Earl.Smith@example.com', $users[0]->email);
$this->assertSame('tim.smith@example.com', $users[1]->email);
}
}
|
