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