3 namespace Doctrine\Tests\ORM\Functional;
5 use Doctrine\Common\Collections\ArrayCollection;
7 use Doctrine\DBAL\Connection;
8 use Doctrine\Tests\Models\CMS\CmsUser,
9 Doctrine\Tests\Models\CMS\CmsArticle,
10 Doctrine\Tests\Models\CMS\CmsPhonenumber;
11 use Doctrine\ORM\Mapping\ClassMetadata;
12 use Doctrine\ORM\Query;
13 use Doctrine\ORM\Query\Parameter;
15 require_once __DIR__ . '/../../TestInit.php';
18 * Functional Query tests.
22 class QueryTest extends \Doctrine\Tests\OrmFunctionalTestCase
24 protected function setUp()
26 $this->useModelSet('cms');
30 public function testSimpleQueries()
33 $user->name = 'Guilherme';
34 $user->username = 'gblanco';
35 $user->status = 'developer';
36 $this->_em->persist($user);
40 $query = $this->_em->createQuery("select u, upper(u.name) from Doctrine\Tests\Models\CMS\CmsUser u where u.username = 'gblanco'");
42 $result = $query->getResult();
44 $this->assertEquals(1, count($result));
45 $this->assertInstanceOf('Doctrine\Tests\Models\CMS\CmsUser', $result[0][0]);
46 $this->assertEquals('Guilherme', $result[0][0]->name);
47 $this->assertEquals('gblanco', $result[0][0]->username);
48 $this->assertEquals('developer', $result[0][0]->status);
49 $this->assertEquals('GUILHERME', $result[0][1]);
51 $resultArray = $query->getArrayResult();
52 $this->assertEquals(1, count($resultArray));
53 $this->assertTrue(is_array($resultArray[0][0]));
54 $this->assertEquals('Guilherme', $resultArray[0][0]['name']);
55 $this->assertEquals('gblanco', $resultArray[0][0]['username']);
56 $this->assertEquals('developer', $resultArray[0][0]['status']);
57 $this->assertEquals('GUILHERME', $resultArray[0][1]);
59 $scalarResult = $query->getScalarResult();
60 $this->assertEquals(1, count($scalarResult));
61 $this->assertEquals('Guilherme', $scalarResult[0]['u_name']);
62 $this->assertEquals('gblanco', $scalarResult[0]['u_username']);
63 $this->assertEquals('developer', $scalarResult[0]['u_status']);
64 $this->assertEquals('GUILHERME', $scalarResult[0][1]);
66 $query = $this->_em->createQuery("select upper(u.name) from Doctrine\Tests\Models\CMS\CmsUser u where u.username = 'gblanco'");
67 $this->assertEquals('GUILHERME', $query->getSingleScalarResult());
70 public function testJoinQueries()
73 $user->name = 'Guilherme';
74 $user->username = 'gblanco';
75 $user->status = 'developer';
77 $article1 = new CmsArticle;
78 $article1->topic = "Doctrine 2";
79 $article1->text = "This is an introduction to Doctrine 2.";
80 $user->addArticle($article1);
82 $article2 = new CmsArticle;
83 $article2->topic = "Symfony 2";
84 $article2->text = "This is an introduction to Symfony 2.";
85 $user->addArticle($article2);
87 $this->_em->persist($user);
88 $this->_em->persist($article1);
89 $this->_em->persist($article2);
94 $query = $this->_em->createQuery("select u, a from Doctrine\Tests\Models\CMS\CmsUser u join u.articles a ORDER BY a.topic");
95 $users = $query->getResult();
96 $this->assertEquals(1, count($users));
97 $this->assertInstanceOf('Doctrine\Tests\Models\CMS\CmsUser', $users[0]);
98 $this->assertEquals(2, count($users[0]->articles));
99 $this->assertEquals('Doctrine 2', $users[0]->articles[0]->topic);
100 $this->assertEquals('Symfony 2', $users[0]->articles[1]->topic);
103 public function testUsingZeroBasedQueryParameterShouldWork()
106 $user->name = 'Jonathan';
107 $user->username = 'jwage';
108 $user->status = 'developer';
109 $this->_em->persist($user);
113 $q = $this->_em->createQuery('SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.username = ?0');
114 $q->setParameter(0, 'jwage');
115 $user = $q->getSingleResult();
117 $this->assertNotNull($user);
120 public function testUsingUnknownQueryParameterShouldThrowException()
122 $this->setExpectedException(
123 "Doctrine\ORM\Query\QueryException",
124 "Invalid parameter: token 2 is not defined in the query."
127 $q = $this->_em->createQuery('SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.name = ?1');
128 $q->setParameter(2, 'jwage');
129 $user = $q->getSingleResult();
132 public function testMismatchingParamExpectedParamCount()
134 $this->setExpectedException(
135 "Doctrine\ORM\Query\QueryException",
136 "Invalid parameter number: number of bound variables does not match number of tokens"
139 $q = $this->_em->createQuery('SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.name = ?1');
140 $q->setParameter(1, 'jwage');
141 $q->setParameter(2, 'jwage');
143 $user = $q->getSingleResult();
146 public function testInvalidInputParameterThrowsException()
148 $this->setExpectedException("Doctrine\ORM\Query\QueryException");
150 $q = $this->_em->createQuery('SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.name = ?');
151 $q->setParameter(1, 'jwage');
152 $user = $q->getSingleResult();
155 public function testSetParameters()
157 $q = $this->_em->createQuery('SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.name = ?1 AND u.status = ?2');
159 $parameters = new ArrayCollection();
160 $parameters->add(new Parameter(1, 'jwage'));
161 $parameters->add(new Parameter(2, 'active'));
163 $q->setParameters($parameters);
164 $users = $q->getResult();
167 public function testSetParametersBackwardsCompatible()
169 $q = $this->_em->createQuery('SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.name = ?1 AND u.status = ?2');
170 $q->setParameters(array(1 => 'jwage', 2 => 'active'));
172 $users = $q->getResult();
178 public function testIterateResultAsArrayAndParams()
180 $article1 = new CmsArticle;
181 $article1->topic = "Doctrine 2";
182 $article1->text = "This is an introduction to Doctrine 2.";
184 $article2 = new CmsArticle;
185 $article2->topic = "Symfony 2";
186 $article2->text = "This is an introduction to Symfony 2.";
188 $this->_em->persist($article1);
189 $this->_em->persist($article2);
193 $articleId = $article1->id;
195 $query = $this->_em->createQuery("select a from Doctrine\Tests\Models\CMS\CmsArticle a WHERE a.topic = ?1");
196 $articles = $query->iterate(new ArrayCollection(array(new Parameter(1, 'Doctrine 2'))), Query::HYDRATE_ARRAY);
199 foreach ($articles AS $article) {
202 $this->assertEquals(1, count($found));
203 $this->assertEquals(array(
204 array(array('id' => $articleId, 'topic' => 'Doctrine 2', 'text' => 'This is an introduction to Doctrine 2.', 'version' => 1))
208 public function testIterateResult_IterativelyBuildUpUnitOfWork()
210 $article1 = new CmsArticle;
211 $article1->topic = "Doctrine 2";
212 $article1->text = "This is an introduction to Doctrine 2.";
214 $article2 = new CmsArticle;
215 $article2->topic = "Symfony 2";
216 $article2->text = "This is an introduction to Symfony 2.";
218 $this->_em->persist($article1);
219 $this->_em->persist($article2);
224 $query = $this->_em->createQuery("select a from Doctrine\Tests\Models\CMS\CmsArticle a");
225 $articles = $query->iterate();
229 foreach($articles AS $row) {
231 $topics[] = $article->topic;
233 $identityMap = $this->_em->getUnitOfWork()->getIdentityMap();
234 $identityMapCount = count($identityMap['Doctrine\Tests\Models\CMS\CmsArticle']);
235 $this->assertTrue($identityMapCount>$iteratedCount);
240 $this->assertEquals(array("Doctrine 2", "Symfony 2"), $topics);
241 $this->assertEquals(2, $iteratedCount);
247 public function testIterateResultClearEveryCycle()
249 $article1 = new CmsArticle;
250 $article1->topic = "Doctrine 2";
251 $article1->text = "This is an introduction to Doctrine 2.";
253 $article2 = new CmsArticle;
254 $article2->topic = "Symfony 2";
255 $article2->text = "This is an introduction to Symfony 2.";
257 $this->_em->persist($article1);
258 $this->_em->persist($article2);
263 $query = $this->_em->createQuery("select a from Doctrine\Tests\Models\CMS\CmsArticle a");
264 $articles = $query->iterate();
268 foreach($articles AS $row) {
270 $topics[] = $article->topic;
277 $this->assertEquals(array("Doctrine 2", "Symfony 2"), $topics);
278 $this->assertEquals(2, $iteratedCount);
284 * @expectedException \Doctrine\ORM\Query\QueryException
286 public function testIterateResult_FetchJoinedCollection_ThrowsException()
288 $query = $this->_em->createQuery("SELECT u, a FROM Doctrine\Tests\Models\CMS\CmsUser u JOIN u.articles a");
289 $articles = $query->iterate();
293 * @expectedException Doctrine\ORM\NoResultException
295 public function testGetSingleResultThrowsExceptionOnNoResult()
297 $this->_em->createQuery("select a from Doctrine\Tests\Models\CMS\CmsArticle a")
302 * @expectedException Doctrine\ORM\NoResultException
304 public function testGetSingleScalarResultThrowsExceptionOnNoResult()
306 $this->_em->createQuery("select a from Doctrine\Tests\Models\CMS\CmsArticle a")
307 ->getSingleScalarResult();
311 * @expectedException Doctrine\ORM\NonUniqueResultException
313 public function testGetSingleScalarResultThrowsExceptionOnNonUniqueResult()
316 $user->name = 'Guilherme';
317 $user->username = 'gblanco';
318 $user->status = 'developer';
320 $article1 = new CmsArticle;
321 $article1->topic = "Doctrine 2";
322 $article1->text = "This is an introduction to Doctrine 2.";
323 $user->addArticle($article1);
325 $article2 = new CmsArticle;
326 $article2->topic = "Symfony 2";
327 $article2->text = "This is an introduction to Symfony 2.";
328 $user->addArticle($article2);
330 $this->_em->persist($user);
331 $this->_em->persist($article1);
332 $this->_em->persist($article2);
337 $this->_em->createQuery("select a from Doctrine\Tests\Models\CMS\CmsArticle a")
338 ->getSingleScalarResult();
341 public function testModifiedLimitQuery()
343 for ($i = 0; $i < 5; $i++) {
345 $user->name = 'Guilherme' . $i;
346 $user->username = 'gblanco' . $i;
347 $user->status = 'developer';
348 $this->_em->persist($user);
354 $data = $this->_em->createQuery('SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u')
359 $this->assertEquals(2, count($data));
360 $this->assertEquals('gblanco1', $data[0]->username);
361 $this->assertEquals('gblanco2', $data[1]->username);
363 $data = $this->_em->createQuery('SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u')
368 $this->assertEquals(2, count($data));
369 $this->assertEquals('gblanco3', $data[0]->username);
370 $this->assertEquals('gblanco4', $data[1]->username);
372 $data = $this->_em->createQuery('SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u')
378 public function testSupportsQueriesWithEntityNamespaces()
380 $this->_em->getConfiguration()->addEntityNamespace('CMS', 'Doctrine\Tests\Models\CMS');
383 $query = $this->_em->createQuery('UPDATE CMS:CmsUser u SET u.name = ?1');
384 $this->assertEquals('UPDATE cms_users SET name = ?', $query->getSql());
386 } catch (\Exception $e) {
387 $this->fail($e->getMessage());
390 $this->_em->getConfiguration()->setEntityNamespaces(array());
396 public function testEntityParameters()
398 $article = new CmsArticle;
399 $article->topic = "dr. dolittle";
400 $article->text = "Once upon a time ...";
401 $author = new CmsUser;
402 $author->name = "anonymous";
403 $author->username = "anon";
404 $author->status = "here";
405 $article->user = $author;
406 $this->_em->persist($author);
407 $this->_em->persist($article);
410 //$this->_em->getConnection()->getConfiguration()->setSQLLogger(new \Doctrine\DBAL\Logging\EchoSQLLogger);
411 $q = $this->_em->createQuery("select a from Doctrine\Tests\Models\CMS\CmsArticle a where a.topic = :topic and a.user = :user")
412 ->setParameter("user", $this->_em->getReference('Doctrine\Tests\Models\CMS\CmsUser', $author->id))
413 ->setParameter("topic", "dr. dolittle");
415 $result = $q->getResult();
416 $this->assertEquals(1, count($result));
417 $this->assertInstanceOf('Doctrine\Tests\Models\CMS\CmsArticle', $result[0]);
418 $this->assertEquals("dr. dolittle", $result[0]->topic);
419 $this->assertInstanceOf('Doctrine\ORM\Proxy\Proxy', $result[0]->user);
420 $this->assertFalse($result[0]->user->__isInitialized__);
426 public function testEnableFetchEagerMode()
428 for ($i = 0; $i < 10; $i++) {
429 $article = new CmsArticle;
430 $article->topic = "dr. dolittle";
431 $article->text = "Once upon a time ...";
432 $author = new CmsUser;
433 $author->name = "anonymous";
434 $author->username = "anon".$i;
435 $author->status = "here";
436 $article->user = $author;
437 $this->_em->persist($author);
438 $this->_em->persist($article);
443 $articles = $this->_em->createQuery('select a from Doctrine\Tests\Models\CMS\CmsArticle a')
444 ->setFetchMode('Doctrine\Tests\Models\CMS\CmsArticle', 'user', ClassMetadata::FETCH_EAGER)
447 $this->assertEquals(10, count($articles));
448 foreach ($articles AS $article) {
449 $this->assertNotInstanceOf('Doctrine\ORM\Proxy\Proxy', $article);
456 public function testgetOneOrNullResult()
459 $user->name = 'Guilherme';
460 $user->username = 'gblanco';
461 $user->status = 'developer';
462 $this->_em->persist($user);
466 $query = $this->_em->createQuery("select u from Doctrine\Tests\Models\CMS\CmsUser u where u.username = 'gblanco'");
468 $fetchedUser = $query->getOneOrNullResult();
469 $this->assertInstanceOf('Doctrine\Tests\Models\CMS\CmsUser', $fetchedUser);
470 $this->assertEquals('gblanco', $fetchedUser->username);
472 $query = $this->_em->createQuery("select u.username from Doctrine\Tests\Models\CMS\CmsUser u where u.username = 'gblanco'");
473 $fetchedUsername = $query->getOneOrNullResult(Query::HYDRATE_SINGLE_SCALAR);
474 $this->assertEquals('gblanco', $fetchedUsername);
480 public function testgetOneOrNullResultSeveralRows()
483 $user->name = 'Guilherme';
484 $user->username = 'gblanco';
485 $user->status = 'developer';
486 $this->_em->persist($user);
488 $user->name = 'Roman';
489 $user->username = 'romanb';
490 $user->status = 'developer';
491 $this->_em->persist($user);
495 $query = $this->_em->createQuery("select u from Doctrine\Tests\Models\CMS\CmsUser u");
497 $this->setExpectedException('Doctrine\ORM\NonUniqueResultException');
498 $fetchedUser = $query->getOneOrNullResult();
504 public function testgetOneOrNullResultNoRows()
506 $query = $this->_em->createQuery("select u from Doctrine\Tests\Models\CMS\CmsUser u");
507 $this->assertNull($query->getOneOrNullResult());
509 $query = $this->_em->createQuery("select u.username from Doctrine\Tests\Models\CMS\CmsUser u where u.username = 'gblanco'");
510 $this->assertNull($query->getOneOrNullResult(Query::HYDRATE_SCALAR));
516 public function testParameterOrder()
518 $user1 = new CmsUser;
519 $user1->name = 'Benjamin';
520 $user1->username = 'beberlei';
521 $user1->status = 'developer';
522 $this->_em->persist($user1);
524 $user2 = new CmsUser;
525 $user2->name = 'Roman';
526 $user2->username = 'romanb';
527 $user2->status = 'developer';
528 $this->_em->persist($user2);
530 $user3 = new CmsUser;
531 $user3->name = 'Jonathan';
532 $user3->username = 'jwage';
533 $user3->status = 'developer';
534 $this->_em->persist($user3);
539 $query = $this->_em->createQuery("SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.status = :a AND u.id IN (:b)");
540 $query->setParameters(new ArrayCollection(array(
541 new Parameter('b', array($user1->id, $user2->id, $user3->id)),
542 new Parameter('a', 'developer')
544 $result = $query->getResult();
546 $this->assertEquals(3, count($result));
549 public function testDqlWithAutoInferOfParameters()
552 $user->name = 'Benjamin';
553 $user->username = 'beberlei';
554 $user->status = 'developer';
555 $this->_em->persist($user);
558 $user->name = 'Roman';
559 $user->username = 'romanb';
560 $user->status = 'developer';
561 $this->_em->persist($user);
564 $user->name = 'Jonathan';
565 $user->username = 'jwage';
566 $user->status = 'developer';
567 $this->_em->persist($user);
572 $query = $this->_em->createQuery("SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.username IN (?0)");
573 $query->setParameter(0, array('beberlei', 'jwage'));
575 $users = $query->execute();
577 $this->assertEquals(2, count($users));
580 public function testQueryBuilderWithStringWhereClauseContainingOrAndConditionalPrimary()
582 $qb = $this->_em->createQueryBuilder();
584 ->from('Doctrine\Tests\Models\CMS\CmsUser', 'u')
585 ->innerJoin('u.articles', 'a')
586 ->where('(u.id = 0) OR (u.id IS NULL)');
588 $query = $qb->getQuery();
589 $users = $query->execute();
591 $this->assertEquals(0, count($users));
594 public function testQueryWithArrayOfEntitiesAsParameter()
596 $userA = new CmsUser;
597 $userA->name = 'Benjamin';
598 $userA->username = 'beberlei';
599 $userA->status = 'developer';
600 $this->_em->persist($userA);
602 $userB = new CmsUser;
603 $userB->name = 'Roman';
604 $userB->username = 'romanb';
605 $userB->status = 'developer';
606 $this->_em->persist($userB);
608 $userC = new CmsUser;
609 $userC->name = 'Jonathan';
610 $userC->username = 'jwage';
611 $userC->status = 'developer';
612 $this->_em->persist($userC);
617 $query = $this->_em->createQuery("SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u IN (?0) OR u.username = ?1");
618 $query->setParameter(0, array($userA, $userC));
619 $query->setParameter(1, 'beberlei');
621 $users = $query->execute();
623 $this->assertEquals(2, count($users));
626 public function testQueryWithHiddenAsSelectExpression()
628 $userA = new CmsUser;
629 $userA->name = 'Benjamin';
630 $userA->username = 'beberlei';
631 $userA->status = 'developer';
632 $this->_em->persist($userA);
634 $userB = new CmsUser;
635 $userB->name = 'Roman';
636 $userB->username = 'romanb';
637 $userB->status = 'developer';
638 $this->_em->persist($userB);
640 $userC = new CmsUser;
641 $userC->name = 'Jonathan';
642 $userC->username = 'jwage';
643 $userC->status = 'developer';
644 $this->_em->persist($userC);
649 $query = $this->_em->createQuery("SELECT u, (SELECT COUNT(u2.id) FROM Doctrine\Tests\Models\CMS\CmsUser u2) AS HIDDEN total FROM Doctrine\Tests\Models\CMS\CmsUser u");
650 $users = $query->execute();
652 $this->assertEquals(3, count($users));
653 $this->assertInstanceOf('Doctrine\Tests\Models\CMS\CmsUser', $users[0]);
659 public function testSetParameterBindingSingleIdentifierObject()
661 $userC = new CmsUser;
662 $userC->name = 'Jonathan';
663 $userC->username = 'jwage';
664 $userC->status = 'developer';
665 $this->_em->persist($userC);
670 $q = $this->_em->createQuery("SELECT DISTINCT u from Doctrine\Tests\Models\CMS\CmsUser u WHERE u.id = ?1");
671 $q->setParameter(1, $userC);
673 $this->assertEquals($userC, $q->getParameter(1)->getValue());
675 // Parameter is not converted before, but it should be converted during execution. Test should not fail here
683 public function testUnexpectedResultException()
685 $dql = "SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u";
688 $u1->name = 'Fabio B. Silva';
689 $u1->username = 'FabioBatSilva';
690 $u1->status = 'developer';
692 $u2->username = 'test';
693 $u2->status = 'tester';
696 $this->_em->createQuery($dql)->getSingleResult();
697 $this->fail('Expected exception "\Doctrine\ORM\NoResultException".');
698 } catch (\Doctrine\ORM\UnexpectedResultException $exc) {
699 $this->assertInstanceOf('\Doctrine\ORM\NoResultException', $exc);
703 $this->_em->persist($u1);
704 $this->_em->persist($u2);
709 $this->_em->createQuery($dql)->getSingleResult();
710 $this->fail('Expected exception "\Doctrine\ORM\NonUniqueResultException".');
711 } catch (\Doctrine\ORM\UnexpectedResultException $exc) {
712 $this->assertInstanceOf('\Doctrine\ORM\NonUniqueResultException', $exc);
716 public function testMultipleJoinComponentsUsingInnerJoin()
718 $userA = new CmsUser;
719 $userA->name = 'Benjamin';
720 $userA->username = 'beberlei';
721 $userA->status = 'developer';
723 $phonenumberA = new CmsPhonenumber;
724 $phonenumberA->phonenumber = '111111';
725 $userA->addPhonenumber($phonenumberA);
727 $userB = new CmsUser;
728 $userB->name = 'Alexander';
729 $userB->username = 'asm89';
730 $userB->status = 'developer';
732 $this->_em->persist($userA);
733 $this->_em->persist($userB);
737 $query = $this->_em->createQuery("
739 FROM Doctrine\Tests\Models\CMS\CmsUser u
740 INNER JOIN Doctrine\Tests\Models\CMS\CmsPhonenumber p WITH u = p.user
742 $users = $query->execute();
744 $this->assertEquals(2, count($users));
745 $this->assertInstanceOf('Doctrine\Tests\Models\CMS\CmsUser', $users[0]);
746 $this->assertInstanceOf('Doctrine\Tests\Models\CMS\CmsPhonenumber', $users[1]);
749 public function testMultipleJoinComponentsUsingLeftJoin()
751 $userA = new CmsUser;
752 $userA->name = 'Benjamin';
753 $userA->username = 'beberlei';
754 $userA->status = 'developer';
756 $phonenumberA = new CmsPhonenumber;
757 $phonenumberA->phonenumber = '111111';
758 $userA->addPhonenumber($phonenumberA);
760 $userB = new CmsUser;
761 $userB->name = 'Alexander';
762 $userB->username = 'asm89';
763 $userB->status = 'developer';
765 $this->_em->persist($userA);
766 $this->_em->persist($userB);
770 $query = $this->_em->createQuery("
772 FROM Doctrine\Tests\Models\CMS\CmsUser u
773 LEFT JOIN Doctrine\Tests\Models\CMS\CmsPhonenumber p WITH u = p.user
775 $users = $query->execute();
777 $this->assertEquals(4, count($users));
778 $this->assertInstanceOf('Doctrine\Tests\Models\CMS\CmsUser', $users[0]);
779 $this->assertInstanceOf('Doctrine\Tests\Models\CMS\CmsPhonenumber', $users[1]);
780 $this->assertInstanceOf('Doctrine\Tests\Models\CMS\CmsUser', $users[2]);
781 $this->assertNull($users[3]);