1
|
|
|
<?php |
2
|
|
|
|
3
|
|
|
declare(strict_types=1); |
4
|
|
|
|
5
|
|
|
namespace Doctrine\DBAL\Tests\Query; |
6
|
|
|
|
7
|
|
|
use Doctrine\DBAL\Connection; |
8
|
|
|
use Doctrine\DBAL\ParameterType; |
9
|
|
|
use Doctrine\DBAL\Query\Expression\ExpressionBuilder; |
10
|
|
|
use Doctrine\DBAL\Query\QueryBuilder; |
11
|
|
|
use Doctrine\DBAL\Query\QueryException; |
12
|
|
|
use PHPUnit\Framework\TestCase; |
13
|
|
|
|
14
|
|
|
/** |
15
|
|
|
* @group DBAL-12 |
16
|
|
|
*/ |
17
|
|
|
class QueryBuilderTest extends TestCase |
18
|
|
|
{ |
19
|
|
|
/** @var Connection */ |
20
|
|
|
protected $conn; |
21
|
|
|
|
22
|
|
|
protected function setUp() : void |
23
|
|
|
{ |
24
|
|
|
$this->conn = $this->createMock(Connection::class); |
25
|
|
|
|
26
|
|
|
$expressionBuilder = new ExpressionBuilder($this->conn); |
27
|
|
|
|
28
|
|
|
$this->conn->expects(self::any()) |
29
|
|
|
->method('getExpressionBuilder') |
30
|
|
|
->will(self::returnValue($expressionBuilder)); |
31
|
|
|
} |
32
|
|
|
|
33
|
|
|
/** |
34
|
|
|
* @group DBAL-2291 |
35
|
|
|
*/ |
36
|
|
|
public function testSimpleSelectWithoutFrom() : void |
37
|
|
|
{ |
38
|
|
|
$qb = new QueryBuilder($this->conn); |
39
|
|
|
|
40
|
|
|
$qb->select('some_function()'); |
41
|
|
|
|
42
|
|
|
self::assertEquals('SELECT some_function()', (string) $qb); |
43
|
|
|
} |
44
|
|
|
|
45
|
|
|
public function testSimpleSelect() : void |
46
|
|
|
{ |
47
|
|
|
$qb = new QueryBuilder($this->conn); |
48
|
|
|
|
49
|
|
|
$qb->select('u.id') |
50
|
|
|
->from('users', 'u'); |
51
|
|
|
|
52
|
|
|
self::assertEquals('SELECT u.id FROM users u', (string) $qb); |
53
|
|
|
} |
54
|
|
|
|
55
|
|
|
public function testSimpleSelectWithDistinct() : void |
56
|
|
|
{ |
57
|
|
|
$qb = new QueryBuilder($this->conn); |
58
|
|
|
|
59
|
|
|
$qb->select('u.id') |
60
|
|
|
->distinct() |
61
|
|
|
->from('users', 'u'); |
62
|
|
|
|
63
|
|
|
self::assertEquals('SELECT DISTINCT u.id FROM users u', (string) $qb); |
64
|
|
|
} |
65
|
|
|
|
66
|
|
|
public function testSelectWithSimpleWhere() : void |
67
|
|
|
{ |
68
|
|
|
$qb = new QueryBuilder($this->conn); |
69
|
|
|
$expr = $qb->expr(); |
70
|
|
|
|
71
|
|
|
$qb->select('u.id') |
72
|
|
|
->from('users', 'u') |
73
|
|
|
->where($expr->and($expr->eq('u.nickname', '?'))); |
74
|
|
|
|
75
|
|
|
self::assertEquals('SELECT u.id FROM users u WHERE u.nickname = ?', (string) $qb); |
76
|
|
|
} |
77
|
|
|
|
78
|
|
|
public function testSelectWithLeftJoin() : void |
79
|
|
|
{ |
80
|
|
|
$qb = new QueryBuilder($this->conn); |
81
|
|
|
$expr = $qb->expr(); |
82
|
|
|
|
83
|
|
|
$qb->select('u.*', 'p.*') |
84
|
|
|
->from('users', 'u') |
85
|
|
|
->leftJoin('u', 'phones', 'p', $expr->eq('p.user_id', 'u.id')); |
86
|
|
|
|
87
|
|
|
self::assertEquals('SELECT u.*, p.* FROM users u LEFT JOIN phones p ON p.user_id = u.id', (string) $qb); |
88
|
|
|
} |
89
|
|
|
|
90
|
|
|
public function testSelectWithJoin() : void |
91
|
|
|
{ |
92
|
|
|
$qb = new QueryBuilder($this->conn); |
93
|
|
|
$expr = $qb->expr(); |
94
|
|
|
|
95
|
|
|
$qb->select('u.*', 'p.*') |
96
|
|
|
->from('users', 'u') |
97
|
|
|
->join('u', 'phones', 'p', $expr->eq('p.user_id', 'u.id')); |
98
|
|
|
|
99
|
|
|
self::assertEquals('SELECT u.*, p.* FROM users u INNER JOIN phones p ON p.user_id = u.id', (string) $qb); |
100
|
|
|
} |
101
|
|
|
|
102
|
|
|
public function testSelectWithJoinNoCondition() : void |
103
|
|
|
{ |
104
|
|
|
$qb = new QueryBuilder($this->conn); |
105
|
|
|
|
106
|
|
|
$qb->select('u.*', 'p.*') |
107
|
|
|
->from('users', 'u') |
108
|
|
|
->join('u', 'phones', 'p'); |
109
|
|
|
|
110
|
|
|
self::assertEquals('SELECT u.*, p.* FROM users u INNER JOIN phones p', (string) $qb); |
111
|
|
|
} |
112
|
|
|
|
113
|
|
|
public function testSelectWithInnerJoin() : void |
114
|
|
|
{ |
115
|
|
|
$qb = new QueryBuilder($this->conn); |
116
|
|
|
$expr = $qb->expr(); |
117
|
|
|
|
118
|
|
|
$qb->select('u.*', 'p.*') |
119
|
|
|
->from('users', 'u') |
120
|
|
|
->innerJoin('u', 'phones', 'p', $expr->eq('p.user_id', 'u.id')); |
121
|
|
|
|
122
|
|
|
self::assertEquals('SELECT u.*, p.* FROM users u INNER JOIN phones p ON p.user_id = u.id', (string) $qb); |
123
|
|
|
} |
124
|
|
|
|
125
|
|
|
public function testSelectWithRightJoin() : void |
126
|
|
|
{ |
127
|
|
|
$qb = new QueryBuilder($this->conn); |
128
|
|
|
$expr = $qb->expr(); |
129
|
|
|
|
130
|
|
|
$qb->select('u.*', 'p.*') |
131
|
|
|
->from('users', 'u') |
132
|
|
|
->rightJoin('u', 'phones', 'p', $expr->eq('p.user_id', 'u.id')); |
133
|
|
|
|
134
|
|
|
self::assertEquals('SELECT u.*, p.* FROM users u RIGHT JOIN phones p ON p.user_id = u.id', (string) $qb); |
135
|
|
|
} |
136
|
|
|
|
137
|
|
|
public function testSelectWithAndWhereConditions() : void |
138
|
|
|
{ |
139
|
|
|
$qb = new QueryBuilder($this->conn); |
140
|
|
|
|
141
|
|
|
$qb->select('u.*', 'p.*') |
142
|
|
|
->from('users', 'u') |
143
|
|
|
->where('u.username = ?') |
144
|
|
|
->andWhere('u.name = ?'); |
145
|
|
|
|
146
|
|
|
self::assertEquals('SELECT u.*, p.* FROM users u WHERE (u.username = ?) AND (u.name = ?)', (string) $qb); |
147
|
|
|
} |
148
|
|
|
|
149
|
|
|
public function testSelectWithOrWhereConditions() : void |
150
|
|
|
{ |
151
|
|
|
$qb = new QueryBuilder($this->conn); |
152
|
|
|
|
153
|
|
|
$qb->select('u.*', 'p.*') |
154
|
|
|
->from('users', 'u') |
155
|
|
|
->where('u.username = ?') |
156
|
|
|
->orWhere('u.name = ?'); |
157
|
|
|
|
158
|
|
|
self::assertEquals('SELECT u.*, p.* FROM users u WHERE (u.username = ?) OR (u.name = ?)', (string) $qb); |
159
|
|
|
} |
160
|
|
|
|
161
|
|
|
public function testSelectWithOrOrWhereConditions() : void |
162
|
|
|
{ |
163
|
|
|
$qb = new QueryBuilder($this->conn); |
164
|
|
|
|
165
|
|
|
$qb->select('u.*', 'p.*') |
166
|
|
|
->from('users', 'u') |
167
|
|
|
->orWhere('u.username = ?') |
168
|
|
|
->orWhere('u.name = ?'); |
169
|
|
|
|
170
|
|
|
self::assertEquals('SELECT u.*, p.* FROM users u WHERE (u.username = ?) OR (u.name = ?)', (string) $qb); |
171
|
|
|
} |
172
|
|
|
|
173
|
|
|
public function testSelectWithAndOrWhereConditions() : void |
174
|
|
|
{ |
175
|
|
|
$qb = new QueryBuilder($this->conn); |
176
|
|
|
|
177
|
|
|
$qb->select('u.*', 'p.*') |
178
|
|
|
->from('users', 'u') |
179
|
|
|
->where('u.username = ?') |
180
|
|
|
->andWhere('u.username = ?') |
181
|
|
|
->orWhere('u.name = ?') |
182
|
|
|
->andWhere('u.name = ?'); |
183
|
|
|
|
184
|
|
|
self::assertEquals('SELECT u.*, p.* FROM users u WHERE (((u.username = ?) AND (u.username = ?)) OR (u.name = ?)) AND (u.name = ?)', (string) $qb); |
185
|
|
|
} |
186
|
|
|
|
187
|
|
|
public function testSelectGroupBy() : void |
188
|
|
|
{ |
189
|
|
|
$qb = new QueryBuilder($this->conn); |
190
|
|
|
|
191
|
|
|
$qb->select('u.*', 'p.*') |
192
|
|
|
->from('users', 'u') |
193
|
|
|
->groupBy('u.id'); |
194
|
|
|
|
195
|
|
|
self::assertEquals('SELECT u.*, p.* FROM users u GROUP BY u.id', (string) $qb); |
196
|
|
|
} |
197
|
|
|
|
198
|
|
|
public function testSelectAddGroupBy() : void |
199
|
|
|
{ |
200
|
|
|
$qb = new QueryBuilder($this->conn); |
201
|
|
|
|
202
|
|
|
$qb->select('u.*', 'p.*') |
203
|
|
|
->from('users', 'u') |
204
|
|
|
->groupBy('u.id') |
205
|
|
|
->addGroupBy('u.foo'); |
206
|
|
|
|
207
|
|
|
self::assertEquals('SELECT u.*, p.* FROM users u GROUP BY u.id, u.foo', (string) $qb); |
208
|
|
|
} |
209
|
|
|
|
210
|
|
|
public function testSelectAddGroupBys() : void |
211
|
|
|
{ |
212
|
|
|
$qb = new QueryBuilder($this->conn); |
213
|
|
|
|
214
|
|
|
$qb->select('u.*', 'p.*') |
215
|
|
|
->from('users', 'u') |
216
|
|
|
->groupBy('u.id') |
217
|
|
|
->addGroupBy('u.foo', 'u.bar'); |
218
|
|
|
|
219
|
|
|
self::assertEquals('SELECT u.*, p.* FROM users u GROUP BY u.id, u.foo, u.bar', (string) $qb); |
220
|
|
|
} |
221
|
|
|
|
222
|
|
|
public function testSelectHaving() : void |
223
|
|
|
{ |
224
|
|
|
$qb = new QueryBuilder($this->conn); |
225
|
|
|
|
226
|
|
|
$qb->select('u.*', 'p.*') |
227
|
|
|
->from('users', 'u') |
228
|
|
|
->groupBy('u.id') |
229
|
|
|
->having('u.name = ?'); |
230
|
|
|
|
231
|
|
|
self::assertEquals('SELECT u.*, p.* FROM users u GROUP BY u.id HAVING u.name = ?', (string) $qb); |
232
|
|
|
} |
233
|
|
|
|
234
|
|
|
public function testSelectAndHaving() : void |
235
|
|
|
{ |
236
|
|
|
$qb = new QueryBuilder($this->conn); |
237
|
|
|
|
238
|
|
|
$qb->select('u.*', 'p.*') |
239
|
|
|
->from('users', 'u') |
240
|
|
|
->groupBy('u.id') |
241
|
|
|
->andHaving('u.name = ?'); |
242
|
|
|
|
243
|
|
|
self::assertEquals('SELECT u.*, p.* FROM users u GROUP BY u.id HAVING u.name = ?', (string) $qb); |
244
|
|
|
} |
245
|
|
|
|
246
|
|
|
public function testSelectHavingAndHaving() : void |
247
|
|
|
{ |
248
|
|
|
$qb = new QueryBuilder($this->conn); |
249
|
|
|
|
250
|
|
|
$qb->select('u.*', 'p.*') |
251
|
|
|
->from('users', 'u') |
252
|
|
|
->groupBy('u.id') |
253
|
|
|
->having('u.name = ?') |
254
|
|
|
->andHaving('u.username = ?'); |
255
|
|
|
|
256
|
|
|
self::assertEquals('SELECT u.*, p.* FROM users u GROUP BY u.id HAVING (u.name = ?) AND (u.username = ?)', (string) $qb); |
257
|
|
|
} |
258
|
|
|
|
259
|
|
|
public function testSelectHavingOrHaving() : void |
260
|
|
|
{ |
261
|
|
|
$qb = new QueryBuilder($this->conn); |
262
|
|
|
|
263
|
|
|
$qb->select('u.*', 'p.*') |
264
|
|
|
->from('users', 'u') |
265
|
|
|
->groupBy('u.id') |
266
|
|
|
->having('u.name = ?') |
267
|
|
|
->orHaving('u.username = ?'); |
268
|
|
|
|
269
|
|
|
self::assertEquals('SELECT u.*, p.* FROM users u GROUP BY u.id HAVING (u.name = ?) OR (u.username = ?)', (string) $qb); |
270
|
|
|
} |
271
|
|
|
|
272
|
|
|
public function testSelectOrHavingOrHaving() : void |
273
|
|
|
{ |
274
|
|
|
$qb = new QueryBuilder($this->conn); |
275
|
|
|
|
276
|
|
|
$qb->select('u.*', 'p.*') |
277
|
|
|
->from('users', 'u') |
278
|
|
|
->groupBy('u.id') |
279
|
|
|
->orHaving('u.name = ?') |
280
|
|
|
->orHaving('u.username = ?'); |
281
|
|
|
|
282
|
|
|
self::assertEquals('SELECT u.*, p.* FROM users u GROUP BY u.id HAVING (u.name = ?) OR (u.username = ?)', (string) $qb); |
283
|
|
|
} |
284
|
|
|
|
285
|
|
|
public function testSelectHavingAndOrHaving() : void |
286
|
|
|
{ |
287
|
|
|
$qb = new QueryBuilder($this->conn); |
288
|
|
|
|
289
|
|
|
$qb->select('u.*', 'p.*') |
290
|
|
|
->from('users', 'u') |
291
|
|
|
->groupBy('u.id') |
292
|
|
|
->having('u.name = ?') |
293
|
|
|
->orHaving('u.username = ?') |
294
|
|
|
->andHaving('u.username = ?'); |
295
|
|
|
|
296
|
|
|
self::assertEquals('SELECT u.*, p.* FROM users u GROUP BY u.id HAVING ((u.name = ?) OR (u.username = ?)) AND (u.username = ?)', (string) $qb); |
297
|
|
|
} |
298
|
|
|
|
299
|
|
|
public function testSelectOrderBy() : void |
300
|
|
|
{ |
301
|
|
|
$qb = new QueryBuilder($this->conn); |
302
|
|
|
|
303
|
|
|
$qb->select('u.*', 'p.*') |
304
|
|
|
->from('users', 'u') |
305
|
|
|
->orderBy('u.name'); |
306
|
|
|
|
307
|
|
|
self::assertEquals('SELECT u.*, p.* FROM users u ORDER BY u.name', (string) $qb); |
308
|
|
|
} |
309
|
|
|
|
310
|
|
|
public function testSelectAddOrderBy() : void |
311
|
|
|
{ |
312
|
|
|
$qb = new QueryBuilder($this->conn); |
313
|
|
|
|
314
|
|
|
$qb->select('u.*', 'p.*') |
315
|
|
|
->from('users', 'u') |
316
|
|
|
->orderBy('u.name') |
317
|
|
|
->addOrderBy('u.username', 'DESC'); |
318
|
|
|
|
319
|
|
|
self::assertEquals('SELECT u.*, p.* FROM users u ORDER BY u.name, u.username DESC', (string) $qb); |
320
|
|
|
} |
321
|
|
|
|
322
|
|
|
public function testSelectAddAddOrderBy() : void |
323
|
|
|
{ |
324
|
|
|
$qb = new QueryBuilder($this->conn); |
325
|
|
|
|
326
|
|
|
$qb->select('u.*', 'p.*') |
327
|
|
|
->from('users', 'u') |
328
|
|
|
->addOrderBy('u.name') |
329
|
|
|
->addOrderBy('u.username', 'DESC'); |
330
|
|
|
|
331
|
|
|
self::assertEquals('SELECT u.*, p.* FROM users u ORDER BY u.name, u.username DESC', (string) $qb); |
332
|
|
|
} |
333
|
|
|
|
334
|
|
|
public function testEmptySelect() : void |
335
|
|
|
{ |
336
|
|
|
$qb = new QueryBuilder($this->conn); |
337
|
|
|
$qb2 = $qb->select(); |
338
|
|
|
|
339
|
|
|
self::assertSame($qb, $qb2); |
340
|
|
|
self::assertEquals(QueryBuilder::SELECT, $qb->getType()); |
341
|
|
|
|
342
|
|
|
$this->expectException(QueryException::class); |
343
|
|
|
$qb->getSQL(); |
344
|
|
|
} |
345
|
|
|
|
346
|
|
|
public function testSelectAddSelect() : void |
347
|
|
|
{ |
348
|
|
|
$qb = new QueryBuilder($this->conn); |
349
|
|
|
|
350
|
|
|
$qb->select('u.*') |
351
|
|
|
->addSelect('p.*') |
352
|
|
|
->from('users', 'u'); |
353
|
|
|
|
354
|
|
|
self::assertEquals('SELECT u.*, p.* FROM users u', (string) $qb); |
355
|
|
|
} |
356
|
|
|
|
357
|
|
|
public function testSelectMultipleFrom() : void |
358
|
|
|
{ |
359
|
|
|
$qb = new QueryBuilder($this->conn); |
360
|
|
|
|
361
|
|
|
$qb->select('u.*') |
362
|
|
|
->addSelect('p.*') |
363
|
|
|
->from('users', 'u') |
364
|
|
|
->from('phonenumbers', 'p'); |
365
|
|
|
|
366
|
|
|
self::assertEquals('SELECT u.*, p.* FROM users u, phonenumbers p', (string) $qb); |
367
|
|
|
} |
368
|
|
|
|
369
|
|
|
public function testUpdate() : void |
370
|
|
|
{ |
371
|
|
|
$qb = new QueryBuilder($this->conn); |
372
|
|
|
$qb->update('users') |
373
|
|
|
->set('foo', '?') |
374
|
|
|
->set('bar', '?'); |
375
|
|
|
|
376
|
|
|
self::assertEquals(QueryBuilder::UPDATE, $qb->getType()); |
377
|
|
|
self::assertEquals('UPDATE users SET foo = ?, bar = ?', (string) $qb); |
378
|
|
|
} |
379
|
|
|
|
380
|
|
|
public function testUpdateWhere() : void |
381
|
|
|
{ |
382
|
|
|
$qb = new QueryBuilder($this->conn); |
383
|
|
|
$qb->update('users') |
384
|
|
|
->set('foo', '?') |
385
|
|
|
->where('foo = ?'); |
386
|
|
|
|
387
|
|
|
self::assertEquals('UPDATE users SET foo = ? WHERE foo = ?', (string) $qb); |
388
|
|
|
} |
389
|
|
|
|
390
|
|
|
public function testDelete() : void |
391
|
|
|
{ |
392
|
|
|
$qb = new QueryBuilder($this->conn); |
393
|
|
|
$qb->delete('users'); |
394
|
|
|
|
395
|
|
|
self::assertEquals(QueryBuilder::DELETE, $qb->getType()); |
396
|
|
|
self::assertEquals('DELETE FROM users', (string) $qb); |
397
|
|
|
} |
398
|
|
|
|
399
|
|
|
public function testDeleteWhere() : void |
400
|
|
|
{ |
401
|
|
|
$qb = new QueryBuilder($this->conn); |
402
|
|
|
$qb->delete('users') |
403
|
|
|
->where('u.foo = ?'); |
404
|
|
|
|
405
|
|
|
self::assertEquals('DELETE FROM users WHERE u.foo = ?', (string) $qb); |
406
|
|
|
} |
407
|
|
|
|
408
|
|
|
public function testInsertValues() : void |
409
|
|
|
{ |
410
|
|
|
$qb = new QueryBuilder($this->conn); |
411
|
|
|
$qb->insert('users') |
412
|
|
|
->values( |
413
|
|
|
[ |
414
|
|
|
'foo' => '?', |
415
|
|
|
'bar' => '?', |
416
|
|
|
] |
417
|
|
|
); |
418
|
|
|
|
419
|
|
|
self::assertEquals(QueryBuilder::INSERT, $qb->getType()); |
420
|
|
|
self::assertEquals('INSERT INTO users (foo, bar) VALUES(?, ?)', (string) $qb); |
421
|
|
|
} |
422
|
|
|
|
423
|
|
|
public function testInsertReplaceValues() : void |
424
|
|
|
{ |
425
|
|
|
$qb = new QueryBuilder($this->conn); |
426
|
|
|
$qb->insert('users') |
427
|
|
|
->values( |
428
|
|
|
[ |
429
|
|
|
'foo' => '?', |
430
|
|
|
'bar' => '?', |
431
|
|
|
] |
432
|
|
|
) |
433
|
|
|
->values( |
434
|
|
|
[ |
435
|
|
|
'bar' => '?', |
436
|
|
|
'foo' => '?', |
437
|
|
|
] |
438
|
|
|
); |
439
|
|
|
|
440
|
|
|
self::assertEquals(QueryBuilder::INSERT, $qb->getType()); |
441
|
|
|
self::assertEquals('INSERT INTO users (bar, foo) VALUES(?, ?)', (string) $qb); |
442
|
|
|
} |
443
|
|
|
|
444
|
|
|
public function testInsertSetValue() : void |
445
|
|
|
{ |
446
|
|
|
$qb = new QueryBuilder($this->conn); |
447
|
|
|
$qb->insert('users') |
448
|
|
|
->setValue('foo', 'bar') |
449
|
|
|
->setValue('bar', '?') |
450
|
|
|
->setValue('foo', '?'); |
451
|
|
|
|
452
|
|
|
self::assertEquals(QueryBuilder::INSERT, $qb->getType()); |
453
|
|
|
self::assertEquals('INSERT INTO users (foo, bar) VALUES(?, ?)', (string) $qb); |
454
|
|
|
} |
455
|
|
|
|
456
|
|
|
public function testInsertValuesSetValue() : void |
457
|
|
|
{ |
458
|
|
|
$qb = new QueryBuilder($this->conn); |
459
|
|
|
$qb->insert('users') |
460
|
|
|
->values( |
461
|
|
|
['foo' => '?'] |
462
|
|
|
) |
463
|
|
|
->setValue('bar', '?'); |
464
|
|
|
|
465
|
|
|
self::assertEquals(QueryBuilder::INSERT, $qb->getType()); |
466
|
|
|
self::assertEquals('INSERT INTO users (foo, bar) VALUES(?, ?)', (string) $qb); |
467
|
|
|
} |
468
|
|
|
|
469
|
|
|
public function testGetConnection() : void |
470
|
|
|
{ |
471
|
|
|
$qb = new QueryBuilder($this->conn); |
472
|
|
|
self::assertSame($this->conn, $qb->getConnection()); |
473
|
|
|
} |
474
|
|
|
|
475
|
|
|
public function testGetState() : void |
476
|
|
|
{ |
477
|
|
|
$qb = new QueryBuilder($this->conn); |
478
|
|
|
|
479
|
|
|
self::assertEquals(QueryBuilder::STATE_CLEAN, $qb->getState()); |
480
|
|
|
|
481
|
|
|
$qb->select('u.*')->from('users', 'u'); |
482
|
|
|
|
483
|
|
|
self::assertEquals(QueryBuilder::STATE_DIRTY, $qb->getState()); |
484
|
|
|
|
485
|
|
|
$sql1 = $qb->getSQL(); |
486
|
|
|
|
487
|
|
|
self::assertEquals(QueryBuilder::STATE_CLEAN, $qb->getState()); |
488
|
|
|
self::assertEquals($sql1, $qb->getSQL()); |
489
|
|
|
} |
490
|
|
|
|
491
|
|
|
/** |
492
|
|
|
* @dataProvider maxResultsProvider |
493
|
|
|
*/ |
494
|
|
|
public function testSetMaxResults(?int $maxResults) : void |
495
|
|
|
{ |
496
|
|
|
$qb = new QueryBuilder($this->conn); |
497
|
|
|
$qb->setMaxResults($maxResults); |
498
|
|
|
|
499
|
|
|
self::assertEquals(QueryBuilder::STATE_DIRTY, $qb->getState()); |
500
|
|
|
self::assertEquals($maxResults, $qb->getMaxResults()); |
501
|
|
|
} |
502
|
|
|
|
503
|
|
|
/** |
504
|
|
|
* @return mixed[][] |
505
|
|
|
*/ |
506
|
|
|
public static function maxResultsProvider() : iterable |
507
|
|
|
{ |
508
|
|
|
return [ |
509
|
|
|
'non-null' => [10], |
510
|
|
|
'null' => [null], |
511
|
|
|
]; |
512
|
|
|
} |
513
|
|
|
|
514
|
|
|
public function testSetFirstResult() : void |
515
|
|
|
{ |
516
|
|
|
$qb = new QueryBuilder($this->conn); |
517
|
|
|
$qb->setFirstResult(10); |
518
|
|
|
|
519
|
|
|
self::assertEquals(QueryBuilder::STATE_DIRTY, $qb->getState()); |
520
|
|
|
self::assertEquals(10, $qb->getFirstResult()); |
521
|
|
|
} |
522
|
|
|
|
523
|
|
|
public function testCreateNamedParameter() : void |
524
|
|
|
{ |
525
|
|
|
$qb = new QueryBuilder($this->conn); |
526
|
|
|
|
527
|
|
|
$qb->select('u.*')->from('users', 'u')->where( |
528
|
|
|
$qb->expr()->eq('u.name', $qb->createNamedParameter(10, ParameterType::INTEGER)) |
529
|
|
|
); |
530
|
|
|
|
531
|
|
|
self::assertEquals('SELECT u.* FROM users u WHERE u.name = :dcValue1', (string) $qb); |
532
|
|
|
self::assertEquals(10, $qb->getParameter('dcValue1')); |
533
|
|
|
self::assertEquals(ParameterType::INTEGER, $qb->getParameterType('dcValue1')); |
534
|
|
|
} |
535
|
|
|
|
536
|
|
|
public function testCreateNamedParameterCustomPlaceholder() : void |
537
|
|
|
{ |
538
|
|
|
$qb = new QueryBuilder($this->conn); |
539
|
|
|
|
540
|
|
|
$qb->select('u.*')->from('users', 'u')->where( |
541
|
|
|
$qb->expr()->eq('u.name', $qb->createNamedParameter(10, ParameterType::INTEGER, ':test')) |
542
|
|
|
); |
543
|
|
|
|
544
|
|
|
self::assertEquals('SELECT u.* FROM users u WHERE u.name = :test', (string) $qb); |
545
|
|
|
self::assertEquals(10, $qb->getParameter('test')); |
546
|
|
|
self::assertEquals(ParameterType::INTEGER, $qb->getParameterType('test')); |
547
|
|
|
} |
548
|
|
|
|
549
|
|
|
public function testCreatePositionalParameter() : void |
550
|
|
|
{ |
551
|
|
|
$qb = new QueryBuilder($this->conn); |
552
|
|
|
|
553
|
|
|
$qb->select('u.*')->from('users', 'u')->where( |
554
|
|
|
$qb->expr()->eq('u.name', $qb->createPositionalParameter(10, ParameterType::INTEGER)) |
555
|
|
|
); |
556
|
|
|
|
557
|
|
|
self::assertEquals('SELECT u.* FROM users u WHERE u.name = ?', (string) $qb); |
558
|
|
|
self::assertEquals(10, $qb->getParameter(1)); |
559
|
|
|
self::assertEquals(ParameterType::INTEGER, $qb->getParameterType(1)); |
560
|
|
|
} |
561
|
|
|
|
562
|
|
|
/** |
563
|
|
|
* @group DBAL-172 |
564
|
|
|
*/ |
565
|
|
|
public function testReferenceJoinFromJoin() : void |
566
|
|
|
{ |
567
|
|
|
$qb = new QueryBuilder($this->conn); |
568
|
|
|
|
569
|
|
|
$qb->select('COUNT(DISTINCT news.id)') |
570
|
|
|
->from('cb_newspages', 'news') |
571
|
|
|
->innerJoin('news', 'nodeversion', 'nv', 'nv.refId = news.id AND nv.refEntityname=\'News\'') |
572
|
|
|
->innerJoin('invalid', 'nodetranslation', 'nt', 'nv.nodetranslation = nt.id') |
573
|
|
|
->innerJoin('nt', 'node', 'n', 'nt.node = n.id') |
574
|
|
|
->where('nt.lang = :lang AND n.deleted != 1'); |
575
|
|
|
|
576
|
|
|
$this->expectException(QueryException::class); |
577
|
|
|
$this->expectExceptionMessage('The given alias "invalid" is not part of any FROM or JOIN clause table. The currently registered aliases are: news, nv.'); |
578
|
|
|
self::assertEquals('', $qb->getSQL()); |
579
|
|
|
} |
580
|
|
|
|
581
|
|
|
/** |
582
|
|
|
* @group DBAL-172 |
583
|
|
|
*/ |
584
|
|
|
public function testSelectFromMasterWithWhereOnJoinedTables() : void |
585
|
|
|
{ |
586
|
|
|
$qb = new QueryBuilder($this->conn); |
587
|
|
|
|
588
|
|
|
$qb->select('COUNT(DISTINCT news.id)') |
589
|
|
|
->from('newspages', 'news') |
590
|
|
|
->innerJoin('news', 'nodeversion', 'nv', "nv.refId = news.id AND nv.refEntityname='Entity\\News'") |
591
|
|
|
->innerJoin('nv', 'nodetranslation', 'nt', 'nv.nodetranslation = nt.id') |
592
|
|
|
->innerJoin('nt', 'node', 'n', 'nt.node = n.id') |
593
|
|
|
->where('nt.lang = ?') |
594
|
|
|
->andWhere('n.deleted = 0'); |
595
|
|
|
|
596
|
|
|
self::assertEquals("SELECT COUNT(DISTINCT news.id) FROM newspages news INNER JOIN nodeversion nv ON nv.refId = news.id AND nv.refEntityname='Entity\\News' INNER JOIN nodetranslation nt ON nv.nodetranslation = nt.id INNER JOIN node n ON nt.node = n.id WHERE (nt.lang = ?) AND (n.deleted = 0)", $qb->getSQL()); |
597
|
|
|
} |
598
|
|
|
|
599
|
|
|
/** |
600
|
|
|
* @group DBAL-442 |
601
|
|
|
*/ |
602
|
|
|
public function testSelectWithMultipleFromAndJoins() : void |
603
|
|
|
{ |
604
|
|
|
$qb = new QueryBuilder($this->conn); |
605
|
|
|
|
606
|
|
|
$qb->select('DISTINCT u.id') |
607
|
|
|
->from('users', 'u') |
608
|
|
|
->from('articles', 'a') |
609
|
|
|
->innerJoin('u', 'permissions', 'p', 'p.user_id = u.id') |
610
|
|
|
->innerJoin('a', 'comments', 'c', 'c.article_id = a.id') |
611
|
|
|
->where('u.id = a.user_id') |
612
|
|
|
->andWhere('p.read = 1'); |
613
|
|
|
|
614
|
|
|
self::assertEquals('SELECT DISTINCT u.id FROM users u INNER JOIN permissions p ON p.user_id = u.id, articles a INNER JOIN comments c ON c.article_id = a.id WHERE (u.id = a.user_id) AND (p.read = 1)', $qb->getSQL()); |
615
|
|
|
} |
616
|
|
|
|
617
|
|
|
/** |
618
|
|
|
* @group DBAL-774 |
619
|
|
|
*/ |
620
|
|
|
public function testSelectWithJoinsWithMultipleOnConditionsParseOrder() : void |
621
|
|
|
{ |
622
|
|
|
$qb = new QueryBuilder($this->conn); |
623
|
|
|
|
624
|
|
|
$qb->select('a.id') |
625
|
|
|
->from('table_a', 'a') |
626
|
|
|
->join('a', 'table_b', 'b', 'a.fk_b = b.id') |
627
|
|
|
->join('b', 'table_c', 'c', 'c.fk_b = b.id AND b.language = ?') |
628
|
|
|
->join('a', 'table_d', 'd', 'a.fk_d = d.id') |
629
|
|
|
->join('c', 'table_e', 'e', 'e.fk_c = c.id AND e.fk_d = d.id'); |
630
|
|
|
|
631
|
|
|
self::assertEquals( |
632
|
|
|
'SELECT a.id ' . |
633
|
|
|
'FROM table_a a ' . |
634
|
|
|
'INNER JOIN table_b b ON a.fk_b = b.id ' . |
635
|
|
|
'INNER JOIN table_d d ON a.fk_d = d.id ' . |
636
|
|
|
'INNER JOIN table_c c ON c.fk_b = b.id AND b.language = ? ' . |
637
|
|
|
'INNER JOIN table_e e ON e.fk_c = c.id AND e.fk_d = d.id', |
638
|
|
|
(string) $qb |
639
|
|
|
); |
640
|
|
|
} |
641
|
|
|
|
642
|
|
|
/** |
643
|
|
|
* @group DBAL-774 |
644
|
|
|
*/ |
645
|
|
|
public function testSelectWithMultipleFromsAndJoinsWithMultipleOnConditionsParseOrder() : void |
646
|
|
|
{ |
647
|
|
|
$qb = new QueryBuilder($this->conn); |
648
|
|
|
|
649
|
|
|
$qb->select('a.id') |
650
|
|
|
->from('table_a', 'a') |
651
|
|
|
->from('table_f', 'f') |
652
|
|
|
->join('a', 'table_b', 'b', 'a.fk_b = b.id') |
653
|
|
|
->join('b', 'table_c', 'c', 'c.fk_b = b.id AND b.language = ?') |
654
|
|
|
->join('a', 'table_d', 'd', 'a.fk_d = d.id') |
655
|
|
|
->join('c', 'table_e', 'e', 'e.fk_c = c.id AND e.fk_d = d.id') |
656
|
|
|
->join('f', 'table_g', 'g', 'f.fk_g = g.id'); |
657
|
|
|
|
658
|
|
|
self::assertEquals( |
659
|
|
|
'SELECT a.id ' . |
660
|
|
|
'FROM table_a a ' . |
661
|
|
|
'INNER JOIN table_b b ON a.fk_b = b.id ' . |
662
|
|
|
'INNER JOIN table_d d ON a.fk_d = d.id ' . |
663
|
|
|
'INNER JOIN table_c c ON c.fk_b = b.id AND b.language = ? ' . |
664
|
|
|
'INNER JOIN table_e e ON e.fk_c = c.id AND e.fk_d = d.id, ' . |
665
|
|
|
'table_f f ' . |
666
|
|
|
'INNER JOIN table_g g ON f.fk_g = g.id', |
667
|
|
|
(string) $qb |
668
|
|
|
); |
669
|
|
|
} |
670
|
|
|
|
671
|
|
|
public function testClone() : void |
672
|
|
|
{ |
673
|
|
|
$qb = new QueryBuilder($this->conn); |
674
|
|
|
|
675
|
|
|
$qb->select('u.id') |
676
|
|
|
->from('users', 'u') |
677
|
|
|
->where('u.id = :test'); |
678
|
|
|
|
679
|
|
|
$qb->setParameter(':test', (object) 1); |
680
|
|
|
|
681
|
|
|
$clone = clone $qb; |
682
|
|
|
|
683
|
|
|
self::assertEquals((string) $qb, (string) $clone); |
684
|
|
|
|
685
|
|
|
$qb->andWhere('u.id = 1'); |
686
|
|
|
|
687
|
|
|
self::assertNotSame($qb->getParameters(), $clone->getParameters()); |
688
|
|
|
} |
689
|
|
|
|
690
|
|
|
public function testSimpleSelectWithoutTableAlias() : void |
691
|
|
|
{ |
692
|
|
|
$qb = new QueryBuilder($this->conn); |
693
|
|
|
|
694
|
|
|
$qb->select('id') |
695
|
|
|
->from('users'); |
696
|
|
|
|
697
|
|
|
self::assertEquals('SELECT id FROM users', (string) $qb); |
698
|
|
|
} |
699
|
|
|
|
700
|
|
|
public function testSimpleSelectWithMatchingTableAlias() : void |
701
|
|
|
{ |
702
|
|
|
$qb = new QueryBuilder($this->conn); |
703
|
|
|
|
704
|
|
|
$qb->select('id') |
705
|
|
|
->from('users', 'users'); |
706
|
|
|
|
707
|
|
|
self::assertEquals('SELECT id FROM users', (string) $qb); |
708
|
|
|
} |
709
|
|
|
|
710
|
|
|
public function testSelectWithSimpleWhereWithoutTableAlias() : void |
711
|
|
|
{ |
712
|
|
|
$qb = new QueryBuilder($this->conn); |
713
|
|
|
|
714
|
|
|
$qb->select('id', 'name') |
715
|
|
|
->from('users') |
716
|
|
|
->where('awesome=9001'); |
717
|
|
|
|
718
|
|
|
self::assertEquals('SELECT id, name FROM users WHERE awesome=9001', (string) $qb); |
719
|
|
|
} |
720
|
|
|
|
721
|
|
|
public function testComplexSelectWithoutTableAliases() : void |
722
|
|
|
{ |
723
|
|
|
$qb = new QueryBuilder($this->conn); |
724
|
|
|
|
725
|
|
|
$qb->select('DISTINCT users.id') |
726
|
|
|
->from('users') |
727
|
|
|
->from('articles') |
728
|
|
|
->innerJoin('users', 'permissions', 'p', 'p.user_id = users.id') |
729
|
|
|
->innerJoin('articles', 'comments', 'c', 'c.article_id = articles.id') |
730
|
|
|
->where('users.id = articles.user_id') |
731
|
|
|
->andWhere('p.read = 1'); |
732
|
|
|
|
733
|
|
|
self::assertEquals('SELECT DISTINCT users.id FROM users INNER JOIN permissions p ON p.user_id = users.id, articles INNER JOIN comments c ON c.article_id = articles.id WHERE (users.id = articles.user_id) AND (p.read = 1)', $qb->getSQL()); |
734
|
|
|
} |
735
|
|
|
|
736
|
|
|
public function testComplexSelectWithSomeTableAliases() : void |
737
|
|
|
{ |
738
|
|
|
$qb = new QueryBuilder($this->conn); |
739
|
|
|
|
740
|
|
|
$qb->select('u.id') |
741
|
|
|
->from('users', 'u') |
742
|
|
|
->from('articles') |
743
|
|
|
->innerJoin('u', 'permissions', 'p', 'p.user_id = u.id') |
744
|
|
|
->innerJoin('articles', 'comments', 'c', 'c.article_id = articles.id'); |
745
|
|
|
|
746
|
|
|
self::assertEquals('SELECT u.id FROM users u INNER JOIN permissions p ON p.user_id = u.id, articles INNER JOIN comments c ON c.article_id = articles.id', $qb->getSQL()); |
747
|
|
|
} |
748
|
|
|
|
749
|
|
|
public function testSelectAllFromTableWithoutTableAlias() : void |
750
|
|
|
{ |
751
|
|
|
$qb = new QueryBuilder($this->conn); |
752
|
|
|
|
753
|
|
|
$qb->select('users.*') |
754
|
|
|
->from('users'); |
755
|
|
|
|
756
|
|
|
self::assertEquals('SELECT users.* FROM users', (string) $qb); |
757
|
|
|
} |
758
|
|
|
|
759
|
|
|
public function testSelectAllWithoutTableAlias() : void |
760
|
|
|
{ |
761
|
|
|
$qb = new QueryBuilder($this->conn); |
762
|
|
|
|
763
|
|
|
$qb->select('*') |
764
|
|
|
->from('users'); |
765
|
|
|
|
766
|
|
|
self::assertEquals('SELECT * FROM users', (string) $qb); |
767
|
|
|
} |
768
|
|
|
|
769
|
|
|
/** |
770
|
|
|
* @group DBAL-959 |
771
|
|
|
*/ |
772
|
|
|
public function testGetParameterType() : void |
773
|
|
|
{ |
774
|
|
|
$qb = new QueryBuilder($this->conn); |
775
|
|
|
|
776
|
|
|
$qb->select('*')->from('users'); |
777
|
|
|
|
778
|
|
|
self::assertNull($qb->getParameterType('name')); |
779
|
|
|
|
780
|
|
|
$qb->where('name = :name'); |
781
|
|
|
$qb->setParameter('name', 'foo'); |
782
|
|
|
|
783
|
|
|
self::assertNull($qb->getParameterType('name')); |
784
|
|
|
|
785
|
|
|
$qb->setParameter('name', 'foo', ParameterType::STRING); |
786
|
|
|
|
787
|
|
|
self::assertSame(ParameterType::STRING, $qb->getParameterType('name')); |
788
|
|
|
} |
789
|
|
|
|
790
|
|
|
/** |
791
|
|
|
* @group DBAL-959 |
792
|
|
|
*/ |
793
|
|
|
public function testGetParameterTypes() : void |
794
|
|
|
{ |
795
|
|
|
$qb = new QueryBuilder($this->conn); |
796
|
|
|
|
797
|
|
|
$qb->select('*')->from('users'); |
798
|
|
|
|
799
|
|
|
self::assertSame([], $qb->getParameterTypes()); |
800
|
|
|
|
801
|
|
|
$qb->where('name = :name'); |
802
|
|
|
$qb->setParameter('name', 'foo'); |
803
|
|
|
|
804
|
|
|
self::assertSame([], $qb->getParameterTypes()); |
805
|
|
|
|
806
|
|
|
$qb->setParameter('name', 'foo', ParameterType::STRING); |
807
|
|
|
|
808
|
|
|
$qb->where('is_active = :isActive'); |
809
|
|
|
$qb->setParameter('isActive', true, ParameterType::BOOLEAN); |
810
|
|
|
|
811
|
|
|
self::assertSame([ |
812
|
|
|
'name' => ParameterType::STRING, |
813
|
|
|
'isActive' => ParameterType::BOOLEAN, |
814
|
|
|
], $qb->getParameterTypes()); |
815
|
|
|
} |
816
|
|
|
|
817
|
|
|
/** |
818
|
|
|
* @group DBAL-1137 |
819
|
|
|
*/ |
820
|
|
|
public function testJoinWithNonUniqueAliasThrowsException() : void |
821
|
|
|
{ |
822
|
|
|
$qb = new QueryBuilder($this->conn); |
823
|
|
|
|
824
|
|
|
$qb->select('a.id') |
825
|
|
|
->from('table_a', 'a') |
826
|
|
|
->join('a', 'table_b', 'a', 'a.fk_b = a.id'); |
827
|
|
|
|
828
|
|
|
$this->expectException(QueryException::class); |
829
|
|
|
$this->expectExceptionMessage('The given alias "a" is not unique in FROM and JOIN clause table. The currently registered aliases are: a.'); |
830
|
|
|
|
831
|
|
|
$qb->getSQL(); |
832
|
|
|
} |
833
|
|
|
} |
834
|
|
|
|