Completed
Push — master ( 7ef84c...fcafb3 )
by Woody
03:32
created

SelectTest::testOrderByExpression()   A

Complexity

Conditions 1
Paths 1

Size

Total Lines 9
Code Lines 6

Duplication

Lines 0
Ratio 0 %

Importance

Changes 0
Metric Value
cc 1
eloc 6
nc 1
nop 0
dl 0
loc 9
rs 10
c 0
b 0
f 0
1
<?php
2
3
namespace Latitude\QueryBuilder\Query;
4
5
use Latitude\QueryBuilder\TestCase;
6
7
use function Latitude\QueryBuilder\alias;
8
use function Latitude\QueryBuilder\express;
9
use function Latitude\QueryBuilder\field;
10
use function Latitude\QueryBuilder\fn;
11
use function Latitude\QueryBuilder\identify;
12
use function Latitude\QueryBuilder\on;
13
14
class SelectTest extends TestCase
15
{
16
    public function testSelect()
17
    {
18
        $select = $this->factory
19
            ->select()
20
            ->from('users');
21
22
        $this->assertSql('SELECT * FROM users', $select);
23
        $this->assertParams([], $select);
24
    }
25
26
    public function testDistinct()
27
    {
28
        $select = $this->factory
29
            ->select()
30
            ->distinct();
31
32
        $this->assertSql('SELECT DISTINCT *', $select);
33
        $this->assertParams([], $select);
34
    }
35
36
    public function testColumns()
37
    {
38
        $select = $this->factory
39
            ->select('id', 'username')
40
            ->from('users');
41
42
        $this->assertSql('SELECT id, username FROM users', $select);
43
        $this->assertParams([], $select);
44
    }
45
46
    public function testJoin()
47
    {
48
        $select = $this->factory
49
            ->select('u.username', 'r.role', 'c.country')
50
            ->from(alias('users', 'u'))
51
            ->join(alias('roles', 'r'), on('u.role_id', 'r.id'))
52
            ->join(alias('countries', 'c'), on('u.country_id', 'c.id'));
53
54
        $expected = implode(' ', [
55
            'SELECT u.username, r.role, c.country',
56
            'FROM users AS u',
57
            'JOIN roles AS r ON u.role_id = r.id',
58
            'JOIN countries AS c ON u.country_id = c.id',
59
        ]);
60
61
        $this->assertSql($expected, $select);
62
        $this->assertParams([], $select);
63
    }
64
65
    public function testJoinInner()
66
    {
67
        $select = $this->factory
68
            ->select('u.username', 'c.country')
69
            ->from(alias('users', 'u'))
70
            ->innerJoin(alias('countries', 'c'), on('u.country_id', 'c.id'));
71
72
        $expected = implode(' ', [
73
            'SELECT u.username, c.country',
74
            'FROM users AS u',
75
            'INNER JOIN countries AS c ON u.country_id = c.id',
76
        ]);
77
78
        $this->assertSql($expected, $select);
79
        $this->assertParams([], $select);
80
    }
81
82
    public function testJoinLeft()
83
    {
84
        $select = $this->factory
85
            ->select('u.username', 'c.country')
86
            ->from(alias('users', 'u'))
87
            ->leftJoin(alias('countries', 'c'), on('u.country_id', 'c.id'));
88
89
        $expected = implode(' ', [
90
            'SELECT u.username, c.country',
91
            'FROM users AS u',
92
            'LEFT JOIN countries AS c ON u.country_id = c.id',
93
        ]);
94
95
        $this->assertSql($expected, $select);
96
        $this->assertParams([], $select);
97
    }
98
99
    public function testJoinRight()
100
    {
101
        $select = $this->factory
102
            ->select('u.username', 'c.country')
103
            ->from(alias('users', 'u'))
104
            ->rightJoin(alias('countries', 'c'), on('u.country_id', 'c.id'));
105
106
        $expected = implode(' ', [
107
            'SELECT u.username, c.country',
108
            'FROM users AS u',
109
            'RIGHT JOIN countries AS c ON u.country_id = c.id',
110
        ]);
111
112
        $this->assertSql($expected, $select);
113
        $this->assertParams([], $select);
114
    }
115
116
    public function testJoinFull()
117
    {
118
        $select = $this->factory
119
            ->select('u.username', 'c.country')
120
            ->from(alias('users', 'u'))
121
            ->fullJoin(alias('countries', 'c'), on('u.country_id', 'c.id'));
122
123
        $expected = implode(' ', [
124
            'SELECT u.username, c.country',
125
            'FROM users AS u',
126
            'FULL JOIN countries AS c ON u.country_id = c.id',
127
        ]);
128
129
        $this->assertSql($expected, $select);
130
        $this->assertParams([], $select);
131
    }
132
133
    public function testWhere()
134
    {
135
        $select = $this->factory
136
            ->select()
137
            ->from('users')
138
            ->where(field('id')->eq(1));
139
140
        $this->assertSql('SELECT * FROM users WHERE id = ?', $select);
141
        $this->assertParams([1], $select);
142
    }
143
144
    public function testWhereAnd()
145
    {
146
        $select = $this->factory
147
            ->select()
148
            ->from('users')
149
            ->andWhere(field('id')->eq(1))
150
            ->andWhere(field('username')->eq('admin'));
151
152
        $this->assertSql('SELECT * FROM users WHERE id = ? AND username = ?', $select);
153
        $this->assertParams([1, 'admin'], $select);
154
    }
155
156
    public function testWhereOr()
157
    {
158
        $select = $this->factory
159
            ->select()
160
            ->from('countries')
161
            ->orWhere(field('country')->eq('JP'))
162
            ->orWhere(field('country')->eq('CN'));
163
164
        $this->assertSql('SELECT * FROM countries WHERE country = ? OR country = ?', $select);
165
        $this->assertParams(['JP', 'CN'], $select);
166
    }
167
168
    public function testGroupBy()
169
    {
170
        $select = $this->factory
171
            ->select(
172
                alias(fn('COUNT', 'id'), 'total')
173
            )
174
            ->from('employees')
175
            ->groupBy('department');
176
177
        $expected = implode(' ', [
178
            'SELECT COUNT(id) AS total',
179
            'FROM employees',
180
            'GROUP BY department',
181
        ]);
182
183
        $this->assertSql($expected, $select);
184
        $this->assertParams([], $select);
185
    }
186
187
    public function testHaving()
188
    {
189
        $select = $this->factory
190
            ->select(
191
                'department',
192
                alias($sum = fn('SUM', 'salary'), 'total')
193
            )
194
            ->from('employees')
195
            ->groupBy('department')
196
            ->having(field($sum)->gt(5000));
197
198
        $expected = implode(' ', [
199
            'SELECT department, SUM(salary) AS total',
200
            'FROM employees',
201
            'GROUP BY department',
202
            'HAVING SUM(salary) > ?',
203
        ]);
204
205
        $this->assertSql($expected, $select);
206
        $this->assertParams([5000], $select);
207
    }
208
209
    public function testOrderBy()
210
    {
211
        $select = $this->factory
212
            ->select()
213
            ->from('users')
214
            ->orderBy('birthday');
215
216
        $this->assertSql('SELECT * FROM users ORDER BY birthday', $select);
217
        $this->assertParams([], $select);
218
    }
219
220
    public function testOrderByDirection()
221
    {
222
        $select = $this->factory
223
            ->select(
224
                'u.id',
225
                'u.username',
226
                alias(fn('COUNT', 'l.id'), 'total')
227
            )
228
            ->from(alias('users', 'u'))
229
            ->join(alias('logins', 'l'), on('u.id', 'l.user_id'))
230
            ->groupBy('l.user_id')
231
            ->orderBy('u.username')
232
            ->orderBy('total', 'desc');
233
234
        $expected = implode(' ', [
235
            'SELECT u.id, u.username, COUNT(l.id) AS total',
236
            'FROM users AS u',
237
            'JOIN logins AS l ON u.id = l.user_id',
238
            'GROUP BY l.user_id',
239
            'ORDER BY u.username, total DESC',
240
        ]);
241
242
        $this->assertSql($expected, $select);
243
        $this->assertParams([], $select);
244
    }
245
246
    public function testOrderByReset()
247
    {
248
        $select = $this->factory
249
            ->select()
250
            ->from('users')
251
            ->orderBy('birthday');
252
253
        $select->orderBy(null);
254
255
        $this->assertSql('SELECT * FROM users', $select);
256
        $this->assertParams([], $select);
257
    }
258
259
    public function testOrderByExpression()
260
    {
261
        $select = $this->factory
262
            ->select()
263
            ->from('users')
264
            ->orderBy(express("FIELD(%s, 'off')", identify('status')), 'DESC');
265
266
        $this->assertSql("SELECT * FROM users ORDER BY FIELD(status, 'off') DESC", $select);
267
        $this->assertParams([], $select);
268
    }
269
270
    public function testOffsetLimit()
271
    {
272
        $select = $this->factory
273
            ->select()
274
            ->from('users')
275
            ->limit(10)
276
            ->offset(100);
277
278
        $this->assertSql('SELECT * FROM users LIMIT 10 OFFSET 100', $select);
279
        $this->assertParams([], $select);
280
    }
281
282
    public function testUnion()
283
    {
284
        $a = $this->factory->select('supplier_id')->from('suppliers');
285
        $b = $this->factory->select('supplier_id')->from('orders');
286
287
        $union = $a->union($b)->orderBy('supplier_id', 'desc');
288
289
        $expected = implode(' ', [
290
            'SELECT supplier_id FROM suppliers',
291
            'UNION',
292
            'SELECT supplier_id FROM orders',
293
            'ORDER BY supplier_id DESC'
294
        ]);
295
296
        $this->assertSql($expected, $union);
297
        $this->assertParams([], $union);
298
    }
299
300
    public function testUnionAll()
301
    {
302
        $a = $this->factory->select('first_name', 'last_name')->from('employees');
303
        $b = $this->factory->select('first_name', 'last_name')->from('customers');
304
        $c = $this->factory->select('first_name', 'last_name')->from('partners');
305
306
        $union = $a->unionAll($b)->unionAll($c);
307
308
        $expected = implode(' ', [
309
            'SELECT first_name, last_name FROM employees',
310
            'UNION ALL',
311
            'SELECT first_name, last_name FROM customers',
312
            'UNION ALL',
313
            'SELECT first_name, last_name FROM partners',
314
        ]);
315
316
        $this->assertSql($expected, $union);
317
        $this->assertParams([], $union);
318
    }
319
}
320