db

1
<?php
2

3
namespace Upside\Db\Test\SQL;
4

5
use Upside\Db\SQL\Expression;
6

7
class WhereTest extends BaseClass
8
{
9
    public function testWhereIs()
10
    {
11
        $expected = 'SELECT * FROM "users" WHERE "age" = 21';
12
        $actual = $this->db->from('users')->where('age')->is(21)->select();
13
        $this->assertEquals($expected, $actual);
14
    }
15

16
    public function testWhereIsNot()
17
    {
18
        $expected = 'SELECT * FROM "users" WHERE "age" != 21';
19
        $actual = $this->db->from('users')->where('age')->is_not(21)->select();
20
        $this->assertEquals($expected, $actual);
21
    }
22

23
    public function testWhereLT()
24
    {
25
        $expected = 'SELECT * FROM "users" WHERE "age" < 21';
26
        $actual = $this->db->from('users')->where('age')->less_than(21)->select();
27
        $this->assertEquals($expected, $actual);
28
    }
29

30
    public function testWhereLTAlt()
31
    {
32
        $expected = 'SELECT * FROM "users" WHERE "age" < 21';
33
        $actual = $this->db->from('users')->where('age')->lt(21)->select();
34
        $this->assertEquals($expected, $actual);
35
    }
36

37
    public function testWhereGT()
38
    {
39
        $expected = 'SELECT * FROM "users" WHERE "age" > 21';
40
        $actual = $this->db->from('users')->where('age')->greater_than(21)->select();
41
        $this->assertEquals($expected, $actual);
42
    }
43

44
    public function testWhereGTAlt()
45
    {
46
        $expected = 'SELECT * FROM "users" WHERE "age" > 21';
47
        $actual = $this->db->from('users')->where('age')->gt(21)->select();
48
        $this->assertEquals($expected, $actual);
49
    }
50

51
    public function testWhereLTE()
52
    {
53
        $expected = 'SELECT * FROM "users" WHERE "age" <= 21';
54
        $actual = $this->db->from('users')->where('age')->at_most(21)->select();
55
        $this->assertEquals($expected, $actual);
56
    }
57

58
    public function testWhereLTEAlt()
59
    {
60
        $expected = 'SELECT * FROM "users" WHERE "age" <= 21';
61
        $actual = $this->db->from('users')->where('age')->lte(21)->select();
62
        $this->assertEquals($expected, $actual);
63
    }
64

65
    public function testWhereGTE()
66
    {
67
        $expected = 'SELECT * FROM "users" WHERE "age" >= 21';
68
        $actual = $this->db->from('users')->where('age')->at_least(21)->select();
69
        $this->assertEquals($expected, $actual);
70
    }
71

72
    public function testWhereGTEAlt()
73
    {
74
        $expected = 'SELECT * FROM "users" WHERE "age" >= 21';
75
        $actual = $this->db->from('users')->where('age')->gte(21)->select();
76
        $this->assertEquals($expected, $actual);
77
    }
78

79
    public function testBetween()
80
    {
81
        $expected = 'SELECT * FROM "users" WHERE "age" BETWEEN 18 AND 21';
82
        $actual = $this->db->from('users')->where('age')->between(18, 21)->select();
83
        $this->assertEquals($expected, $actual);
84
    }
85

86
    public function testNotBetween()
87
    {
88
        $expected = 'SELECT * FROM "users" WHERE "age" NOT BETWEEN 18 AND 21';
89
        $actual = $this->db->from('users')->where('age')->not_between(18, 21)->select();
90
        $this->assertEquals($expected, $actual);
91
    }
92

93
    public function testWhereInArray()
94
    {
95
        $expected = 'SELECT * FROM "users" WHERE "age" IN (18, 21, 31)';
96
        $actual = $this->db->from('users')->where('age')->in([18, 21, 31])->select();
97
        $this->assertEquals($expected, $actual);
98
    }
99

100
    public function testWhereNotInArray()
101
    {
102
        $expected = 'SELECT * FROM "users" WHERE "age" NOT IN (18, 21, 31)';
103
        $actual = $this->db->from('users')->where('age')->not_in([18, 21, 31])->select();
104
        $this->assertEquals($expected, $actual);
105
    }
106

107
    public function testWhereInQuery()
108
    {
109
        $expected = 'SELECT * FROM "users" WHERE "age" IN (SELECT "name" FROM "customers")';
110
        $actual = $this->db->from('users')->where('age')->in(function ($query) {
111
            $query->from('customers')->select('name');
112
        })->select();
113
        $this->assertEquals($expected, $actual);
114
    }
115

116

117
    public function testWhereNotInQuery()
118
    {
119
        $expected = 'SELECT * FROM "users" WHERE "age" NOT IN (SELECT "name" FROM "customers")';
120
        $actual = $this->db->from('users')->where('age')->not_in(function ($query) {
121
            $query->from('customers')->select('name');
122
        })->select();;
123
        $this->assertEquals($expected, $actual);
124
    }
125

126
    public function testWhereLike()
127
    {
128
        $expected = 'SELECT * FROM "users" WHERE "name" LIKE \'%foo%\'';
129
        $actual = $this->db->from('users')->where('name')->like('%foo%')->select();
130
        $this->assertEquals($expected, $actual);
131
    }
132

133
    public function testWhereNotLike()
134
    {
135
        $expected = 'SELECT * FROM "users" WHERE "name" NOT LIKE \'%foo%\'';
136
        $actual = $this->db->from('users')->where('name')->not_like('%foo%')->select();
137
        $this->assertEquals($expected, $actual);
138
    }
139

140
    public function testWhereIsNull()
141
    {
142
        $expected = 'SELECT * FROM "users" WHERE "name" IS NULL';
143
        $actual = $this->db->from('users')->where('name')->is_null()->select();
144
        $this->assertEquals($expected, $actual);
145
    }
146

147
    public function testWhereIsNotNull()
148
    {
149
        $expected = 'SELECT * FROM "users" WHERE "name" IS NOT NULL';
150
        $actual = $this->db->from('users')->where('name')->not_null()->select();
151
        $this->assertEquals($expected, $actual);
152
    }
153

154
    public function testWhereAndCondition()
155
    {
156
        $expected = 'SELECT * FROM "users" WHERE "age" = 18 AND "city" = \'London\'';
157
        $actual = $this->db->from('users')
158
            ->where('age')->is(18)
159
            ->and_where('city')->is('London')
160
            ->select();
161
        $this->assertEquals($expected, $actual);
162
    }
163

164
    public function testWhereOrCondition()
165
    {
166
        $expected = 'SELECT * FROM "users" WHERE "age" = 18 OR "city" = \'London\'';
167
        $actual = $this->db->from('users')
168
            ->where('age')->is(18)
169
            ->or_where('city')->is('London')
170
            ->select();
171
        $this->assertEquals($expected, $actual);
172
    }
173

174
    public function testWhereGroupCondition()
175
    {
176
        $expected = 'SELECT * FROM "users" WHERE "age" = 18 AND ("city" = \'London\' OR "city" = \'Paris\')';
177
        $actual = $this->db->from('users')
178
            ->where('age')->is(18)
179
            ->and_where(function ($group) {
180
                $group->where('city')->is('London')
181
                    ->or_where('city')->is('Paris');
182
            })
183
            ->select();
184
        $this->assertEquals($expected, $actual);
185
    }
186

187
    public function testWhereIsColumn()
188
    {
189
        $expected = 'SELECT * FROM "users" WHERE "age" = "foo"';
190
        $actual = $this->db->from('users')->where('age')->is('foo', true)->select();
191
        $this->assertEquals($expected, $actual);
192
    }
193

194
    public function testWhereIsNotColumn()
195
    {
196
        $expected = 'SELECT * FROM "users" WHERE "age" != "foo"';
197
        $actual = $this->db->from('users')->where('age')->is_not('foo', true)->select();
198
        $this->assertEquals($expected, $actual);
199
    }
200

201
    public function testWhereLTColumn()
202
    {
203
        $expected = 'SELECT * FROM "users" WHERE "age" < "foo"';
204
        $actual = $this->db->from('users')->where('age')->less_than('foo', true)->select();
205
        $this->assertEquals($expected, $actual);
206
    }
207

208
    public function testWhereLTAltColumn()
209
    {
210
        $expected = 'SELECT * FROM "users" WHERE "age" < "foo"';
211
        $actual = $this->db->from('users')->where('age')->lt('foo', true)->select();
212
        $this->assertEquals($expected, $actual);
213
    }
214

215
    public function testWhereGTColumn()
216
    {
217
        $expected = 'SELECT * FROM "users" WHERE "age" > "foo"';
218
        $actual = $this->db->from('users')->where('age')->greater_than('foo', true)->select();
219
        $this->assertEquals($expected, $actual);
220
    }
221

222
    public function testWhereGTAltColumn()
223
    {
224
        $expected = 'SELECT * FROM "users" WHERE "age" > "foo"';
225
        $actual = $this->db->from('users')->where('age')->gt('foo', true)->select();
226
        $this->assertEquals($expected, $actual);
227
    }
228

229
    public function testWhereLTEColumn()
230
    {
231
        $expected = 'SELECT * FROM "users" WHERE "age" <= "foo"';
232
        $actual = $this->db->from('users')->where('age')->at_most('foo', true)->select();
233
        $this->assertEquals($expected, $actual);
234
    }
235

236
    public function testWhereLTEAltColumn()
237
    {
238
        $expected = 'SELECT * FROM "users" WHERE "age" <= "foo"';
239
        $actual = $this->db->from('users')->where('age')->lte('foo', true)->select();
240
        $this->assertEquals($expected, $actual);
241
    }
242

243
    public function testWhereGTEColumn()
244
    {
245
        $expected = 'SELECT * FROM "users" WHERE "age" >= "foo"';
246
        $actual = $this->db->from('users')->where('age')->at_least('foo', true)->select();
247
        $this->assertEquals($expected, $actual);
248
    }
249

250
    public function testWhereGTEAltColumn()
251
    {
252
        $expected = 'SELECT * FROM "users" WHERE "age" >= "foo"';
253
        $actual = $this->db->from('users')->where('age')->gte('foo', true)->select();
254
        $this->assertEquals($expected, $actual);
255
    }
256

257
    public function testWhereExists()
258
    {
259
        $expected = 'SELECT * FROM "users" WHERE EXISTS (SELECT * FROM "orders" WHERE "orders"."name" = "users"."name")';
260
        $actual = $this->db->from('users')
261
            ->where_exists(function ($query) {
262
                $query->from('orders')
263
                    ->where('orders.name')->eq('users.name', true)
264
                    ->select();
265
            })
266
            ->select();
267
        $this->assertEquals($expected, $actual);
268
    }
269

270
    public function testWhereExpression1()
271
    {
272
        $expected = 'SELECT * FROM "numbers" WHERE "c" = "b" + 10';
273
        $actual = $this->db->from('numbers')
274
            ->where('c')->eq(function ($expr) {
275
                $expr->column('b')->op('+')->value(10);
276
            })
277
            ->select();
278
        $this->assertEquals($expected, $actual);
279
    }
280

281
    public function testWhereExpression2()
282
    {
283
        $expected = 'SELECT * FROM "numbers" WHERE "c" = "a" + "b"';
284
        $actual = $this->db->from('numbers')
285
            ->where('c')->eq(function ($expr) {
286
                $expr->column('a')->{'+'}->column('b');
287
            })
288
            ->select();
289
        $this->assertEquals($expected, $actual);
290
    }
291

292
    public function testWhereExpression3()
293
    {
294
        $expected = 'SELECT * FROM "names" WHERE LCASE("name") LIKE \'%test%\'';
295
        $actual = $this->db->from('names')
296
            ->where(function (Expression $expr) {
297
                $expr->lcase('name');
298
            }, true)
299
            ->like('%test%')
300
            ->select();
301
        $this->assertEquals($expected, $actual);
302
    }
303

304
    public function testWhereNop() {
305
        $expected = 'SELECT * FROM "users" WHERE match( "username" ) against( \'expression\' )';
306
        $actual = $this->db->from('users')
307
            ->where(function (Expression $expr) {
308
                $expr->op('match(')->column('username')->op(') against(')
309
                    ->value('expression')->op(')');
310
            }, true)->nop()
311
            ->select();
312
        $this->assertEquals($expected, $actual);
313
    }
314
}