3 namespace Doctrine\Tests\DBAL\Query;
5 use Doctrine\DBAL\Query\Expression\ExpressionBuilder,
6 Doctrine\DBAL\Query\QueryBuilder;
8 require_once __DIR__ . '/../../TestInit.php';
13 class QueryBuilderTest extends \Doctrine\Tests\DbalTestCase
17 public function setUp()
19 $this->conn = $this->getMock('Doctrine\DBAL\Connection', array(), array(), '', false);
21 $expressionBuilder = new ExpressionBuilder($this->conn);
23 $this->conn->expects($this->any())
24 ->method('getExpressionBuilder')
25 ->will($this->returnValue($expressionBuilder));
28 public function testSimpleSelect()
30 $qb = new QueryBuilder($this->conn);
35 $this->assertEquals('SELECT u.id FROM users u', (string) $qb);
38 public function testSelectWithSimpleWhere()
40 $qb = new QueryBuilder($this->conn);
45 ->where($expr->andX($expr->eq('u.nickname', '?')));
47 $this->assertEquals("SELECT u.id FROM users u WHERE u.nickname = ?", (string) $qb);
50 public function testSelectWithLeftJoin()
52 $qb = new QueryBuilder($this->conn);
55 $qb->select('u.*', 'p.*')
57 ->leftJoin('u', 'phones', 'p', $expr->eq('p.user_id', 'u.id'));
59 $this->assertEquals('SELECT u.*, p.* FROM users u LEFT JOIN phones p ON p.user_id = u.id', (string) $qb);
62 public function testSelectWithJoin()
64 $qb = new QueryBuilder($this->conn);
67 $qb->select('u.*', 'p.*')
69 ->Join('u', 'phones', 'p', $expr->eq('p.user_id', 'u.id'));
71 $this->assertEquals('SELECT u.*, p.* FROM users u INNER JOIN phones p ON p.user_id = u.id', (string) $qb);
74 public function testSelectWithInnerJoin()
76 $qb = new QueryBuilder($this->conn);
79 $qb->select('u.*', 'p.*')
81 ->innerJoin('u', 'phones', 'p', $expr->eq('p.user_id', 'u.id'));
83 $this->assertEquals('SELECT u.*, p.* FROM users u INNER JOIN phones p ON p.user_id = u.id', (string) $qb);
86 public function testSelectWithRightJoin()
88 $qb = new QueryBuilder($this->conn);
91 $qb->select('u.*', 'p.*')
93 ->rightJoin('u', 'phones', 'p', $expr->eq('p.user_id', 'u.id'));
95 $this->assertEquals('SELECT u.*, p.* FROM users u RIGHT JOIN phones p ON p.user_id = u.id', (string) $qb);
98 public function testSelectWithAndWhereConditions()
100 $qb = new QueryBuilder($this->conn);
103 $qb->select('u.*', 'p.*')
105 ->where('u.username = ?')
106 ->andWhere('u.name = ?');
108 $this->assertEquals('SELECT u.*, p.* FROM users u WHERE (u.username = ?) AND (u.name = ?)', (string) $qb);
111 public function testSelectWithOrWhereConditions()
113 $qb = new QueryBuilder($this->conn);
116 $qb->select('u.*', 'p.*')
118 ->where('u.username = ?')
119 ->orWhere('u.name = ?');
121 $this->assertEquals('SELECT u.*, p.* FROM users u WHERE (u.username = ?) OR (u.name = ?)', (string) $qb);
124 public function testSelectWithOrOrWhereConditions()
126 $qb = new QueryBuilder($this->conn);
129 $qb->select('u.*', 'p.*')
131 ->orWhere('u.username = ?')
132 ->orWhere('u.name = ?');
134 $this->assertEquals('SELECT u.*, p.* FROM users u WHERE (u.username = ?) OR (u.name = ?)', (string) $qb);
137 public function testSelectWithAndOrWhereConditions()
139 $qb = new QueryBuilder($this->conn);
142 $qb->select('u.*', 'p.*')
144 ->where('u.username = ?')
145 ->andWhere('u.username = ?')
146 ->orWhere('u.name = ?')
147 ->andWhere('u.name = ?');
149 $this->assertEquals('SELECT u.*, p.* FROM users u WHERE (((u.username = ?) AND (u.username = ?)) OR (u.name = ?)) AND (u.name = ?)', (string) $qb);
152 public function testSelectGroupBy()
154 $qb = new QueryBuilder($this->conn);
157 $qb->select('u.*', 'p.*')
161 $this->assertEquals('SELECT u.*, p.* FROM users u GROUP BY u.id', (string) $qb);
164 public function testSelectEmptyGroupBy()
166 $qb = new QueryBuilder($this->conn);
169 $qb->select('u.*', 'p.*')
171 ->from('users', 'u');
173 $this->assertEquals('SELECT u.*, p.* FROM users u', (string) $qb);
176 public function testSelectEmptyAddGroupBy()
178 $qb = new QueryBuilder($this->conn);
181 $qb->select('u.*', 'p.*')
182 ->addGroupBy(array())
183 ->from('users', 'u');
185 $this->assertEquals('SELECT u.*, p.* FROM users u', (string) $qb);
188 public function testSelectAddGroupBy()
190 $qb = new QueryBuilder($this->conn);
193 $qb->select('u.*', 'p.*')
196 ->addGroupBy('u.foo');
198 $this->assertEquals('SELECT u.*, p.* FROM users u GROUP BY u.id, u.foo', (string) $qb);
201 public function testSelectAddGroupBys()
203 $qb = new QueryBuilder($this->conn);
206 $qb->select('u.*', 'p.*')
209 ->addGroupBy('u.foo', 'u.bar');
211 $this->assertEquals('SELECT u.*, p.* FROM users u GROUP BY u.id, u.foo, u.bar', (string) $qb);
214 public function testSelectHaving()
216 $qb = new QueryBuilder($this->conn);
219 $qb->select('u.*', 'p.*')
222 ->having('u.name = ?');
224 $this->assertEquals('SELECT u.*, p.* FROM users u GROUP BY u.id HAVING u.name = ?', (string) $qb);
227 public function testSelectAndHaving()
229 $qb = new QueryBuilder($this->conn);
232 $qb->select('u.*', 'p.*')
235 ->andHaving('u.name = ?');
237 $this->assertEquals('SELECT u.*, p.* FROM users u GROUP BY u.id HAVING u.name = ?', (string) $qb);
240 public function testSelectHavingAndHaving()
242 $qb = new QueryBuilder($this->conn);
245 $qb->select('u.*', 'p.*')
248 ->having('u.name = ?')
249 ->andHaving('u.username = ?');
251 $this->assertEquals('SELECT u.*, p.* FROM users u GROUP BY u.id HAVING (u.name = ?) AND (u.username = ?)', (string) $qb);
254 public function testSelectHavingOrHaving()
256 $qb = new QueryBuilder($this->conn);
259 $qb->select('u.*', 'p.*')
262 ->having('u.name = ?')
263 ->orHaving('u.username = ?');
265 $this->assertEquals('SELECT u.*, p.* FROM users u GROUP BY u.id HAVING (u.name = ?) OR (u.username = ?)', (string) $qb);
268 public function testSelectOrHavingOrHaving()
270 $qb = new QueryBuilder($this->conn);
273 $qb->select('u.*', 'p.*')
276 ->orHaving('u.name = ?')
277 ->orHaving('u.username = ?');
279 $this->assertEquals('SELECT u.*, p.* FROM users u GROUP BY u.id HAVING (u.name = ?) OR (u.username = ?)', (string) $qb);
282 public function testSelectHavingAndOrHaving()
284 $qb = new QueryBuilder($this->conn);
287 $qb->select('u.*', 'p.*')
290 ->having('u.name = ?')
291 ->orHaving('u.username = ?')
292 ->andHaving('u.username = ?');
294 $this->assertEquals('SELECT u.*, p.* FROM users u GROUP BY u.id HAVING ((u.name = ?) OR (u.username = ?)) AND (u.username = ?)', (string) $qb);
297 public function testSelectOrderBy()
299 $qb = new QueryBuilder($this->conn);
302 $qb->select('u.*', 'p.*')
306 $this->assertEquals('SELECT u.*, p.* FROM users u ORDER BY u.name ASC', (string) $qb);
309 public function testSelectAddOrderBy()
311 $qb = new QueryBuilder($this->conn);
314 $qb->select('u.*', 'p.*')
317 ->addOrderBy('u.username', 'DESC');
319 $this->assertEquals('SELECT u.*, p.* FROM users u ORDER BY u.name ASC, u.username DESC', (string) $qb);
322 public function testSelectAddAddOrderBy()
324 $qb = new QueryBuilder($this->conn);
327 $qb->select('u.*', 'p.*')
329 ->addOrderBy('u.name')
330 ->addOrderBy('u.username', 'DESC');
332 $this->assertEquals('SELECT u.*, p.* FROM users u ORDER BY u.name ASC, u.username DESC', (string) $qb);
335 public function testEmptySelect()
337 $qb = new QueryBuilder($this->conn);
338 $qb2 = $qb->select();
340 $this->assertSame($qb, $qb2);
341 $this->assertEquals(QueryBuilder::SELECT, $qb->getType());
344 public function testSelectAddSelect()
346 $qb = new QueryBuilder($this->conn);
351 ->from('users', 'u');
353 $this->assertEquals('SELECT u.*, p.* FROM users u', (string) $qb);
356 public function testEmptyAddSelect()
358 $qb = new QueryBuilder($this->conn);
359 $qb2 = $qb->addSelect();
361 $this->assertSame($qb, $qb2);
362 $this->assertEquals(QueryBuilder::SELECT, $qb->getType());
365 public function testSelectMultipleFrom()
367 $qb = new QueryBuilder($this->conn);
373 ->from('phonenumbers', 'p');
375 $this->assertEquals('SELECT u.*, p.* FROM users u, phonenumbers p', (string) $qb);
378 public function testUpdate()
380 $qb = new QueryBuilder($this->conn);
381 $qb->update('users', 'u')
385 $this->assertEquals(QueryBuilder::UPDATE, $qb->getType());
386 $this->assertEquals('UPDATE users u SET u.foo = ?, u.bar = ?', (string) $qb);
389 public function testUpdateWithoutAlias()
391 $qb = new QueryBuilder($this->conn);
396 $this->assertEquals('UPDATE users SET foo = ?, bar = ?', (string) $qb);
399 public function testUpdateWhere()
401 $qb = new QueryBuilder($this->conn);
402 $qb->update('users', 'u')
404 ->where('u.foo = ?');
406 $this->assertEquals('UPDATE users u SET u.foo = ? WHERE u.foo = ?', (string) $qb);
409 public function testEmptyUpdate()
411 $qb = new QueryBuilder($this->conn);
412 $qb2 = $qb->update();
414 $this->assertEquals(QueryBuilder::UPDATE, $qb->getType());
415 $this->assertSame($qb2, $qb);
418 public function testDelete()
420 $qb = new QueryBuilder($this->conn);
421 $qb->delete('users', 'u');
423 $this->assertEquals(QueryBuilder::DELETE, $qb->getType());
424 $this->assertEquals('DELETE FROM users u', (string) $qb);
427 public function testDeleteWithoutAlias()
429 $qb = new QueryBuilder($this->conn);
430 $qb->delete('users');
432 $this->assertEquals(QueryBuilder::DELETE, $qb->getType());
433 $this->assertEquals('DELETE FROM users', (string) $qb);
436 public function testDeleteWhere()
438 $qb = new QueryBuilder($this->conn);
439 $qb->delete('users', 'u')
440 ->where('u.foo = ?');
442 $this->assertEquals('DELETE FROM users u WHERE u.foo = ?', (string) $qb);
445 public function testEmptyDelete()
447 $qb = new QueryBuilder($this->conn);
448 $qb2 = $qb->delete();
450 $this->assertEquals(QueryBuilder::DELETE, $qb->getType());
451 $this->assertSame($qb2, $qb);
454 public function testGetConnection()
456 $qb = new QueryBuilder($this->conn);
457 $this->assertSame($this->conn, $qb->getConnection());
460 public function testGetState()
462 $qb = new QueryBuilder($this->conn);
464 $this->assertEquals(QueryBuilder::STATE_CLEAN, $qb->getState());
466 $qb->select('u.*')->from('users', 'u');
468 $this->assertEquals(QueryBuilder::STATE_DIRTY, $qb->getState());
470 $sql1 = $qb->getSQL();
472 $this->assertEquals(QueryBuilder::STATE_CLEAN, $qb->getState());
473 $this->assertEquals($sql1, $qb->getSQL());
476 public function testSetMaxResults()
478 $qb = new QueryBuilder($this->conn);
479 $qb->setMaxResults(10);
481 $this->assertEquals(QueryBuilder::STATE_DIRTY, $qb->getState());
482 $this->assertEQuals(10, $qb->getMaxResults());
485 public function testSetFirstResult()
487 $qb = new QueryBuilder($this->conn);
488 $qb->setFirstResult(10);
490 $this->assertEquals(QueryBuilder::STATE_DIRTY, $qb->getState());
491 $this->assertEQuals(10, $qb->getFirstResult());
494 public function testResetQueryPart()
496 $qb = new QueryBuilder($this->conn);
498 $qb->select('u.*')->from('users', 'u')->where('u.name = ?');
500 $this->assertEquals('SELECT u.* FROM users u WHERE u.name = ?', (string)$qb);
501 $qb->resetQueryPart('where');
502 $this->assertEquals('SELECT u.* FROM users u', (string)$qb);
505 public function testResetQueryParts()
507 $qb = new QueryBuilder($this->conn);
509 $qb->select('u.*')->from('users', 'u')->where('u.name = ?')->orderBy('u.name');
511 $this->assertEquals('SELECT u.* FROM users u WHERE u.name = ? ORDER BY u.name ASC', (string)$qb);
512 $qb->resetQueryParts(array('where', 'orderBy'));
513 $this->assertEquals('SELECT u.* FROM users u', (string)$qb);
516 public function testCreateNamedParameter()
518 $qb = new QueryBuilder($this->conn);
520 $qb->select('u.*')->from('users', 'u')->where(
521 $qb->expr()->eq('u.name', $qb->createNamedParameter(10, \PDO::PARAM_INT))
524 $this->assertEquals('SELECT u.* FROM users u WHERE u.name = :dcValue1', (string)$qb);
525 $this->assertEquals(10, $qb->getParameter('dcValue1'));
528 public function testCreateNamedParameterCustomPlaceholder()
530 $qb = new QueryBuilder($this->conn);
532 $qb->select('u.*')->from('users', 'u')->where(
533 $qb->expr()->eq('u.name', $qb->createNamedParameter(10, \PDO::PARAM_INT, ':test'))
536 $this->assertEquals('SELECT u.* FROM users u WHERE u.name = :test', (string)$qb);
537 $this->assertEquals(10, $qb->getParameter('test'));
540 public function testCreatePositionalParameter()
542 $qb = new QueryBuilder($this->conn);
544 $qb->select('u.*')->from('users', 'u')->where(
545 $qb->expr()->eq('u.name', $qb->createPositionalParameter(10, \PDO::PARAM_INT))
548 $this->assertEquals('SELECT u.* FROM users u WHERE u.name = ?', (string)$qb);
549 $this->assertEquals(10, $qb->getParameter(1));
555 public function testReferenceJoinFromJoin()
557 $qb = new QueryBuilder($this->conn);
559 $qb->select("l.id", "mdsh.xcode", "mdso.xcode")
560 ->from("location_tree", "l")
561 ->join("l", "location_tree_pos", "p", "l.id = p.tree_id")
562 ->rightJoin("l", "hotel", "h", "h.location_id = l.id")
563 ->leftJoin("l", "offer_location", "ol", "l.id=ol.location_id")
564 ->leftJoin("ol", "mds_offer", "mdso", "ol.offer_id = mdso.offer_id")
565 ->leftJoin("h", "mds_hotel", "mdsh", "h.id = mdsh.hotel_id")
566 ->where("p.parent_id IN (:ids)")
567 ->andWhere("(mdso.xcode IS NOT NULL OR mdsh.xcode IS NOT NULL)");
569 $this->setExpectedException('Doctrine\DBAL\Query\QueryException', "The given alias 'ol' is not part of any FROM clause table. The currently registered FROM-clause aliases are: l");
570 $this->assertEquals('', $qb->getSQL());