2 namespace Doctrine\Tests\ORM\Query;
4 use Doctrine\ORM\Query,
5 Doctrine\ORM\Query\QueryException;
7 require_once __DIR__ . '/../../TestInit.php';
9 class LanguageRecognitionTest extends \Doctrine\Tests\OrmTestCase
13 protected function setUp()
15 $this->_em = $this->_getTestEntityManager();
18 public function assertValidDQL($dql, $debug = false)
21 $parserResult = $this->parseDql($dql);
22 } catch (QueryException $e) {
24 echo $e->getTraceAsString() . PHP_EOL;
27 $this->fail($e->getMessage());
31 public function assertInvalidDQL($dql, $debug = false)
34 $parserResult = $this->parseDql($dql);
36 $this->fail('No syntax errors were detected, when syntax errors were expected');
37 } catch (QueryException $e) {
39 echo $e->getMessage() . PHP_EOL;
40 echo $e->getTraceAsString() . PHP_EOL;
45 public function parseDql($dql, $hints = array())
47 $query = $this->_em->createQuery($dql);
48 $query->setHint(Query::HINT_FORCE_PARTIAL_LOAD, true);
51 foreach ($hints as $key => $value) {
52 $query->setHint($key, $value);
55 $parser = new \Doctrine\ORM\Query\Parser($query);
57 // We do NOT test SQL output here. That only unnecessarily slows down the tests!
58 $parser->setCustomOutputTreeWalker('Doctrine\Tests\Mocks\MockTreeWalker');
60 return $parser->parse();
63 public function testEmptyQueryString()
65 $this->assertInvalidDQL('');
68 public function testPlainFromClauseWithAlias()
70 $this->assertValidDQL('SELECT u.id FROM Doctrine\Tests\Models\CMS\CmsUser u');
73 public function testSelectSingleComponentWithAsterisk()
75 $this->assertValidDQL('SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u');
78 public function testSelectSingleComponentWithMultipleColumns()
80 $this->assertValidDQL('SELECT u.name, u.username FROM Doctrine\Tests\Models\CMS\CmsUser u');
83 public function testSelectMultipleComponentsUsingMultipleFrom()
85 $this->assertValidDQL('SELECT u, p FROM Doctrine\Tests\Models\CMS\CmsUser u, Doctrine\Tests\Models\CMS\CmsPhonenumber p WHERE u = p.user');
88 public function testSelectMultipleComponentsWithAsterisk()
90 $this->assertValidDQL('SELECT u, p FROM Doctrine\Tests\Models\CMS\CmsUser u JOIN u.phonenumbers p');
93 public function testSelectDistinctIsSupported()
95 $this->assertValidDQL('SELECT DISTINCT u.name FROM Doctrine\Tests\Models\CMS\CmsUser u');
98 public function testAggregateFunctionInSelect()
100 $this->assertValidDQL('SELECT COUNT(u.id) FROM Doctrine\Tests\Models\CMS\CmsUser u');
103 public function testDuplicatedAliasInAggregateFunction()
105 $this->assertInvalidDQL('SELECT COUNT(u.id) AS num, SUM(u.id) AS num FROM Doctrine\Tests\Models\CMS\CmsUser u');
108 public function testAggregateFunctionWithDistinctInSelect()
110 $this->assertValidDQL('SELECT COUNT(DISTINCT u.name) FROM Doctrine\Tests\Models\CMS\CmsUser u');
113 public function testFunctionalExpressionsSupportedInWherePart()
115 $this->assertValidDQL("SELECT u.name FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE TRIM(u.name) = 'someone'");
118 public function testArithmeticExpressionsSupportedInWherePart()
120 $this->assertValidDQL('SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE ((u.id + 5000) * u.id + 3) < 10000000');
123 public function testInExpressionSupportedInWherePart()
125 $this->assertValidDQL('SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.id IN (1, 2)');
128 public function testInExpressionWithoutSpacesSupportedInWherePart()
130 $this->assertValidDQL('SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.id IN (1,2,3)');
133 public function testNotInExpressionSupportedInWherePart()
135 $this->assertValidDQL('SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.id NOT IN (1)');
138 public function testInExpressionWithSingleValuedAssociationPathExpression()
140 $this->assertValidDQL("SELECT u FROM Doctrine\Tests\Models\Forum\ForumUser u WHERE u.avatar IN (?1, ?2)");
143 public function testInvalidInExpressionWithCollectionValuedAssociationPathExpression()
145 $this->assertInvalidDQL("SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.phonenumbers IN (?1, ?2)");
148 public function testInstanceOfExpressionSupportedInWherePart()
150 $this->assertValidDQL('SELECT u FROM Doctrine\Tests\Models\Company\CompanyPerson u WHERE u INSTANCE OF Doctrine\Tests\Models\Company\CompanyEmployee');
153 public function testInstanceOfExpressionWithInputParamSupportedInWherePart()
155 $this->assertValidDQL('SELECT u FROM Doctrine\Tests\Models\Company\CompanyPerson u WHERE u INSTANCE OF ?1');
158 public function testNotInstanceOfExpressionSupportedInWherePart()
160 $this->assertValidDQL('SELECT u FROM Doctrine\Tests\Models\Company\CompanyPerson u WHERE u NOT INSTANCE OF ?1');
163 public function testExistsExpressionSupportedInWherePart()
165 $this->assertValidDQL('SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE EXISTS (SELECT p.phonenumber FROM Doctrine\Tests\Models\CMS\CmsPhonenumber p WHERE p.phonenumber = 1234)');
168 public function testNotExistsExpressionSupportedInWherePart()
170 $this->assertValidDQL('SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE NOT EXISTS (SELECT p.phonenumber FROM Doctrine\Tests\Models\CMS\CmsPhonenumber p WHERE p.phonenumber = 1234)');
173 public function testAggregateFunctionInHavingClause()
175 $this->assertValidDQL('SELECT u.name FROM Doctrine\Tests\Models\CMS\CmsUser u LEFT JOIN u.phonenumbers p HAVING COUNT(p.phonenumber) > 2');
176 $this->assertValidDQL("SELECT u.name FROM Doctrine\Tests\Models\CMS\CmsUser u LEFT JOIN u.phonenumbers p HAVING MAX(u.name) = 'romanb'");
179 public function testLeftJoin()
181 $this->assertValidDQL('SELECT u, p FROM Doctrine\Tests\Models\CMS\CmsUser u LEFT JOIN u.phonenumbers p');
184 public function testJoin()
186 $this->assertValidDQL('SELECT u,p FROM Doctrine\Tests\Models\CMS\CmsUser u JOIN u.phonenumbers p');
189 public function testInnerJoin()
191 $this->assertValidDQL('SELECT u, p FROM Doctrine\Tests\Models\CMS\CmsUser u INNER JOIN u.phonenumbers p');
194 public function testMultipleLeftJoin()
196 $this->assertValidDQL('SELECT u, a, p FROM Doctrine\Tests\Models\CMS\CmsUser u LEFT JOIN u.articles a LEFT JOIN u.phonenumbers p');
199 public function testMultipleInnerJoin()
201 $this->assertValidDQL('SELECT u.name FROM Doctrine\Tests\Models\CMS\CmsUser u INNER JOIN u.articles a INNER JOIN u.phonenumbers p');
204 public function testMixingOfJoins()
206 $this->assertValidDQL('SELECT u.name, a.topic, p.phonenumber FROM Doctrine\Tests\Models\CMS\CmsUser u INNER JOIN u.articles a LEFT JOIN u.phonenumbers p');
209 public function testJoinClassPath()
211 $this->assertValidDQL('SELECT u.name FROM Doctrine\Tests\Models\CMS\CmsUser u JOIN Doctrine\Tests\Models\CMS\CmsArticle a WITH a.user = u.id');
214 public function testOrderBySingleColumn()
216 $this->assertValidDQL('SELECT u.name FROM Doctrine\Tests\Models\CMS\CmsUser u ORDER BY u.name');
219 public function testOrderBySingleColumnAscending()
221 $this->assertValidDQL('SELECT u.name FROM Doctrine\Tests\Models\CMS\CmsUser u ORDER BY u.name ASC');
224 public function testOrderBySingleColumnDescending()
226 $this->assertValidDQL('SELECT u.name FROM Doctrine\Tests\Models\CMS\CmsUser u ORDER BY u.name DESC');
229 public function testOrderByMultipleColumns()
231 $this->assertValidDQL('SELECT u.name, u.username FROM Doctrine\Tests\Models\CMS\CmsUser u ORDER BY u.username DESC, u.name DESC');
234 public function testSubselectInInExpression()
236 $this->assertValidDQL("SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.id NOT IN (SELECT u2.id FROM Doctrine\Tests\Models\CMS\CmsUser u2 WHERE u2.name = 'zYne')");
239 public function testSubselectInSelectPart()
241 $this->assertValidDQL("SELECT u.name, (SELECT COUNT(p.phonenumber) FROM Doctrine\Tests\Models\CMS\CmsPhonenumber p WHERE p.phonenumber = 1234) pcount FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.name = 'jon'");
244 public function testArithmeticExpressionInSelectPart()
246 $this->assertValidDQL("SELECT SUM(u.id) / COUNT(u.id) FROM Doctrine\Tests\Models\CMS\CmsUser u");
249 public function testArithmeticExpressionInSubselectPart()
251 $this->assertValidDQL("SELECT (SELECT SUM(u.id) / COUNT(u.id) FROM Doctrine\Tests\Models\CMS\CmsUser u2) value FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.name = 'jon'");
254 public function testArithmeticExpressionWithParenthesisInSubselectPart()
256 $this->assertValidDQL("SELECT (SELECT (SUM(u.id) / COUNT(u.id)) FROM Doctrine\Tests\Models\CMS\CmsUser u2) value FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.name = 'jon'");
262 public function testSelectLiteralInSubselect()
264 $this->assertValidDQL('SELECT (SELECT 1 FROM Doctrine\Tests\Models\CMS\CmsUser u2) value FROM Doctrine\Tests\Models\CMS\CmsUser u');
265 $this->assertValidDQL('SELECT (SELECT 0 FROM Doctrine\Tests\Models\CMS\CmsUser u2) value FROM Doctrine\Tests\Models\CMS\CmsUser u');
271 public function testConstantValueInSelect()
273 $this->assertValidDQL("SELECT u.name, 'foo' AS bar FROM Doctrine\Tests\Models\CMS\CmsUser u", true);
276 public function testDuplicateAliasInSubselectPart()
278 $this->assertInvalidDQL("SELECT (SELECT SUM(u.id) / COUNT(u.id) AS foo FROM Doctrine\Tests\Models\CMS\CmsUser u2) foo FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.name = 'jon'");
281 public function testPositionalInputParameter()
283 $this->assertValidDQL('SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.id = ?1');
286 public function testNamedInputParameter()
288 $this->assertValidDQL('SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.id = :id');
291 public function testJoinConditionOverrideNotSupported()
293 $this->assertInvalidDQL("SELECT u.name, p FROM Doctrine\Tests\Models\CMS\CmsUser u LEFT JOIN u.phonenumbers p ON p.phonenumber = '123 123'");
296 public function testIndexByClauseWithOneComponent()
298 $this->assertValidDQL('SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u INDEX BY u.id');
301 public function testIndexBySupportsJoins()
303 $this->assertValidDQL('SELECT u, a FROM Doctrine\Tests\Models\CMS\CmsUser u LEFT JOIN u.articles a INDEX BY a.id'); // INDEX BY is now referring to articles
306 public function testIndexBySupportsJoins2()
308 $this->assertValidDQL('SELECT u, p FROM Doctrine\Tests\Models\CMS\CmsUser u INDEX BY u.id LEFT JOIN u.phonenumbers p INDEX BY p.phonenumber');
311 public function testBetweenExpressionSupported()
313 $this->assertValidDQL("SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.name BETWEEN 'jepso' AND 'zYne'");
316 public function testNotBetweenExpressionSupported()
318 $this->assertValidDQL("SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.name NOT BETWEEN 'jepso' AND 'zYne'");
321 public function testLikeExpression()
323 $this->assertValidDQL("SELECT u.id FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.name LIKE 'z%'");
326 public function testNotLikeExpression()
328 $this->assertValidDQL("SELECT u.id FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.name NOT LIKE 'z%'");
331 public function testLikeExpressionWithCustomEscapeCharacter()
333 $this->assertValidDQL("SELECT u.id FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.name LIKE 'z|%' ESCAPE '|'");
336 public function testFieldComparisonWithoutAlias()
338 $this->assertInvalidDQL("SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE id = 1");
341 public function testDuplicatedAliasDeclaration()
343 $this->assertInvalidDQL("SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u INNER JOIN u.articles u WHERE u.id = 1");
346 public function testImplicitJoinInWhereOnSingleValuedAssociationPathExpression()
348 // This should be allowed because avatar is a single-value association.
349 // SQL: SELECT ... FROM forum_user fu INNER JOIN forum_avatar fa ON fu.avatar_id = fa.id WHERE fa.id = ?
350 $this->assertValidDQL("SELECT u FROM Doctrine\Tests\Models\Forum\ForumUser u JOIN u.avatar a WHERE a.id = ?1");
353 public function testImplicitJoinInWhereOnCollectionValuedPathExpression()
355 // This should be forbidden, because articles is a collection
356 $this->assertInvalidDQL("SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u JOIN u.articles a WHERE a.title = ?");
359 public function testInvalidSyntaxIsRejected()
361 $this->assertInvalidDQL("FOOBAR CmsUser");
362 $this->assertInvalidDQL("DELETE FROM Doctrine\Tests\Models\CMS\CmsUser.articles");
363 $this->assertInvalidDQL("SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u JOIN u.articles.comments");
365 // Currently UNDEFINED OFFSET error
366 $this->assertInvalidDQL("SELECT c FROM CmsUser.articles.comments c");
369 public function testUpdateWorksWithOneField()
371 $this->assertValidDQL("UPDATE Doctrine\Tests\Models\CMS\CmsUser u SET u.name = 'someone'");
374 public function testUpdateWorksWithMultipleFields()
376 $this->assertValidDQL("UPDATE Doctrine\Tests\Models\CMS\CmsUser u SET u.name = 'someone', u.username = 'some'");
379 public function testUpdateSupportsConditions()
381 $this->assertValidDQL("UPDATE Doctrine\Tests\Models\CMS\CmsUser u SET u.name = 'someone' WHERE u.id = 5");
384 public function testDeleteAll()
386 $this->assertValidDQL('DELETE FROM Doctrine\Tests\Models\CMS\CmsUser u');
389 public function testDeleteWithCondition()
391 $this->assertValidDQL('DELETE FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.id = 3');
395 * The main use case for this generalized style of join is when a join condition
396 * does not involve a foreign key relationship that is mapped to an entity relationship.
398 public function testImplicitJoinWithCartesianProductAndConditionInWhere()
400 $this->assertValidDQL("SELECT u, a FROM Doctrine\Tests\Models\CMS\CmsUser u, Doctrine\Tests\Models\CMS\CmsArticle a WHERE u.name = a.topic");
403 public function testAllExpressionWithCorrelatedSubquery()
405 $this->assertValidDQL('SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.id > ALL (SELECT u2.id FROM Doctrine\Tests\Models\CMS\CmsUser u2 WHERE u2.name = u.name)');
408 public function testCustomJoinsAndWithKeywordSupported()
410 $this->assertValidDQL('SELECT u, p FROM Doctrine\Tests\Models\CMS\CmsUser u INNER JOIN u.phonenumbers p WITH p.phonenumber = 123 WHERE u.id = 1');
413 public function testAnyExpressionWithCorrelatedSubquery()
415 $this->assertValidDQL('SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.id > ANY (SELECT u2.id FROM Doctrine\Tests\Models\CMS\CmsUser u2 WHERE u2.name = u.name)');
418 public function testSomeExpressionWithCorrelatedSubquery()
420 $this->assertValidDQL('SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.id > SOME (SELECT u2.id FROM Doctrine\Tests\Models\CMS\CmsUser u2 WHERE u2.name = u.name)');
423 public function testArithmeticExpressionWithoutParenthesisInWhereClause()
425 $this->assertValidDQL('SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE SIZE(u.phonenumbers) + 1 > 10');
428 public function testMemberOfExpression()
430 $this->assertValidDQL('SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE :param MEMBER OF u.phonenumbers');
431 //$this->assertValidDQL("SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE 'Joe' MEMBER OF u.nicknames");
434 public function testSizeFunction()
436 $this->assertValidDQL('SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE SIZE(u.phonenumbers) > 1');
439 public function testEmptyCollectionComparisonExpression()
441 $this->assertValidDQL('SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.phonenumbers IS EMPTY');
444 public function testSingleValuedAssociationFieldInWhere()
446 $this->assertValidDQL('SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.address = ?1');
447 $this->assertValidDQL('SELECT p FROM Doctrine\Tests\Models\CMS\CmsPhonenumber p WHERE p.user = ?1');
450 public function testBooleanLiteralInWhere()
452 $this->assertValidDQL('SELECT b FROM Doctrine\Tests\Models\Generic\BooleanModel b WHERE b.booleanField = true');
455 public function testSubqueryInSelectExpression()
457 $this->assertValidDQL('select u, (select max(p.phonenumber) from Doctrine\Tests\Models\CMS\CmsPhonenumber p) maxId from Doctrine\Tests\Models\CMS\CmsUser u');
460 public function testUsageOfQComponentOutsideSubquery()
462 $this->assertInvalidDQL('select u, (select max(p.phonenumber) from Doctrine\Tests\Models\CMS\CmsPhonenumber p) maxId from Doctrine\Tests\Models\CMS\CmsUser u WHERE p.user = ?1');
465 public function testUnknownAbstractSchemaName()
467 $this->assertInvalidDQL('SELECT u FROM UnknownClassName u');
470 public function testCorrectPartialObjectLoad()
472 $this->assertValidDQL('SELECT PARTIAL u.{id,name} FROM Doctrine\Tests\Models\CMS\CmsUser u');
475 public function testIncorrectPartialObjectLoadBecauseOfMissingIdentifier()
477 $this->assertInvalidDQL('SELECT PARTIAL u.{name} FROM Doctrine\Tests\Models\CMS\CmsUser u');
480 public function testScalarExpressionInSelect()
482 $this->assertValidDQL('SELECT u, 42 + u.id AS someNumber FROM Doctrine\Tests\Models\CMS\CmsUser u');
485 public function testInputParameterInSelect()
487 $this->assertValidDQL('SELECT u, u.id + ?1 AS someNumber FROM Doctrine\Tests\Models\CMS\CmsUser u');
493 public function testCustomFunctionsReturningStringInStringPrimary()
495 $this->_em->getConfiguration()->addCustomStringFunction('CC', 'Doctrine\ORM\Query\AST\Functions\ConcatFunction');
497 $this->assertValidDQL("SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE CC('%', u.name) LIKE '%foo%'", true);
503 public function testDQLKeywordInJoinIsAllowed()
505 $this->assertValidDQL('SELECT u FROM ' . __NAMESPACE__ . '\DQLKeywordsModelUser u JOIN u.group g');
511 public function testDQLKeywordInConditionIsAllowed()
513 $this->assertValidDQL('SELECT g FROM ' . __NAMESPACE__ . '\DQLKeywordsModelGroup g WHERE g.from=0');
516 /* The exception is currently thrown in the SQLWalker, not earlier.
517 public function testInverseSideSingleValuedAssociationPathNotAllowed()
519 $this->assertInvalidDQL('SELECT u.id FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.address = ?1');
526 public function testSelectOnlyNonRootEntityAlias()
528 $this->assertInvalidDQL('SELECT g FROM Doctrine\Tests\Models\CMS\CmsUser u JOIN u.groups g');
534 public function testInputParameterSingleChar()
536 $this->assertValidDQL('SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.name = :q');
542 public function testGroupBy()
544 $this->assertValidDQL('SELECT g.id, count(u.id) FROM Doctrine\Tests\Models\CMS\CmsGroup g JOIN g.users u GROUP BY g.id');
550 public function testGroupByIdentificationVariable()
552 $this->assertValidDQL('SELECT g, count(u.id) FROM Doctrine\Tests\Models\CMS\CmsGroup g JOIN g.users u GROUP BY g');
558 public function testGroupByUnknownIdentificationVariable()
560 $this->assertInvalidDQL('SELECT g, count(u.id) FROM Doctrine\Tests\Models\CMS\CmsGroup g JOIN g.users u GROUP BY m');
566 public function testSizeOfForeignKeyOneToManyPrimaryKeyEntity()
568 $this->assertValidDQL("SELECT a, t FROM Doctrine\Tests\Models\DDC117\DDC117Article a JOIN a.translations t WHERE SIZE(a.translations) > 0");
574 public function testSizeOfForeignKeyManyToManyPrimaryKeyEntity()
576 $this->assertValidDQL("SELECT e, t FROM Doctrine\Tests\Models\DDC117\DDC117Editor e JOIN e.reviewingTranslations t WHERE SIZE(e.reviewingTranslations) > 0");
579 public function testCaseSupportContainingNullIfExpression()
581 $this->assertValidDQL("SELECT u.id, NULLIF(u.name, u.name) AS shouldBeNull FROM Doctrine\Tests\Models\CMS\CmsUser u");
584 public function testCaseSupportContainingCoalesceExpression()
586 $this->assertValidDQL("select COALESCE(NULLIF(u.name, ''), u.username) as Display FROM Doctrine\Tests\Models\CMS\CmsUser u");
591 class DQLKeywordsModelUser
593 /** @Id @Column(type="integer") @GeneratedValue */
595 /** @OneToOne(targetEntity="DQLKeywordsModelGroup") */
600 class DQLKeywordsModelGroup
602 /** @Id @Column(type="integer") @GeneratedValue */