PageRenderTime 55ms CodeModel.GetById 25ms RepoModel.GetById 0ms app.codeStats 1ms

/vendor/doctrine/orm/tests/Doctrine/Tests/ORM/Query/SelectSqlGenerationTest.php

https://github.com/DevAlien/DriverMaps
PHP | 1807 lines | 1331 code | 220 blank | 256 comment | 12 complexity | 093e6a5d4a921e5b6a6ac3e087e411a8 MD5 | raw file

Large files files are truncated, but you can click here to view the full file

  1. <?php
  2. namespace Doctrine\Tests\ORM\Query;
  3. use Doctrine\DBAL\Types\Type as DBALType;
  4. use Doctrine\ORM\Query;
  5. require_once __DIR__ . '/../../TestInit.php';
  6. class SelectSqlGenerationTest extends \Doctrine\Tests\OrmTestCase
  7. {
  8. private $_em;
  9. protected function setUp()
  10. {
  11. $this->_em = $this->_getTestEntityManager();
  12. }
  13. /**
  14. * Assert a valid SQL generation.
  15. *
  16. * @param string $dqlToBeTested
  17. * @param string $sqlToBeConfirmed
  18. * @param array $queryHints
  19. * @param array $queryParams
  20. */
  21. public function assertSqlGeneration($dqlToBeTested, $sqlToBeConfirmed, array $queryHints = array(), array $queryParams = array())
  22. {
  23. try {
  24. $query = $this->_em->createQuery($dqlToBeTested);
  25. foreach ($queryParams AS $name => $value) {
  26. $query->setParameter($name, $value);
  27. }
  28. $query->setHint(Query::HINT_FORCE_PARTIAL_LOAD, true)
  29. ->useQueryCache(false);
  30. foreach ($queryHints AS $name => $value) {
  31. $query->setHint($name, $value);
  32. }
  33. $sqlGenerated = $query->getSQL();
  34. parent::assertEquals(
  35. $sqlToBeConfirmed,
  36. $sqlGenerated,
  37. sprintf('"%s" is not equal of "%s"', $sqlGenerated, $sqlToBeConfirmed)
  38. );
  39. $query->free();
  40. } catch (\Exception $e) {
  41. $this->fail($e->getMessage() ."\n".$e->getTraceAsString());
  42. }
  43. }
  44. /**
  45. * Asser an invalid SQL generation.
  46. *
  47. * @param string $dqlToBeTested
  48. * @param string $expectedException
  49. * @param array $queryHints
  50. * @param array $queryParams
  51. */
  52. public function assertInvalidSqlGeneration($dqlToBeTested, $expectedException, array $queryHints = array(), array $queryParams = array())
  53. {
  54. $this->setExpectedException($expectedException);
  55. $query = $this->_em->createQuery($dqlToBeTested);
  56. foreach ($queryParams AS $name => $value) {
  57. $query->setParameter($name, $value);
  58. }
  59. $query->setHint(Query::HINT_FORCE_PARTIAL_LOAD, true)
  60. ->useQueryCache(false);
  61. foreach ($queryHints AS $name => $value) {
  62. $query->setHint($name, $value);
  63. }
  64. $sql = $query->getSql();
  65. $query->free();
  66. // If we reached here, test failed
  67. $this->fail($sql);
  68. }
  69. public function testSupportsSelectForAllFields()
  70. {
  71. $this->assertSqlGeneration(
  72. 'SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u',
  73. 'SELECT c0_.id AS id0, c0_.status AS status1, c0_.username AS username2, c0_.name AS name3 FROM cms_users c0_'
  74. );
  75. }
  76. public function testSupportsSelectForOneField()
  77. {
  78. $this->assertSqlGeneration(
  79. 'SELECT u.id FROM Doctrine\Tests\Models\CMS\CmsUser u',
  80. 'SELECT c0_.id AS id0 FROM cms_users c0_'
  81. );
  82. }
  83. public function testSupportsSelectForOneNestedField()
  84. {
  85. $this->assertSqlGeneration(
  86. 'SELECT u.id FROM Doctrine\Tests\Models\CMS\CmsArticle a JOIN a.user u',
  87. 'SELECT c0_.id AS id0 FROM cms_articles c1_ INNER JOIN cms_users c0_ ON c1_.user_id = c0_.id'
  88. );
  89. }
  90. public function testSupportsSelectForAllNestedField()
  91. {
  92. $this->assertSqlGeneration(
  93. 'SELECT a FROM Doctrine\Tests\Models\CMS\CmsArticle a JOIN a.user u ORDER BY u.name ASC',
  94. 'SELECT c0_.id AS id0, c0_.topic AS topic1, c0_.text AS text2, c0_.version AS version3 FROM cms_articles c0_ INNER JOIN cms_users c1_ ON c0_.user_id = c1_.id ORDER BY c1_.name ASC'
  95. );
  96. }
  97. public function testSupportsSelectForMultipleColumnsOfASingleComponent()
  98. {
  99. $this->assertSqlGeneration(
  100. 'SELECT u.username, u.name FROM Doctrine\Tests\Models\CMS\CmsUser u',
  101. 'SELECT c0_.username AS username0, c0_.name AS name1 FROM cms_users c0_'
  102. );
  103. }
  104. public function testSupportsSelectUsingMultipleFromComponents()
  105. {
  106. $this->assertSqlGeneration(
  107. 'SELECT u, p FROM Doctrine\Tests\Models\CMS\CmsUser u, Doctrine\Tests\Models\CMS\CmsPhonenumber p WHERE u = p.user',
  108. 'SELECT c0_.id AS id0, c0_.status AS status1, c0_.username AS username2, c0_.name AS name3, c1_.phonenumber AS phonenumber4 FROM cms_users c0_, cms_phonenumbers c1_ WHERE c0_.id = c1_.user_id'
  109. );
  110. }
  111. public function testSupportsJoinOnMultipleComponents()
  112. {
  113. $this->assertSqlGeneration(
  114. 'SELECT u, p FROM Doctrine\Tests\Models\CMS\CmsUser u JOIN Doctrine\Tests\Models\CMS\CmsPhonenumber p WITH u = p.user',
  115. 'SELECT c0_.id AS id0, c0_.status AS status1, c0_.username AS username2, c0_.name AS name3, c1_.phonenumber AS phonenumber4 FROM cms_users c0_ INNER JOIN cms_phonenumbers c1_ ON (c0_.id = c1_.user_id)'
  116. );
  117. }
  118. public function testSupportsJoinOnMultipleComponentsWithJoinedInheritanceType()
  119. {
  120. $this->assertSqlGeneration(
  121. 'SELECT e FROM Doctrine\Tests\Models\Company\CompanyEmployee e JOIN Doctrine\Tests\Models\Company\CompanyManager m WITH e.id = m.id',
  122. 'SELECT c0_.id AS id0, c0_.name AS name1, c1_.salary AS salary2, c1_.department AS department3, c1_.startDate AS startDate4, c0_.discr AS discr5 FROM company_employees c1_ INNER JOIN company_persons c0_ ON c1_.id = c0_.id INNER JOIN company_managers c2_ INNER JOIN company_employees c3_ ON c2_.id = c3_.id INNER JOIN company_persons c4_ ON c2_.id = c4_.id AND (c0_.id = c4_.id)'
  123. );
  124. $this->assertSqlGeneration(
  125. 'SELECT e FROM Doctrine\Tests\Models\Company\CompanyEmployee e LEFT JOIN Doctrine\Tests\Models\Company\CompanyManager m WITH e.id = m.id',
  126. 'SELECT c0_.id AS id0, c0_.name AS name1, c1_.salary AS salary2, c1_.department AS department3, c1_.startDate AS startDate4, c0_.discr AS discr5 FROM company_employees c1_ INNER JOIN company_persons c0_ ON c1_.id = c0_.id LEFT JOIN company_managers c2_ INNER JOIN company_employees c3_ ON c2_.id = c3_.id INNER JOIN company_persons c4_ ON c2_.id = c4_.id ON (c0_.id = c4_.id)'
  127. );
  128. }
  129. public function testSupportsSelectWithCollectionAssociationJoin()
  130. {
  131. $this->assertSqlGeneration(
  132. 'SELECT u, p FROM Doctrine\Tests\Models\CMS\CmsUser u JOIN u.phonenumbers p',
  133. 'SELECT c0_.id AS id0, c0_.status AS status1, c0_.username AS username2, c0_.name AS name3, c1_.phonenumber AS phonenumber4 FROM cms_users c0_ INNER JOIN cms_phonenumbers c1_ ON c0_.id = c1_.user_id'
  134. );
  135. }
  136. public function testSupportsSelectWithSingleValuedAssociationJoin()
  137. {
  138. $this->assertSqlGeneration(
  139. 'SELECT u, a FROM Doctrine\Tests\Models\Forum\ForumUser u JOIN u.avatar a',
  140. 'SELECT f0_.id AS id0, f0_.username AS username1, f1_.id AS id2 FROM forum_users f0_ INNER JOIN forum_avatars f1_ ON f0_.avatar_id = f1_.id'
  141. );
  142. }
  143. public function testSelectCorrelatedSubqueryComplexMathematicalExpression()
  144. {
  145. $this->assertSqlGeneration(
  146. 'SELECT (SELECT (count(p.phonenumber)+5)*10 FROM Doctrine\Tests\Models\CMS\CmsPhonenumber p JOIN p.user ui WHERE ui.id = u.id) AS c FROM Doctrine\Tests\Models\CMS\CmsUser u',
  147. 'SELECT (SELECT (count(c0_.phonenumber) + 5) * 10 AS sclr1 FROM cms_phonenumbers c0_ INNER JOIN cms_users c1_ ON c0_.user_id = c1_.id WHERE c1_.id = c2_.id) AS sclr0 FROM cms_users c2_'
  148. );
  149. }
  150. public function testSelectComplexMathematicalExpression()
  151. {
  152. $this->assertSqlGeneration(
  153. 'SELECT (count(p.phonenumber)+5)*10 FROM Doctrine\Tests\Models\CMS\CmsPhonenumber p JOIN p.user ui WHERE ui.id = ?1',
  154. 'SELECT (count(c0_.phonenumber) + 5) * 10 AS sclr0 FROM cms_phonenumbers c0_ INNER JOIN cms_users c1_ ON c0_.user_id = c1_.id WHERE c1_.id = ?'
  155. );
  156. }
  157. /* NOT (YET?) SUPPORTED.
  158. Can be supported if SimpleSelectExpresion supports SingleValuedPathExpression instead of StateFieldPathExpression.
  159. public function testSingleAssociationPathExpressionInSubselect()
  160. {
  161. $this->assertSqlGeneration(
  162. 'SELECT (SELECT p.user FROM Doctrine\Tests\Models\CMS\CmsPhonenumber p WHERE p.user = u) user_id FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.id = ?1',
  163. 'SELECT (SELECT c0_.user_id FROM cms_phonenumbers c0_ WHERE c0_.user_id = c1_.id) AS sclr0 FROM cms_users c1_ WHERE c1_.id = ?'
  164. );
  165. }*/
  166. /**
  167. * @group DDC-1077
  168. */
  169. public function testConstantValueInSelect()
  170. {
  171. $this->assertSqlGeneration(
  172. "SELECT u.name, 'foo' AS bar FROM Doctrine\Tests\Models\CMS\CmsUser u",
  173. "SELECT c0_.name AS name0, 'foo' AS sclr1 FROM cms_users c0_"
  174. );
  175. }
  176. public function testSupportsOrderByWithAscAsDefault()
  177. {
  178. $this->assertSqlGeneration(
  179. 'SELECT u FROM Doctrine\Tests\Models\Forum\ForumUser u ORDER BY u.id',
  180. 'SELECT f0_.id AS id0, f0_.username AS username1 FROM forum_users f0_ ORDER BY f0_.id ASC'
  181. );
  182. }
  183. public function testSupportsOrderByAsc()
  184. {
  185. $this->assertSqlGeneration(
  186. 'SELECT u FROM Doctrine\Tests\Models\Forum\ForumUser u ORDER BY u.id asc',
  187. 'SELECT f0_.id AS id0, f0_.username AS username1 FROM forum_users f0_ ORDER BY f0_.id ASC'
  188. );
  189. }
  190. public function testSupportsOrderByDesc()
  191. {
  192. $this->assertSqlGeneration(
  193. 'SELECT u FROM Doctrine\Tests\Models\Forum\ForumUser u ORDER BY u.id desc',
  194. 'SELECT f0_.id AS id0, f0_.username AS username1 FROM forum_users f0_ ORDER BY f0_.id DESC'
  195. );
  196. }
  197. public function testSupportsSelectDistinct()
  198. {
  199. $this->assertSqlGeneration(
  200. 'SELECT DISTINCT u.name FROM Doctrine\Tests\Models\CMS\CmsUser u',
  201. 'SELECT DISTINCT c0_.name AS name0 FROM cms_users c0_'
  202. );
  203. }
  204. public function testSupportsAggregateFunctionInSelectedFields()
  205. {
  206. $this->assertSqlGeneration(
  207. 'SELECT COUNT(u.id) FROM Doctrine\Tests\Models\CMS\CmsUser u GROUP BY u.id',
  208. 'SELECT COUNT(c0_.id) AS sclr0 FROM cms_users c0_ GROUP BY c0_.id'
  209. );
  210. }
  211. public function testSupportsAggregateFunctionWithSimpleArithmetic()
  212. {
  213. $this->assertSqlGeneration(
  214. 'SELECT MAX(u.id + 4) * 2 FROM Doctrine\Tests\Models\CMS\CmsUser u',
  215. 'SELECT MAX(c0_.id + 4) * 2 AS sclr0 FROM cms_users c0_'
  216. );
  217. }
  218. public function testSupportsWhereClauseWithPositionalParameter()
  219. {
  220. $this->assertSqlGeneration(
  221. 'select u from Doctrine\Tests\Models\Forum\ForumUser u where u.id = ?1',
  222. 'SELECT f0_.id AS id0, f0_.username AS username1 FROM forum_users f0_ WHERE f0_.id = ?'
  223. );
  224. }
  225. public function testSupportsWhereClauseWithNamedParameter()
  226. {
  227. $this->assertSqlGeneration(
  228. 'select u from Doctrine\Tests\Models\Forum\ForumUser u where u.username = :name',
  229. 'SELECT f0_.id AS id0, f0_.username AS username1 FROM forum_users f0_ WHERE f0_.username = ?'
  230. );
  231. }
  232. public function testSupportsWhereAndClauseWithNamedParameters()
  233. {
  234. $this->assertSqlGeneration(
  235. 'select u from Doctrine\Tests\Models\Forum\ForumUser u where u.username = :name and u.username = :name2',
  236. 'SELECT f0_.id AS id0, f0_.username AS username1 FROM forum_users f0_ WHERE f0_.username = ? AND f0_.username = ?'
  237. );
  238. }
  239. public function testSupportsCombinedWhereClauseWithNamedParameter()
  240. {
  241. $this->assertSqlGeneration(
  242. 'select u from Doctrine\Tests\Models\Forum\ForumUser u where (u.username = :name OR u.username = :name2) AND u.id = :id',
  243. 'SELECT f0_.id AS id0, f0_.username AS username1 FROM forum_users f0_ WHERE (f0_.username = ? OR f0_.username = ?) AND f0_.id = ?'
  244. );
  245. }
  246. public function testSupportsAggregateFunctionInASelectDistinct()
  247. {
  248. $this->assertSqlGeneration(
  249. 'SELECT COUNT(DISTINCT u.name) FROM Doctrine\Tests\Models\CMS\CmsUser u',
  250. 'SELECT COUNT(DISTINCT c0_.name) AS sclr0 FROM cms_users c0_'
  251. );
  252. }
  253. // Ticket #668
  254. public function testSupportsASqlKeywordInAStringLiteralParam()
  255. {
  256. $this->assertSqlGeneration(
  257. "SELECT u.name FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.name LIKE '%foo OR bar%'",
  258. "SELECT c0_.name AS name0 FROM cms_users c0_ WHERE c0_.name LIKE '%foo OR bar%'"
  259. );
  260. }
  261. public function testSupportsArithmeticExpressionsInWherePart()
  262. {
  263. $this->assertSqlGeneration(
  264. 'SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE ((u.id + 5000) * u.id + 3) < 10000000',
  265. 'SELECT c0_.id AS id0, c0_.status AS status1, c0_.username AS username2, c0_.name AS name3 FROM cms_users c0_ WHERE (c0_.id + 5000) * c0_.id + 3 < 10000000'
  266. );
  267. }
  268. public function testSupportsMultipleEntitiesInFromClause()
  269. {
  270. $this->assertSqlGeneration(
  271. 'SELECT u, a FROM Doctrine\Tests\Models\CMS\CmsUser u, Doctrine\Tests\Models\CMS\CmsArticle a JOIN a.user u2 WHERE u.id = u2.id',
  272. 'SELECT c0_.id AS id0, c0_.status AS status1, c0_.username AS username2, c0_.name AS name3, c1_.id AS id4, c1_.topic AS topic5, c1_.text AS text6, c1_.version AS version7 FROM cms_users c0_, cms_articles c1_ INNER JOIN cms_users c2_ ON c1_.user_id = c2_.id WHERE c0_.id = c2_.id'
  273. );
  274. }
  275. public function testSupportsMultipleEntitiesInFromClauseUsingPathExpression()
  276. {
  277. $this->assertSqlGeneration(
  278. 'SELECT u, a FROM Doctrine\Tests\Models\CMS\CmsUser u, Doctrine\Tests\Models\CMS\CmsArticle a WHERE u.id = a.user',
  279. 'SELECT c0_.id AS id0, c0_.status AS status1, c0_.username AS username2, c0_.name AS name3, c1_.id AS id4, c1_.topic AS topic5, c1_.text AS text6, c1_.version AS version7 FROM cms_users c0_, cms_articles c1_ WHERE c0_.id = c1_.user_id'
  280. );
  281. }
  282. public function testSupportsPlainJoinWithoutClause()
  283. {
  284. $this->assertSqlGeneration(
  285. 'SELECT u.id, a.id from Doctrine\Tests\Models\CMS\CmsUser u LEFT JOIN u.articles a',
  286. 'SELECT c0_.id AS id0, c1_.id AS id1 FROM cms_users c0_ LEFT JOIN cms_articles c1_ ON c0_.id = c1_.user_id'
  287. );
  288. $this->assertSqlGeneration(
  289. 'SELECT u.id, a.id from Doctrine\Tests\Models\CMS\CmsUser u JOIN u.articles a',
  290. 'SELECT c0_.id AS id0, c1_.id AS id1 FROM cms_users c0_ INNER JOIN cms_articles c1_ ON c0_.id = c1_.user_id'
  291. );
  292. }
  293. /**
  294. * @group DDC-135
  295. */
  296. public function testSupportsJoinAndWithClauseRestriction()
  297. {
  298. $this->assertSqlGeneration(
  299. "SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u LEFT JOIN u.articles a WITH a.topic LIKE '%foo%'",
  300. "SELECT c0_.id AS id0, c0_.status AS status1, c0_.username AS username2, c0_.name AS name3 FROM cms_users c0_ LEFT JOIN cms_articles c1_ ON c0_.id = c1_.user_id AND (c1_.topic LIKE '%foo%')"
  301. );
  302. $this->assertSqlGeneration(
  303. "SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u INNER JOIN u.articles a WITH a.topic LIKE '%foo%'",
  304. "SELECT c0_.id AS id0, c0_.status AS status1, c0_.username AS username2, c0_.name AS name3 FROM cms_users c0_ INNER JOIN cms_articles c1_ ON c0_.id = c1_.user_id AND (c1_.topic LIKE '%foo%')"
  305. );
  306. }
  307. /**
  308. * @group DDC-135
  309. * @group DDC-177
  310. */
  311. public function testJoinOnClause_NotYetSupported_ThrowsException()
  312. {
  313. $this->setExpectedException('Doctrine\ORM\Query\QueryException');
  314. $sql = $this->_em->createQuery(
  315. "SELECT u, a FROM Doctrine\Tests\Models\CMS\CmsUser u LEFT JOIN u.articles a ON a.topic LIKE '%foo%'"
  316. )->getSql();
  317. }
  318. public function testSupportsMultipleJoins()
  319. {
  320. $this->assertSqlGeneration(
  321. 'SELECT u.id, a.id, p.phonenumber, c.id from Doctrine\Tests\Models\CMS\CmsUser u JOIN u.articles a JOIN u.phonenumbers p JOIN a.comments c',
  322. 'SELECT c0_.id AS id0, c1_.id AS id1, c2_.phonenumber AS phonenumber2, c3_.id AS id3 FROM cms_users c0_ INNER JOIN cms_articles c1_ ON c0_.id = c1_.user_id INNER JOIN cms_phonenumbers c2_ ON c0_.id = c2_.user_id INNER JOIN cms_comments c3_ ON c1_.id = c3_.article_id'
  323. );
  324. }
  325. public function testSupportsTrimFunction()
  326. {
  327. $this->assertSqlGeneration(
  328. "SELECT u.name FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE TRIM(TRAILING ' ' FROM u.name) = 'someone'",
  329. "SELECT c0_.name AS name0 FROM cms_users c0_ WHERE TRIM(TRAILING ' ' FROM c0_.name) = 'someone'"
  330. );
  331. }
  332. // Ticket 894
  333. public function testSupportsBetweenClauseWithPositionalParameters()
  334. {
  335. $this->assertSqlGeneration(
  336. "SELECT u.name FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.id BETWEEN ?1 AND ?2",
  337. "SELECT c0_.name AS name0 FROM cms_users c0_ WHERE c0_.id BETWEEN ? AND ?"
  338. );
  339. }
  340. /**
  341. * @group DDC-1802
  342. */
  343. public function testSupportsNotBetweenForSizeFunction()
  344. {
  345. $this->assertSqlGeneration(
  346. "SELECT m.name FROM Doctrine\Tests\Models\StockExchange\Market m WHERE SIZE(m.stocks) NOT BETWEEN ?1 AND ?2",
  347. "SELECT e0_.name AS name0 FROM exchange_markets e0_ WHERE (SELECT COUNT(*) FROM exchange_stocks e1_ WHERE e1_.market_id = e0_.id) NOT BETWEEN ? AND ?"
  348. );
  349. }
  350. public function testSupportsFunctionalExpressionsInWherePart()
  351. {
  352. $this->assertSqlGeneration(
  353. "SELECT u.name FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE TRIM(u.name) = 'someone'",
  354. // String quoting in the SQL usually depends on the database platform.
  355. // This test works with a mock connection which uses ' for string quoting.
  356. "SELECT c0_.name AS name0 FROM cms_users c0_ WHERE TRIM(c0_.name) = 'someone'"
  357. );
  358. }
  359. public function testSupportsInstanceOfExpressionsInWherePart()
  360. {
  361. $this->assertSqlGeneration(
  362. "SELECT u FROM Doctrine\Tests\Models\Company\CompanyPerson u WHERE u INSTANCE OF Doctrine\Tests\Models\Company\CompanyEmployee",
  363. "SELECT c0_.id AS id0, c0_.name AS name1, c0_.discr AS discr2 FROM company_persons c0_ WHERE c0_.discr IN ('employee')"
  364. );
  365. }
  366. public function testSupportsInstanceOfExpressionInWherePartWithMultipleValues()
  367. {
  368. $this->assertSqlGeneration(
  369. "SELECT u FROM Doctrine\Tests\Models\Company\CompanyPerson u WHERE u INSTANCE OF (Doctrine\Tests\Models\Company\CompanyEmployee, \Doctrine\Tests\Models\Company\CompanyManager)",
  370. "SELECT c0_.id AS id0, c0_.name AS name1, c0_.discr AS discr2 FROM company_persons c0_ WHERE c0_.discr IN ('employee', 'manager')"
  371. );
  372. }
  373. /**
  374. * @group DDC-1194
  375. */
  376. public function testSupportsInstanceOfExpressionsInWherePartPrefixedSlash()
  377. {
  378. $this->assertSqlGeneration(
  379. "SELECT u FROM Doctrine\Tests\Models\Company\CompanyPerson u WHERE u INSTANCE OF \Doctrine\Tests\Models\Company\CompanyEmployee",
  380. "SELECT c0_.id AS id0, c0_.name AS name1, c0_.discr AS discr2 FROM company_persons c0_ WHERE c0_.discr IN ('employee')"
  381. );
  382. }
  383. /**
  384. * @group DDC-1194
  385. */
  386. public function testSupportsInstanceOfExpressionsInWherePartWithUnrelatedClass()
  387. {
  388. $this->assertInvalidSqlGeneration(
  389. "SELECT u FROM Doctrine\Tests\Models\Company\CompanyPerson u WHERE u INSTANCE OF \Doctrine\Tests\Models\CMS\CmsUser",
  390. "Doctrine\ORM\Query\QueryException"
  391. );
  392. }
  393. public function testSupportsInstanceOfExpressionsInWherePartInDeeperLevel()
  394. {
  395. $this->assertSqlGeneration(
  396. "SELECT u FROM Doctrine\Tests\Models\Company\CompanyEmployee u WHERE u INSTANCE OF Doctrine\Tests\Models\Company\CompanyManager",
  397. "SELECT c0_.id AS id0, c0_.name AS name1, c1_.salary AS salary2, c1_.department AS department3, c1_.startDate AS startDate4, c0_.discr AS discr5 FROM company_employees c1_ INNER JOIN company_persons c0_ ON c1_.id = c0_.id WHERE c0_.discr IN ('manager')"
  398. );
  399. }
  400. public function testSupportsInstanceOfExpressionsInWherePartInDeepestLevel()
  401. {
  402. $this->assertSqlGeneration(
  403. "SELECT u FROM Doctrine\Tests\Models\Company\CompanyManager u WHERE u INSTANCE OF Doctrine\Tests\Models\Company\CompanyManager",
  404. "SELECT c0_.id AS id0, c0_.name AS name1, c1_.salary AS salary2, c1_.department AS department3, c1_.startDate AS startDate4, c2_.title AS title5, c0_.discr AS discr6 FROM company_managers c2_ INNER JOIN company_employees c1_ ON c2_.id = c1_.id INNER JOIN company_persons c0_ ON c2_.id = c0_.id WHERE c0_.discr IN ('manager')"
  405. );
  406. }
  407. public function testSupportsInstanceOfExpressionsUsingInputParameterInWherePart()
  408. {
  409. $this->assertSqlGeneration(
  410. "SELECT u FROM Doctrine\Tests\Models\Company\CompanyPerson u WHERE u INSTANCE OF ?1",
  411. "SELECT c0_.id AS id0, c0_.name AS name1, c0_.discr AS discr2 FROM company_persons c0_ WHERE c0_.discr IN (?)",
  412. array(), array(1 => $this->_em->getClassMetadata('Doctrine\Tests\Models\Company\CompanyEmployee'))
  413. );
  414. }
  415. // Ticket #973
  416. public function testSupportsSingleValuedInExpressionWithoutSpacesInWherePart()
  417. {
  418. $this->assertSqlGeneration(
  419. "SELECT u.name FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE IDENTITY(u.email) IN(46)",
  420. "SELECT c0_.name AS name0 FROM cms_users c0_ WHERE c0_.email_id IN (46)"
  421. );
  422. }
  423. public function testSupportsMultipleValuedInExpressionInWherePart()
  424. {
  425. $this->assertSqlGeneration(
  426. 'SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.id IN (1, 2)',
  427. 'SELECT c0_.id AS id0, c0_.status AS status1, c0_.username AS username2, c0_.name AS name3 FROM cms_users c0_ WHERE c0_.id IN (1, 2)'
  428. );
  429. }
  430. public function testSupportsNotInExpressionInWherePart()
  431. {
  432. $this->assertSqlGeneration(
  433. 'SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE :id NOT IN (1)',
  434. 'SELECT c0_.id AS id0, c0_.status AS status1, c0_.username AS username2, c0_.name AS name3 FROM cms_users c0_ WHERE ? NOT IN (1)'
  435. );
  436. }
  437. /**
  438. * @group DDC-1802
  439. */
  440. public function testSupportsNotInExpressionForModFunction()
  441. {
  442. $this->assertSqlGeneration(
  443. "SELECT u.name FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE MOD(u.id, 5) NOT IN(1,3,4)",
  444. "SELECT c0_.name AS name0 FROM cms_users c0_ WHERE MOD(c0_.id, 5) NOT IN (1, 3, 4)"
  445. );
  446. }
  447. public function testInExpressionWithSingleValuedAssociationPathExpressionInWherePart()
  448. {
  449. $this->assertSqlGeneration(
  450. 'SELECT u FROM Doctrine\Tests\Models\Forum\ForumUser u WHERE u.avatar IN (?1, ?2)',
  451. 'SELECT f0_.id AS id0, f0_.username AS username1 FROM forum_users f0_ WHERE f0_.avatar_id IN (?, ?)'
  452. );
  453. }
  454. public function testInvalidInExpressionWithSingleValuedAssociationPathExpressionOnInverseSide()
  455. {
  456. // We do not support SingleValuedAssociationPathExpression on inverse side
  457. $this->assertInvalidSqlGeneration(
  458. "SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.address IN (?1, ?2)",
  459. "Doctrine\ORM\Query\QueryException"
  460. );
  461. }
  462. public function testSupportsConcatFunctionForMysqlAndPostgresql()
  463. {
  464. $connMock = $this->_em->getConnection();
  465. $orgPlatform = $connMock->getDatabasePlatform();
  466. $connMock->setDatabasePlatform(new \Doctrine\DBAL\Platforms\MySqlPlatform);
  467. $this->assertSqlGeneration(
  468. "SELECT u.id FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE CONCAT(u.name, 's') = ?1",
  469. "SELECT c0_.id AS id0 FROM cms_users c0_ WHERE CONCAT(c0_.name, 's') = ?"
  470. );
  471. $this->assertSqlGeneration(
  472. "SELECT CONCAT(u.id, u.name) FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.id = ?1",
  473. "SELECT CONCAT(c0_.id, c0_.name) AS sclr0 FROM cms_users c0_ WHERE c0_.id = ?"
  474. );
  475. $connMock->setDatabasePlatform(new \Doctrine\DBAL\Platforms\PostgreSqlPlatform);
  476. $this->assertSqlGeneration(
  477. "SELECT u.id FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE CONCAT(u.name, 's') = ?1",
  478. "SELECT c0_.id AS id0 FROM cms_users c0_ WHERE c0_.name || 's' = ?"
  479. );
  480. $this->assertSqlGeneration(
  481. "SELECT CONCAT(u.id, u.name) FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.id = ?1",
  482. "SELECT c0_.id || c0_.name AS sclr0 FROM cms_users c0_ WHERE c0_.id = ?"
  483. );
  484. $connMock->setDatabasePlatform($orgPlatform);
  485. }
  486. public function testSupportsExistsExpressionInWherePartWithCorrelatedSubquery()
  487. {
  488. $this->assertSqlGeneration(
  489. 'SELECT u.id FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE EXISTS (SELECT p.phonenumber FROM Doctrine\Tests\Models\CMS\CmsPhonenumber p WHERE p.phonenumber = u.id)',
  490. 'SELECT c0_.id AS id0 FROM cms_users c0_ WHERE EXISTS (SELECT c1_.phonenumber FROM cms_phonenumbers c1_ WHERE c1_.phonenumber = c0_.id)'
  491. );
  492. }
  493. /**
  494. * @group DDC-593
  495. */
  496. public function testSubqueriesInComparisonExpression()
  497. {
  498. $this->assertSqlGeneration(
  499. 'SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE (u.id >= (SELECT u2.id FROM Doctrine\Tests\Models\CMS\CmsUser u2 WHERE u2.name = :name)) AND (u.id <= (SELECT u3.id FROM Doctrine\Tests\Models\CMS\CmsUser u3 WHERE u3.name = :name))',
  500. 'SELECT c0_.id AS id0, c0_.status AS status1, c0_.username AS username2, c0_.name AS name3 FROM cms_users c0_ WHERE (c0_.id >= (SELECT c1_.id FROM cms_users c1_ WHERE c1_.name = ?)) AND (c0_.id <= (SELECT c2_.id FROM cms_users c2_ WHERE c2_.name = ?))'
  501. );
  502. }
  503. public function testSupportsMemberOfExpressionOneToMany()
  504. {
  505. // "Get all users who have $phone as a phonenumber." (*cough* doesnt really make sense...)
  506. $q = $this->_em->createQuery('SELECT u.id FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE :param MEMBER OF u.phonenumbers');
  507. $q->setHint(Query::HINT_FORCE_PARTIAL_LOAD, true);
  508. $phone = new \Doctrine\Tests\Models\CMS\CmsPhonenumber;
  509. $phone->phonenumber = 101;
  510. $q->setParameter('param', $phone);
  511. $this->assertEquals(
  512. 'SELECT c0_.id AS id0 FROM cms_users c0_ WHERE EXISTS (SELECT 1 FROM cms_phonenumbers c1_ WHERE c0_.id = c1_.user_id AND c1_.phonenumber = ?)',
  513. $q->getSql()
  514. );
  515. }
  516. public function testSupportsMemberOfExpressionManyToMany()
  517. {
  518. // "Get all users who are members of $group."
  519. $q = $this->_em->createQuery('SELECT u.id FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE :param MEMBER OF u.groups');
  520. $q->setHint(Query::HINT_FORCE_PARTIAL_LOAD, true);
  521. $group = new \Doctrine\Tests\Models\CMS\CmsGroup;
  522. $group->id = 101;
  523. $q->setParameter('param', $group);
  524. $this->assertEquals(
  525. 'SELECT c0_.id AS id0 FROM cms_users c0_ WHERE EXISTS (SELECT 1 FROM cms_users_groups c1_ INNER JOIN cms_groups c2_ ON c1_.group_id = c2_.id WHERE c1_.user_id = c0_.id AND c2_.id = ?)',
  526. $q->getSql()
  527. );
  528. }
  529. public function testSupportsMemberOfExpressionSelfReferencing()
  530. {
  531. // "Get all persons who have $person as a friend."
  532. // Tough one: Many-many self-referencing ("friends") with class table inheritance
  533. $q = $this->_em->createQuery('SELECT p FROM Doctrine\Tests\Models\Company\CompanyPerson p WHERE :param MEMBER OF p.friends');
  534. $person = new \Doctrine\Tests\Models\Company\CompanyPerson;
  535. $this->_em->getClassMetadata(get_class($person))->setIdentifierValues($person, array('id' => 101));
  536. $q->setParameter('param', $person);
  537. $this->assertEquals(
  538. 'SELECT c0_.id AS id0, c0_.name AS name1, c1_.title AS title2, c2_.salary AS salary3, c2_.department AS department4, c2_.startDate AS startDate5, c0_.discr AS discr6, c0_.spouse_id AS spouse_id7, c1_.car_id AS car_id8 FROM company_persons c0_ LEFT JOIN company_managers c1_ ON c0_.id = c1_.id LEFT JOIN company_employees c2_ ON c0_.id = c2_.id WHERE EXISTS (SELECT 1 FROM company_persons_friends c3_ INNER JOIN company_persons c4_ ON c3_.friend_id = c4_.id WHERE c3_.person_id = c0_.id AND c4_.id = ?)',
  539. $q->getSql()
  540. );
  541. }
  542. public function testSupportsMemberOfWithSingleValuedAssociation()
  543. {
  544. // Impossible example, but it illustrates the purpose
  545. $q = $this->_em->createQuery('SELECT u.id FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.email MEMBER OF u.groups');
  546. $this->assertEquals(
  547. 'SELECT c0_.id AS id0 FROM cms_users c0_ WHERE EXISTS (SELECT 1 FROM cms_users_groups c1_ INNER JOIN cms_groups c2_ ON c1_.group_id = c2_.id WHERE c1_.user_id = c0_.id AND c2_.id = c0_.email_id)',
  548. $q->getSql()
  549. );
  550. }
  551. public function testSupportsMemberOfWithIdentificationVariable()
  552. {
  553. // Impossible example, but it illustrates the purpose
  554. $q = $this->_em->createQuery('SELECT u.id FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u MEMBER OF u.groups');
  555. $this->assertEquals(
  556. 'SELECT c0_.id AS id0 FROM cms_users c0_ WHERE EXISTS (SELECT 1 FROM cms_users_groups c1_ INNER JOIN cms_groups c2_ ON c1_.group_id = c2_.id WHERE c1_.user_id = c0_.id AND c2_.id = c0_.id)',
  557. $q->getSql()
  558. );
  559. }
  560. public function testSupportsCurrentDateFunction()
  561. {
  562. $q = $this->_em->createQuery('SELECT d.id FROM Doctrine\Tests\Models\Generic\DateTimeModel d WHERE d.datetime > current_date()');
  563. $q->setHint(Query::HINT_FORCE_PARTIAL_LOAD, true);
  564. $this->assertEquals('SELECT d0_.id AS id0 FROM date_time_model d0_ WHERE d0_.col_datetime > CURRENT_DATE', $q->getSql());
  565. }
  566. public function testSupportsCurrentTimeFunction()
  567. {
  568. $q = $this->_em->createQuery('SELECT d.id FROM Doctrine\Tests\Models\Generic\DateTimeModel d WHERE d.time > current_time()');
  569. $q->setHint(Query::HINT_FORCE_PARTIAL_LOAD, true);
  570. $this->assertEquals('SELECT d0_.id AS id0 FROM date_time_model d0_ WHERE d0_.col_time > CURRENT_TIME', $q->getSql());
  571. }
  572. public function testSupportsCurrentTimestampFunction()
  573. {
  574. $q = $this->_em->createQuery('SELECT d.id FROM Doctrine\Tests\Models\Generic\DateTimeModel d WHERE d.datetime > current_timestamp()');
  575. $q->setHint(Query::HINT_FORCE_PARTIAL_LOAD, true);
  576. $this->assertEquals('SELECT d0_.id AS id0 FROM date_time_model d0_ WHERE d0_.col_datetime > CURRENT_TIMESTAMP', $q->getSql());
  577. }
  578. public function testExistsExpressionInWhereCorrelatedSubqueryAssocCondition()
  579. {
  580. $this->assertSqlGeneration(
  581. // DQL
  582. // The result of this query consists of all employees whose spouses are also employees.
  583. 'SELECT DISTINCT emp FROM Doctrine\Tests\Models\CMS\CmsEmployee emp
  584. WHERE EXISTS (
  585. SELECT spouseEmp
  586. FROM Doctrine\Tests\Models\CMS\CmsEmployee spouseEmp
  587. WHERE spouseEmp = emp.spouse)',
  588. // SQL
  589. 'SELECT DISTINCT c0_.id AS id0, c0_.name AS name1 FROM cms_employees c0_'
  590. . ' WHERE EXISTS ('
  591. . 'SELECT c1_.id FROM cms_employees c1_ WHERE c1_.id = c0_.spouse_id'
  592. . ')'
  593. );
  594. }
  595. public function testExistsExpressionWithSimpleSelectReturningScalar()
  596. {
  597. $this->assertSqlGeneration(
  598. // DQL
  599. // The result of this query consists of all employees whose spouses are also employees.
  600. 'SELECT DISTINCT emp FROM Doctrine\Tests\Models\CMS\CmsEmployee emp
  601. WHERE EXISTS (
  602. SELECT 1
  603. FROM Doctrine\Tests\Models\CMS\CmsEmployee spouseEmp
  604. WHERE spouseEmp = emp.spouse)',
  605. // SQL
  606. 'SELECT DISTINCT c0_.id AS id0, c0_.name AS name1 FROM cms_employees c0_'
  607. . ' WHERE EXISTS ('
  608. . 'SELECT 1 AS sclr2 FROM cms_employees c1_ WHERE c1_.id = c0_.spouse_id'
  609. . ')'
  610. );
  611. }
  612. public function testLimitFromQueryClass()
  613. {
  614. $q = $this->_em
  615. ->createQuery('SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u')
  616. ->setMaxResults(10);
  617. $this->assertEquals('SELECT c0_.id AS id0, c0_.status AS status1, c0_.username AS username2, c0_.name AS name3, c0_.email_id AS email_id4 FROM cms_users c0_ LIMIT 10', $q->getSql());
  618. }
  619. public function testLimitAndOffsetFromQueryClass()
  620. {
  621. $q = $this->_em
  622. ->createQuery('SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u')
  623. ->setMaxResults(10)
  624. ->setFirstResult(0);
  625. $this->assertEquals('SELECT c0_.id AS id0, c0_.status AS status1, c0_.username AS username2, c0_.name AS name3, c0_.email_id AS email_id4 FROM cms_users c0_ LIMIT 10 OFFSET 0', $q->getSql());
  626. }
  627. public function testSizeFunction()
  628. {
  629. $this->assertSqlGeneration(
  630. "SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE SIZE(u.phonenumbers) > 1",
  631. "SELECT c0_.id AS id0, c0_.status AS status1, c0_.username AS username2, c0_.name AS name3 FROM cms_users c0_ WHERE (SELECT COUNT(*) FROM cms_phonenumbers c1_ WHERE c1_.user_id = c0_.id) > 1"
  632. );
  633. }
  634. public function testSizeFunctionSupportsManyToMany()
  635. {
  636. $this->assertSqlGeneration(
  637. "SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE SIZE(u.groups) > 1",
  638. "SELECT c0_.id AS id0, c0_.status AS status1, c0_.username AS username2, c0_.name AS name3 FROM cms_users c0_ WHERE (SELECT COUNT(*) FROM cms_users_groups c1_ WHERE c1_.user_id = c0_.id) > 1"
  639. );
  640. }
  641. public function testEmptyCollectionComparisonExpression()
  642. {
  643. $this->assertSqlGeneration(
  644. "SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.phonenumbers IS EMPTY",
  645. "SELECT c0_.id AS id0, c0_.status AS status1, c0_.username AS username2, c0_.name AS name3 FROM cms_users c0_ WHERE (SELECT COUNT(*) FROM cms_phonenumbers c1_ WHERE c1_.user_id = c0_.id) = 0"
  646. );
  647. $this->assertSqlGeneration(
  648. "SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.phonenumbers IS NOT EMPTY",
  649. "SELECT c0_.id AS id0, c0_.status AS status1, c0_.username AS username2, c0_.name AS name3 FROM cms_users c0_ WHERE (SELECT COUNT(*) FROM cms_phonenumbers c1_ WHERE c1_.user_id = c0_.id) > 0"
  650. );
  651. }
  652. public function testNestedExpressions()
  653. {
  654. $this->assertSqlGeneration(
  655. "select u from Doctrine\Tests\Models\CMS\CmsUser u where u.id > 10 and u.id < 42 and ((u.id * 2) > 5)",
  656. "SELECT c0_.id AS id0, c0_.status AS status1, c0_.username AS username2, c0_.name AS name3 FROM cms_users c0_ WHERE c0_.id > 10 AND c0_.id < 42 AND (c0_.id * 2 > 5)"
  657. );
  658. }
  659. public function testNestedExpressions2()
  660. {
  661. $this->assertSqlGeneration(
  662. "select u from Doctrine\Tests\Models\CMS\CmsUser u where (u.id > 10) and (u.id < 42 and ((u.id * 2) > 5)) or u.id <> 42",
  663. "SELECT c0_.id AS id0, c0_.status AS status1, c0_.username AS username2, c0_.name AS name3 FROM cms_users c0_ WHERE (c0_.id > 10) AND (c0_.id < 42 AND (c0_.id * 2 > 5)) OR c0_.id <> 42"
  664. );
  665. }
  666. public function testNestedExpressions3()
  667. {
  668. $this->assertSqlGeneration(
  669. "select u from Doctrine\Tests\Models\CMS\CmsUser u where (u.id > 10) and (u.id between 1 and 10 or u.id in (1, 2, 3, 4, 5))",
  670. "SELECT c0_.id AS id0, c0_.status AS status1, c0_.username AS username2, c0_.name AS name3 FROM cms_users c0_ WHERE (c0_.id > 10) AND (c0_.id BETWEEN 1 AND 10 OR c0_.id IN (1, 2, 3, 4, 5))"
  671. );
  672. }
  673. public function testOrderByCollectionAssociationSize()
  674. {
  675. $this->assertSqlGeneration(
  676. "select u, size(u.articles) as numArticles from Doctrine\Tests\Models\CMS\CmsUser u order by numArticles",
  677. "SELECT c0_.id AS id0, c0_.status AS status1, c0_.username AS username2, c0_.name AS name3, (SELECT COUNT(*) FROM cms_articles c1_ WHERE c1_.user_id = c0_.id) AS sclr4 FROM cms_users c0_ ORDER BY sclr4 ASC"
  678. );
  679. }
  680. public function testOrderBySupportsSingleValuedPathExpressionOwningSide()
  681. {
  682. $this->assertSqlGeneration(
  683. "select a from Doctrine\Tests\Models\CMS\CmsArticle a order by a.user",
  684. "SELECT c0_.id AS id0, c0_.topic AS topic1, c0_.text AS text2, c0_.version AS version3 FROM cms_articles c0_ ORDER BY c0_.user_id ASC"
  685. );
  686. }
  687. /**
  688. * @expectedException Doctrine\ORM\Query\QueryException
  689. */
  690. public function testOrderBySupportsSingleValuedPathExpressionInverseSide()
  691. {
  692. $q = $this->_em->createQuery("select u from Doctrine\Tests\Models\CMS\CmsUser u order by u.address");
  693. $q->getSQL();
  694. }
  695. public function testBooleanLiteralInWhereOnSqlite()
  696. {
  697. $oldPlat = $this->_em->getConnection()->getDatabasePlatform();
  698. $this->_em->getConnection()->setDatabasePlatform(new \Doctrine\DBAL\Platforms\SqlitePlatform);
  699. $this->assertSqlGeneration(
  700. "SELECT b FROM Doctrine\Tests\Models\Generic\BooleanModel b WHERE b.booleanField = true",
  701. "SELECT b0_.id AS id0, b0_.booleanField AS booleanField1 FROM boolean_model b0_ WHERE b0_.booleanField = 1"
  702. );
  703. $this->assertSqlGeneration(
  704. "SELECT b FROM Doctrine\Tests\Models\Generic\BooleanModel b WHERE b.booleanField = false",
  705. "SELECT b0_.id AS id0, b0_.booleanField AS booleanField1 FROM boolean_model b0_ WHERE b0_.booleanField = 0"
  706. );
  707. $this->_em->getConnection()->setDatabasePlatform($oldPlat);
  708. }
  709. public function testBooleanLiteralInWhereOnPostgres()
  710. {
  711. $oldPlat = $this->_em->getConnection()->getDatabasePlatform();
  712. $this->_em->getConnection()->setDatabasePlatform(new \Doctrine\DBAL\Platforms\PostgreSqlPlatform);
  713. $this->assertSqlGeneration(
  714. "SELECT b FROM Doctrine\Tests\Models\Generic\BooleanModel b WHERE b.booleanField = true",
  715. "SELECT b0_.id AS id0, b0_.booleanField AS booleanfield1 FROM boolean_model b0_ WHERE b0_.booleanField = true"
  716. );
  717. $this->assertSqlGeneration(
  718. "SELECT b FROM Doctrine\Tests\Models\Generic\BooleanModel b WHERE b.booleanField = false",
  719. "SELECT b0_.id AS id0, b0_.booleanField AS booleanfield1 FROM boolean_model b0_ WHERE b0_.booleanField = false"
  720. );
  721. $this->_em->getConnection()->setDatabasePlatform($oldPlat);
  722. }
  723. public function testSingleValuedAssociationFieldInWhere()
  724. {
  725. $this->assertSqlGeneration(
  726. "SELECT p FROM Doctrine\Tests\Models\CMS\CmsPhonenumber p WHERE p.user = ?1",
  727. "SELECT c0_.phonenumber AS phonenumber0 FROM cms_phonenumbers c0_ WHERE c0_.user_id = ?"
  728. );
  729. }
  730. public function testSingleValuedAssociationNullCheckOnOwningSide()
  731. {
  732. $this->assertSqlGeneration(
  733. "SELECT a FROM Doctrine\Tests\Models\CMS\CmsAddress a WHERE a.user IS NULL",
  734. "SELECT c0_.id AS id0, c0_.country AS country1, c0_.zip AS zip2, c0_.city AS city3 FROM cms_addresses c0_ WHERE c0_.user_id IS NULL"
  735. );
  736. }
  737. // Null check on inverse side has to happen through explicit JOIN.
  738. // "SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.address IS NULL"
  739. // where the CmsUser is the inverse side is not supported.
  740. public function testSingleValuedAssociationNullCheckOnInverseSide()
  741. {
  742. $this->assertSqlGeneration(
  743. "SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u LEFT JOIN u.address a WHERE a.id IS NULL",
  744. "SELECT c0_.id AS id0, c0_.status AS status1, c0_.username AS username2, c0_.name AS name3 FROM cms_users c0_ LEFT JOIN cms_addresses c1_ ON c0_.id = c1_.user_id WHERE c1_.id IS NULL"
  745. );
  746. }
  747. /**
  748. * @group DDC-339
  749. * @group DDC-1572
  750. */
  751. public function testStringFunctionLikeExpression()
  752. {
  753. $this->assertSqlGeneration(
  754. "SELECT u.name FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE LOWER(u.name) LIKE '%foo OR bar%'",
  755. "SELECT c0_.name AS name0 FROM cms_users c0_ WHERE LOWER(c0_.name) LIKE '%foo OR bar%'"
  756. );
  757. $this->assertSqlGeneration(
  758. "SELECT u.name FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE LOWER(u.name) LIKE :str",
  759. "SELECT c0_.name AS name0 FROM cms_users c0_ WHERE LOWER(c0_.name) LIKE ?"
  760. );
  761. $this->assertSqlGeneration(
  762. "SELECT u.name FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE CONCAT(UPPER(u.name), '_moo') LIKE :str",
  763. "SELECT c0_.name AS name0 FROM cms_users c0_ WHERE UPPER(c0_.name) || '_moo' LIKE ?"
  764. );
  765. // DDC-1572
  766. $this->assertSqlGeneration(
  767. "SELECT u.name FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE UPPER(u.name) LIKE UPPER(:str)",
  768. "SELECT c0_.name AS name0 FROM cms_users c0_ WHERE UPPER(c0_.name) LIKE UPPER(?)"
  769. );
  770. $this->assertSqlGeneration(
  771. "SELECT u.name FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE UPPER(LOWER(u.name)) LIKE UPPER(LOWER(:str))",
  772. "SELECT c0_.name AS name0 FROM cms_users c0_ WHERE UPPER(LOWER(c0_.name)) LIKE UPPER(LOWER(?))"
  773. );
  774. $this->assertSqlGeneration(
  775. "SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u LEFT JOIN u.articles a WITH a.topic LIKE u.name",
  776. "SELECT c0_.id AS id0, c0_.status AS status1, c0_.username AS username2, c0_.name AS name3 FROM cms_users c0_ LEFT JOIN cms_articles c1_ ON c0_.id = c1_.user_id AND (c1_.topic LIKE c0_.name)"
  777. );
  778. }
  779. /**
  780. * @group DDC-1802
  781. */
  782. public function testStringFunctionNotLikeExpression()
  783. {
  784. $this->assertSqlGeneration(
  785. "SELECT u.name FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE LOWER(u.name) NOT LIKE '%foo OR bar%'",
  786. "SELECT c0_.name AS name0 FROM cms_users c0_ WHERE LOWER(c0_.name) NOT LIKE '%foo OR bar%'"
  787. );
  788. $this->assertSqlGeneration(
  789. "SELECT u.name FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE UPPER(LOWER(u.name)) NOT LIKE UPPER(LOWER(:str))",
  790. "SELECT c0_.name AS name0 FROM cms_users c0_ WHERE UPPER(LOWER(c0_.name)) NOT LIKE UPPER(LOWER(?))"
  791. );
  792. $this->assertSqlGeneration(
  793. "SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u LEFT JOIN u.articles a WITH a.topic NOT LIKE u.name",
  794. "SELECT c0_.id AS id0, c0_.status AS status1, c0_.username AS username2, c0_.name AS name3 FROM cms_users c0_ LEFT JOIN cms_articles c1_ ON c0_.id = c1_.user_id AND (c1_.topic NOT LIKE c0_.name)"
  795. );
  796. }
  797. /**
  798. * @group DDC-338
  799. */
  800. public function testOrderedCollectionFetchJoined()
  801. {
  802. $this->assertSqlGeneration(
  803. "SELECT r, l FROM Doctrine\Tests\Models\Routing\RoutingRoute r JOIN r.legs l",
  804. "SELECT r0_.id AS id0, r1_.id AS id1, r1_.departureDate AS departureDate2, r1_.arrivalDate AS arrivalDate3 FROM RoutingRoute r0_ INNER JOIN RoutingRouteLegs r2_ ON r0_.id = r2_.route_id INNER JOIN RoutingLeg r1_ ON r1_.id = r2_.leg_id ".
  805. "ORDER BY r1_.departureDate ASC"
  806. );
  807. }
  808. public function testSubselectInSelect()
  809. {
  810. $this->assertSqlGeneration(
  811. "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'",
  812. "SELECT c0_.name AS name0, (SELECT COUNT(c1_.phonenumber) AS dctrn__1 FROM cms_phonenumbers c1_ WHERE c1_.phonenumber = 1234) AS sclr1 FROM cms_users c0_ WHERE c0_.name = 'jon'"
  813. );
  814. }
  815. /**
  816. * @group locking
  817. * @group DDC-178
  818. */
  819. public function testPessimisticWriteLockQueryHint()
  820. {
  821. if ($this->_em->getConnection()->getDatabasePlatform() instanceof \Doctrine\DBAL\Platforms\SqlitePlatform) {
  822. $this->markTestSkipped('SqLite does not support Row locking at all.');
  823. }
  824. $this->assertSqlGeneration(
  825. "SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.username = 'gblanco'",
  826. "SELECT c0_.id AS id0, c0_.status AS status1, c0_.username AS username2, c0_.name AS name3 ".
  827. "FROM cms_users c0_ WHERE c0_.username = 'gblanco' FOR UPDATE",
  828. array(Query::HINT_LOCK_MODE => \Doctrine\DBAL\LockMode::PESSIMISTIC_WRITE)
  829. );
  830. }
  831. /**
  832. * @group locking
  833. * @group DDC-178
  834. */
  835. public function testPessimisticReadLockQueryHintPostgreSql()
  836. {
  837. $this->_em->getConnection()->setDatabasePlatform(new \Doctrine\DBAL\Platforms\PostgreSqlPlatform);
  838. $this->assertSqlGeneration(
  839. "SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.username = 'gblanco'",
  840. "SELECT c0_.id AS id0, c0_.status AS status1, c0_.username AS username2, c0_.name AS name3 ".
  841. "FROM cms_users c0_ WHERE c0_.username = 'gblanco' FOR SHARE",
  842. array(Query::HINT_LOCK_MODE => \Doctrine\DBAL\LockMode::PESSIMISTIC_READ)
  843. );
  844. }
  845. /**
  846. * @group DDC-1693
  847. * @group locking
  848. */
  849. public function testLockModeNoneQueryHint()
  850. {
  851. $this->assertSqlGeneration(
  852. "SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.username = 'gblanco'",
  853. "SELECT c0_.id AS id0, c0_.status AS status1, c0_.username AS username2, c0_.name AS name3 ".
  854. "FROM cms_users c0_ WHERE c0_.username = 'gblanco'",
  855. array(Query::HINT_LOCK_MODE => \Doctrine\DBAL\LockMode::NONE)
  856. );
  857. }
  858. /**
  859. * @group DDC-430
  860. */
  861. public function testSupportSelectWithMoreThan10InputParameters()
  862. {
  863. $this->assertSqlGeneration(
  864. "SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.id = ?1 OR u.id = ?2 OR u.id = ?3 OR u.id = ?4 OR u.id = ?5 OR u.id = ?6 OR u.id = ?7 OR u.id = ?8 OR u.id = ?9 OR u.id = ?10 OR u.id = ?11",
  865. "SELECT c0_.id AS id0, c0_.status AS status1, c0_.username AS username2, c0_.name AS name3 FROM cms_users c0_ WHERE c0_.id = ? OR c0_.id = ? OR c0_.id = ? OR c0_.id = ? OR c0_.id = ? OR c0_.id = ? OR c0_.id = ? OR c0_.id = ? OR c0_.id = ? OR c0_.id = ? OR c0_.id = ?"
  866. );
  867. }
  868. /**
  869. * @group locking
  870. * @group DDC-178
  871. */
  872. public function testPessimisticReadLockQueryHintMySql()
  873. {
  874. $this->_em->getConnection()->setDatabasePlatform(new \Doctrine\DBAL\Platforms\MySqlPlatform);
  875. $this->assertSqlGeneration(
  876. "SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.username = 'gblanco'",
  877. "SELECT c0_.id AS id0, c0_.status AS status1, c0_.username AS username2, c0_.name AS name3 ".
  878. "FROM cms_users c0_ WHERE c0_.username = 'gblanco' LOCK IN SHARE MODE",
  879. array(Query::HINT_LOCK_MODE => \Doctrine\DBAL\LockMode::PESSIMISTIC_READ)
  880. );
  881. }
  882. /**
  883. * @group locking
  884. * @group DDC-178
  885. */
  886. public function testPessimisticReadLockQueryHintOracle()
  887. {
  888. $this->_em->getConnection()->setDatabasePlatform(new \Doctrine\DBAL\Platforms\OraclePlatform);
  889. $this->assertSqlGeneration(
  890. "SELECT u FROM Doctrine\Tests\Models\CMS\CmsUser u WHERE u.username = 'gblanco'",
  891. "SELECT c0_.id AS ID0, c0_.status AS STATUS1, c0_.username AS USERNAME2, c0_.name AS NAME3 ".
  892. "FROM cms_users c0_ WHERE c0_.username = 'gblanco' FOR UPDATE",
  893. array(Query::HINT_LOCK_MODE => \Doctrine\DBAL\LockMode::PESSIMISTIC_READ)
  894. );
  895. }
  896. /**
  897. * @group DDC-431
  898. */
  899. public function testSupportToCustomDQLFunctions()
  900. {
  901. $config = $this->_em->getConfiguration();
  902. $config->addCustomNumericFunction('MYABS', 'Doctrine\Tests\ORM\Query\MyAbsFunction');
  903. $this->assertSqlGeneration(
  904. 'SELECT MYABS(p.phonenumber) FROM Doctrine\Tests\Models\CMS\CmsPhonenumber p',
  905. 'SELECT ABS(c0_.phonenumber) AS sclr0 FROM cms_phonenumbers c0_'
  906. );
  907. $config->setCustomNumericFunctions(array());
  908. }
  909. /**
  910. * @group DDC-826
  911. */
  912. public function testMappedSuperclassAssociationJoin()
  913. {
  914. $this->assertSqlGeneration(
  915. 'SELECT f FROM Doctrine\Tests\Models\DirectoryTree\File f JOIN f.parentDirectory d WHERE f.id = ?1',
  916. 'SELECT f0_.id AS id0, f0_.extension AS extension1, f0_.name AS name2 FROM "file" f0_ INNER JOIN Directory d1_ ON f0_.parentDirectory_id = d1_.id WHERE f0_.id = ?'
  917. );
  918. }
  919. /**
  920. * @group DDC-1053
  921. */
  922. public function testGroupBy()
  923. {
  924. $this->assertSqlGeneration(
  925. 'SELECT g.id, count(u.id) FROM Doctrine\Tests\Models\CMS\CmsGroup g JOIN g.users u GROUP BY g.id',
  926. 'SELECT c0_.id AS id0, count(c1_.id) AS sclr1 FROM cms_groups c0_ INNER JOIN cms_users_groups c2_ ON c0_.id = c2_.group_id INNER JOIN cms_users c1_ ON c1_.id = c2_.user_id GROUP BY c0_.id'
  927. );
  928. }
  929. /**
  930. * @group DDC-1053
  931. */
  932. public function testGroupByIdentificationVariable()
  933. {
  934. $this->assertSqlGeneration(
  935. 'SELECT g, count(u.id) FROM Doctrine\Tests\Models\CMS\CmsGroup g JOIN g.users u GROUP BY g',
  936. 'SELECT c0_.id AS id0, c0_.name AS name1, count(c1_.id) AS sclr2 FROM cms_groups c0_ INNER JOIN cms_users_groups c2_ ON c0_.id = c2_.group_id INNER JOIN cms_users c1_ ON c1_.id = c2_.user_…

Large files files are truncated, but you can click here to view the full file