PageRenderTime 52ms CodeModel.GetById 17ms RepoModel.GetById 1ms app.codeStats 0ms

/test/testsuite/generator/builder/om/QueryBuilderTest.php

https://github.com/nextbigsound/propel-orm
PHP | 920 lines | 735 code | 153 blank | 32 comment | 3 complexity | 1c05c27caac911509c8ef97d3b4cd551 MD5 | raw file
  1. <?php
  2. /**
  3. * This file is part of the Propel package.
  4. * For the full copyright and license information, please view the LICENSE
  5. * file that was distributed with this source code.
  6. *
  7. * @license MIT License
  8. */
  9. require_once 'tools/helpers/bookstore/BookstoreTestBase.php';
  10. require_once 'tools/helpers/bookstore/BookstoreDataPopulator.php';
  11. /**
  12. * Test class for QueryBuilder.
  13. *
  14. * @author François Zaninotto
  15. * @version $Id: QueryBuilderTest.php 1347 2009-12-03 21:06:36Z francois $
  16. * @package generator.builder.om
  17. */
  18. class QueryBuilderTest extends BookstoreTestBase
  19. {
  20. public function testExtends()
  21. {
  22. $q = new BookQuery();
  23. $this->assertTrue($q instanceof ModelCriteria, 'Model query extends ModelCriteria');
  24. }
  25. public function testConstructor()
  26. {
  27. $query = new BookQuery();
  28. $this->assertEquals($query->getDbName(), 'bookstore', 'Constructor sets dabatase name');
  29. $this->assertEquals($query->getModelName(), 'Book', 'Constructor sets model name');
  30. }
  31. public function testCreate()
  32. {
  33. $query = BookQuery::create();
  34. $this->assertTrue($query instanceof BookQuery, 'create() returns an object of its class');
  35. $this->assertEquals($query->getDbName(), 'bookstore', 'create() sets dabatase name');
  36. $this->assertEquals($query->getModelName(), 'Book', 'create() sets model name');
  37. $query = BookQuery::create('foo');
  38. $this->assertTrue($query instanceof BookQuery, 'create() returns an object of its class');
  39. $this->assertEquals($query->getDbName(), 'bookstore', 'create() sets dabatase name');
  40. $this->assertEquals($query->getModelName(), 'Book', 'create() sets model name');
  41. $this->assertEquals($query->getModelAlias(), 'foo', 'create() can set the model alias');
  42. }
  43. public function testCreateCustom()
  44. {
  45. // see the myBookQuery class definition at the end of this file
  46. $query = myCustomBookQuery::create();
  47. $this->assertTrue($query instanceof myCustomBookQuery, 'create() returns an object of its class');
  48. $this->assertTrue($query instanceof BookQuery, 'create() returns an object of its class');
  49. $this->assertEquals($query->getDbName(), 'bookstore', 'create() sets dabatase name');
  50. $this->assertEquals($query->getModelName(), 'Book', 'create() sets model name');
  51. $query = myCustomBookQuery::create('foo');
  52. $this->assertTrue($query instanceof myCustomBookQuery, 'create() returns an object of its class');
  53. $this->assertEquals($query->getDbName(), 'bookstore', 'create() sets dabatase name');
  54. $this->assertEquals($query->getModelName(), 'Book', 'create() sets model name');
  55. $this->assertEquals($query->getModelAlias(), 'foo', 'create() can set the model alias');
  56. }
  57. public function testBasePreSelect()
  58. {
  59. $method = new ReflectionMethod('Table2Query', 'basePreSelect');
  60. $this->assertEquals('ModelCriteria', $method->getDeclaringClass()->getName(), 'BaseQuery does not override basePreSelect() by default');
  61. $method = new ReflectionMethod('Table3Query', 'basePreSelect');
  62. $this->assertEquals('BaseTable3Query', $method->getDeclaringClass()->getName(), 'BaseQuery overrides basePreSelect() when a behavior is registered');
  63. }
  64. public function testBasePreDelete()
  65. {
  66. $method = new ReflectionMethod('Table2Query', 'basePreDelete');
  67. $this->assertEquals('ModelCriteria', $method->getDeclaringClass()->getName(), 'BaseQuery does not override basePreDelete() by default');
  68. $method = new ReflectionMethod('Table3Query', 'basePreDelete');
  69. $this->assertEquals('BaseTable3Query', $method->getDeclaringClass()->getName(), 'BaseQuery overrides basePreDelete() when a behavior is registered');
  70. }
  71. public function testBasePostDelete()
  72. {
  73. $method = new ReflectionMethod('Table2Query', 'basePostDelete');
  74. $this->assertEquals('ModelCriteria', $method->getDeclaringClass()->getName(), 'BaseQuery does not override basePostDelete() by default');
  75. $method = new ReflectionMethod('Table3Query', 'basePostDelete');
  76. $this->assertEquals('BaseTable3Query', $method->getDeclaringClass()->getName(), 'BaseQuery overrides basePostDelete() when a behavior is registered');
  77. }
  78. public function testBasePreUpdate()
  79. {
  80. $method = new ReflectionMethod('Table2Query', 'basePreUpdate');
  81. $this->assertEquals('ModelCriteria', $method->getDeclaringClass()->getName(), 'BaseQuery does not override basePreUpdate() by default');
  82. $method = new ReflectionMethod('Table3Query', 'basePreUpdate');
  83. $this->assertEquals('BaseTable3Query', $method->getDeclaringClass()->getName(), 'BaseQuery overrides basePreUpdate() when a behavior is registered');
  84. }
  85. public function testBasePostUpdate()
  86. {
  87. $method = new ReflectionMethod('Table2Query', 'basePostUpdate');
  88. $this->assertEquals('ModelCriteria', $method->getDeclaringClass()->getName(), 'BaseQuery does not override basePostUpdate() by default');
  89. $method = new ReflectionMethod('Table3Query', 'basePostUpdate');
  90. $this->assertEquals('BaseTable3Query', $method->getDeclaringClass()->getName(), 'BaseQuery overrides basePostUpdate() when a behavior is registered');
  91. }
  92. public function testQuery()
  93. {
  94. BookstoreDataPopulator::depopulate();
  95. BookstoreDataPopulator::populate();
  96. $q = new BookQuery();
  97. $book = $q
  98. ->setModelAlias('b')
  99. ->where('b.Title like ?', 'Don%')
  100. ->orderBy('b.ISBN', 'desc')
  101. ->findOne();
  102. $this->assertTrue($book instanceof Book);
  103. $this->assertEquals('Don Juan', $book->getTitle());
  104. }
  105. public function testFindPk()
  106. {
  107. $method = new ReflectionMethod('Table4Query', 'findPk');
  108. $this->assertEquals('BaseTable4Query', $method->getDeclaringClass()->getName(), 'BaseQuery overrides findPk()');
  109. }
  110. public function testFindPkSimpleKey()
  111. {
  112. BookstoreDataPopulator::depopulate();
  113. BookstoreDataPopulator::populate();
  114. BookPeer::clearInstancePool();
  115. $con = Propel::getConnection('bookstore');
  116. // prepare the test data
  117. $c = new ModelCriteria('bookstore', 'Book');
  118. $c->orderBy('Book.Id', 'desc');
  119. $testBook = $c->findOne();
  120. $count = $con->getQueryCount();
  121. BookPeer::clearInstancePool();
  122. $q = new BookQuery();
  123. $book = $q->findPk($testBook->getId());
  124. $this->assertEquals($testBook, $book, 'BaseQuery overrides findPk() to make it faster');
  125. $this->assertEquals($count+1, $con->getQueryCount(), 'findPk() issues a database query when instance pool is empty');
  126. $q = new BookQuery();
  127. $book = $q->findPk($testBook->getId());
  128. $this->assertEquals($testBook, $book, 'BaseQuery overrides findPk() to make it faster');
  129. $this->assertEquals($count+1, $con->getQueryCount(), 'findPk() does not issue a database query when instance is in pool');
  130. }
  131. public function testFindPkCompositeKey()
  132. {
  133. BookstoreDataPopulator::depopulate();
  134. BookstoreDataPopulator::populate();
  135. // save all books to make sure related objects are also saved - BookstoreDataPopulator keeps some unsaved
  136. $c = new ModelCriteria('bookstore', 'Book');
  137. $books = $c->find();
  138. foreach ($books as $book) {
  139. $book->save();
  140. }
  141. BookPeer::clearInstancePool();
  142. // retrieve the test data
  143. $c = new ModelCriteria('bookstore', 'BookListRel');
  144. $bookListRelTest = $c->findOne();
  145. $pk = $bookListRelTest->getPrimaryKey();
  146. $q = new BookListRelQuery();
  147. $bookListRel = $q->findPk($pk);
  148. $this->assertEquals($bookListRelTest, $bookListRel, 'BaseQuery overrides findPk() for composite primary keysto make it faster');
  149. }
  150. public function testFindPks()
  151. {
  152. $method = new ReflectionMethod('Table4Query', 'findPks');
  153. $this->assertEquals('BaseTable4Query', $method->getDeclaringClass()->getName(), 'BaseQuery overrides findPks()');
  154. }
  155. public function testFindPksSimpleKey()
  156. {
  157. BookstoreDataPopulator::depopulate();
  158. BookstoreDataPopulator::populate();
  159. BookPeer::clearInstancePool();
  160. // prepare the test data
  161. $c = new ModelCriteria('bookstore', 'Book');
  162. $c->orderBy('Book.Id', 'desc');
  163. $testBooks = $c->find();
  164. $testBook1 = $testBooks->pop();
  165. $testBook2 = $testBooks->pop();
  166. $q = new BookQuery();
  167. $books = $q->findPks(array($testBook1->getId(), $testBook2->getId()));
  168. $this->assertEquals(array($testBook1, $testBook2), $books->getData(), 'BaseQuery overrides findPks() to make it faster');
  169. }
  170. public function testFindPksCompositeKey()
  171. {
  172. BookstoreDataPopulator::depopulate();
  173. BookstoreDataPopulator::populate();
  174. // save all books to make sure related objects are also saved - BookstoreDataPopulator keeps some unsaved
  175. $c = new ModelCriteria('bookstore', 'Book');
  176. $books = $c->find();
  177. foreach ($books as $book) {
  178. $book->save();
  179. }
  180. BookPeer::clearInstancePool();
  181. // retrieve the test data
  182. $c = new ModelCriteria('bookstore', 'BookListRel');
  183. $bookListRelTest = $c->find();
  184. $search = array();
  185. foreach ($bookListRelTest as $obj) {
  186. $search[]= $obj->getPrimaryKey();
  187. }
  188. $q = new BookListRelQuery();
  189. $objs = $q->findPks($search);
  190. $this->assertEquals($bookListRelTest, $objs, 'BaseQuery overrides findPks() for composite primary keys to make it work');
  191. }
  192. public function testFilterBy()
  193. {
  194. foreach (BookPeer::getFieldNames(BasePeer::TYPE_PHPNAME) as $colName) {
  195. $filterMethod = 'filterBy' . $colName;
  196. $this->assertTrue(method_exists('BookQuery', $filterMethod), 'QueryBuilder adds filterByColumn() methods for every column');
  197. $q = BookQuery::create()->$filterMethod(1);
  198. $this->assertTrue($q instanceof BookQuery, 'filterByColumn() returns the current query instance');
  199. }
  200. }
  201. public function testFilterByPrimaryKeySimpleKey()
  202. {
  203. $q = BookQuery::create()->filterByPrimaryKey(12);
  204. $q1 = BookQuery::create()->add(BookPeer::ID, 12, Criteria::EQUAL);
  205. $this->assertEquals($q1, $q, 'filterByPrimaryKey() translates to a Criteria::EQUAL in the PK column');
  206. $q = BookQuery::create()->setModelAlias('b', true)->filterByPrimaryKey(12);
  207. $q1 = BookQuery::create()->setModelAlias('b', true)->add('b.ID', 12, Criteria::EQUAL);
  208. $this->assertEquals($q1, $q, 'filterByPrimaryKey() uses true table alias if set');
  209. }
  210. public function testFilterByPrimaryKeyCompositeKey()
  211. {
  212. BookstoreDataPopulator::depopulate();
  213. BookstoreDataPopulator::populate();
  214. // save all books to make sure related objects are also saved - BookstoreDataPopulator keeps some unsaved
  215. $c = new ModelCriteria('bookstore', 'Book');
  216. $books = $c->find();
  217. foreach ($books as $book) {
  218. $book->save();
  219. }
  220. BookPeer::clearInstancePool();
  221. // retrieve the test data
  222. $c = new ModelCriteria('bookstore', 'BookListRel');
  223. $bookListRelTest = $c->findOne();
  224. $pk = $bookListRelTest->getPrimaryKey();
  225. $q = new BookListRelQuery();
  226. $q->filterByPrimaryKey($pk);
  227. $q1 = BookListRelQuery::create()
  228. ->add(BookListRelPeer::BOOK_ID, $pk[0], Criteria::EQUAL)
  229. ->add(BookListRelPeer::BOOK_CLUB_LIST_ID, $pk[1], Criteria::EQUAL);
  230. $this->assertEquals($q1, $q, 'filterByPrimaryKey() translates to a Criteria::EQUAL in the PK columns');
  231. }
  232. public function testFilterByPrimaryKeysSimpleKey()
  233. {
  234. $q = BookQuery::create()->filterByPrimaryKeys(array(10, 11, 12));
  235. $q1 = BookQuery::create()->add(BookPeer::ID, array(10, 11, 12), Criteria::IN);
  236. $this->assertEquals($q1, $q, 'filterByPrimaryKeys() translates to a Criteria::IN on the PK column');
  237. $q = BookQuery::create()->setModelAlias('b', true)->filterByPrimaryKeys(array(10, 11, 12));
  238. $q1 = BookQuery::create()->setModelAlias('b', true)->add('b.ID', array(10, 11, 12), Criteria::IN);
  239. $this->assertEquals($q1, $q, 'filterByPrimaryKeys() uses true table alias if set');
  240. }
  241. public function testFilterByPrimaryKeysCompositeKey()
  242. {
  243. BookstoreDataPopulator::depopulate();
  244. BookstoreDataPopulator::populate();
  245. // save all books to make sure related objects are also saved - BookstoreDataPopulator keeps some unsaved
  246. $c = new ModelCriteria('bookstore', 'Book');
  247. $books = $c->find();
  248. foreach ($books as $book) {
  249. $book->save();
  250. }
  251. BookPeer::clearInstancePool();
  252. // retrieve the test data
  253. $c = new ModelCriteria('bookstore', 'BookListRel');
  254. $bookListRelTest = $c->find();
  255. $search = array();
  256. foreach ($bookListRelTest as $obj) {
  257. $search[]= $obj->getPrimaryKey();
  258. }
  259. $q = new BookListRelQuery();
  260. $q->filterByPrimaryKeys($search);
  261. $q1 = BookListRelQuery::create();
  262. foreach ($search as $key) {
  263. $cton0 = $q1->getNewCriterion(BookListRelPeer::BOOK_ID, $key[0], Criteria::EQUAL);
  264. $cton1 = $q1->getNewCriterion(BookListRelPeer::BOOK_CLUB_LIST_ID, $key[1], Criteria::EQUAL);
  265. $cton0->addAnd($cton1);
  266. $q1->addOr($cton0);
  267. }
  268. $this->assertEquals($q1, $q, 'filterByPrimaryKeys() translates to a series of Criteria::EQUAL in the PK columns');
  269. $q = new BookListRelQuery();
  270. $q->filterByPrimaryKeys(array());
  271. $q1 = BookListRelQuery::create();
  272. $q1->add(null, '1<>1', Criteria::CUSTOM);
  273. $this->assertEquals($q1, $q, 'filterByPrimaryKeys() translates to an always failing test on empty arrays');
  274. }
  275. public function testFilterByIntegerPk()
  276. {
  277. $q = BookQuery::create()->filterById(12);
  278. $q1 = BookQuery::create()->add(BookPeer::ID, 12, Criteria::EQUAL);
  279. $this->assertEquals($q1, $q, 'filterByPkColumn() translates to a Criteria::EQUAL by default');
  280. $q = BookQuery::create()->filterById(12, Criteria::NOT_EQUAL);
  281. $q1 = BookQuery::create()->add(BookPeer::ID, 12, Criteria::NOT_EQUAL);
  282. $this->assertEquals($q1, $q, 'filterByPkColumn() accepts an optional comparison operator');
  283. $q = BookQuery::create()->setModelAlias('b', true)->filterById(12);
  284. $q1 = BookQuery::create()->setModelAlias('b', true)->add('b.ID', 12, Criteria::EQUAL);
  285. $this->assertEquals($q1, $q, 'filterByPkColumn() uses true table alias if set');
  286. $q = BookQuery::create()->filterById(array(10, 11, 12));
  287. $q1 = BookQuery::create()->add(BookPeer::ID, array(10, 11, 12), Criteria::IN);
  288. $this->assertEquals($q1, $q, 'filterByPkColumn() translates to a Criteria::IN when passed a simple array key');
  289. $q = BookQuery::create()->filterById(array(10, 11, 12), Criteria::NOT_IN);
  290. $q1 = BookQuery::create()->add(BookPeer::ID, array(10, 11, 12), Criteria::NOT_IN);
  291. $this->assertEquals($q1, $q, 'filterByPkColumn() accepts a comparison when passed a simple array key');
  292. }
  293. public function testFilterByNumber()
  294. {
  295. $q = BookQuery::create()->filterByPrice(12);
  296. $q1 = BookQuery::create()->add(BookPeer::PRICE, 12, Criteria::EQUAL);
  297. $this->assertEquals($q1, $q, 'filterByNumColumn() translates to a Criteria::EQUAL by default');
  298. $q = BookQuery::create()->filterByPrice(12, Criteria::NOT_EQUAL);
  299. $q1 = BookQuery::create()->add(BookPeer::PRICE, 12, Criteria::NOT_EQUAL);
  300. $this->assertEquals($q1, $q, 'filterByNumColumn() accepts an optional comparison operator');
  301. $q = BookQuery::create()->setModelAlias('b', true)->filterByPrice(12);
  302. $q1 = BookQuery::create()->setModelAlias('b', true)->add('b.PRICE', 12, Criteria::EQUAL);
  303. $this->assertEquals($q1, $q, 'filterByNumColumn() uses true table alias if set');
  304. $q = BookQuery::create()->filterByPrice(array(10, 11, 12));
  305. $q1 = BookQuery::create()->add(BookPeer::PRICE, array(10, 11, 12), Criteria::IN);
  306. $this->assertEquals($q1, $q, 'filterByNumColumn() translates to a Criteria::IN when passed a simple array key');
  307. $q = BookQuery::create()->filterByPrice(array(10, 11, 12), Criteria::NOT_IN);
  308. $q1 = BookQuery::create()->add(BookPeer::PRICE, array(10, 11, 12), Criteria::NOT_IN);
  309. $this->assertEquals($q1, $q, 'filterByNumColumn() accepts a comparison when passed a simple array key');
  310. $q = BookQuery::create()->filterByPrice(array('min' => 10));
  311. $q1 = BookQuery::create()->add(BookPeer::PRICE, 10, Criteria::GREATER_EQUAL);
  312. $this->assertEquals($q1, $q, 'filterByNumColumn() translates to a Criteria::GREATER_EQUAL when passed a \'min\' key');
  313. $q = BookQuery::create()->filterByPrice(array('max' => 12));
  314. $q1 = BookQuery::create()->add(BookPeer::PRICE, 12, Criteria::LESS_EQUAL);
  315. $this->assertEquals($q1, $q, 'filterByNumColumn() translates to a Criteria::LESS_EQUAL when passed a \'max\' key');
  316. $q = BookQuery::create()->filterByPrice(array('min' => 10, 'max' => 12));
  317. $q1 = BookQuery::create()
  318. ->add(BookPeer::PRICE, 10, Criteria::GREATER_EQUAL)
  319. ->addAnd(BookPeer::PRICE, 12, Criteria::LESS_EQUAL);
  320. $this->assertEquals($q1, $q, 'filterByNumColumn() translates to a between when passed both a \'min\' and a \'max\' key');
  321. }
  322. public function testFilterByTimestamp()
  323. {
  324. $q = BookstoreEmployeeAccountQuery::create()->filterByCreated(12);
  325. $q1 = BookstoreEmployeeAccountQuery::create()->add(BookstoreEmployeeAccountPeer::CREATED, 12, Criteria::EQUAL);
  326. $this->assertEquals($q1, $q, 'filterByDateColumn() translates to a Criteria::EQUAL by default');
  327. $q = BookstoreEmployeeAccountQuery::create()->filterByCreated(12, Criteria::NOT_EQUAL);
  328. $q1 = BookstoreEmployeeAccountQuery::create()->add(BookstoreEmployeeAccountPeer::CREATED, 12, Criteria::NOT_EQUAL);
  329. $this->assertEquals($q1, $q, 'filterByDateColumn() accepts an optional comparison operator');
  330. $q = BookstoreEmployeeAccountQuery::create()->setModelAlias('b', true)->filterByCreated(12);
  331. $q1 = BookstoreEmployeeAccountQuery::create()->setModelAlias('b', true)->add('b.CREATED', 12, Criteria::EQUAL);
  332. $this->assertEquals($q1, $q, 'filterByDateColumn() uses true table alias if set');
  333. $q = BookstoreEmployeeAccountQuery::create()->filterByCreated(array('min' => 10));
  334. $q1 = BookstoreEmployeeAccountQuery::create()->add(BookstoreEmployeeAccountPeer::CREATED, 10, Criteria::GREATER_EQUAL);
  335. $this->assertEquals($q1, $q, 'filterByDateColumn() translates to a Criteria::GREATER_EQUAL when passed a \'min\' key');
  336. $q = BookstoreEmployeeAccountQuery::create()->filterByCreated(array('max' => 12));
  337. $q1 = BookstoreEmployeeAccountQuery::create()->add(BookstoreEmployeeAccountPeer::CREATED, 12, Criteria::LESS_EQUAL);
  338. $this->assertEquals($q1, $q, 'filterByDateColumn() translates to a Criteria::LESS_EQUAL when passed a \'max\' key');
  339. $q = BookstoreEmployeeAccountQuery::create()->filterByCreated(array('min' => 10, 'max' => 12));
  340. $q1 = BookstoreEmployeeAccountQuery::create()
  341. ->add(BookstoreEmployeeAccountPeer::CREATED, 10, Criteria::GREATER_EQUAL)
  342. ->addAnd(BookstoreEmployeeAccountPeer::CREATED, 12, Criteria::LESS_EQUAL);
  343. $this->assertEquals($q1, $q, 'filterByDateColumn() translates to a between when passed both a \'min\' and a \'max\' key');
  344. }
  345. public function testFilterByString()
  346. {
  347. $q = BookQuery::create()->filterByTitle('foo');
  348. $q1 = BookQuery::create()->add(BookPeer::TITLE, 'foo', Criteria::EQUAL);
  349. $this->assertEquals($q1, $q, 'filterByStringColumn() translates to a Criteria::EQUAL by default');
  350. $q = BookQuery::create()->filterByTitle('foo', Criteria::NOT_EQUAL);
  351. $q1 = BookQuery::create()->add(BookPeer::TITLE, 'foo', Criteria::NOT_EQUAL);
  352. $this->assertEquals($q1, $q, 'filterByStringColumn() accepts an optional comparison operator');
  353. $q = BookQuery::create()->setModelAlias('b', true)->filterByTitle('foo');
  354. $q1 = BookQuery::create()->setModelAlias('b', true)->add('b.TITLE', 'foo', Criteria::EQUAL);
  355. $this->assertEquals($q1, $q, 'filterByStringColumn() uses true table alias if set');
  356. $q = BookQuery::create()->filterByTitle(array('foo', 'bar'));
  357. $q1 = BookQuery::create()->add(BookPeer::TITLE, array('foo', 'bar'), Criteria::IN);
  358. $this->assertEquals($q1, $q, 'filterByStringColumn() translates to a Criteria::IN when passed an array');
  359. $q = BookQuery::create()->filterByTitle(array('foo', 'bar'), Criteria::NOT_IN);
  360. $q1 = BookQuery::create()->add(BookPeer::TITLE, array('foo', 'bar'), Criteria::NOT_IN);
  361. $this->assertEquals($q1, $q, 'filterByStringColumn() accepts a comparison when passed an array');
  362. $q = BookQuery::create()->filterByTitle('foo%');
  363. $q1 = BookQuery::create()->add(BookPeer::TITLE, 'foo%', Criteria::LIKE);
  364. $this->assertEquals($q1, $q, 'filterByStringColumn() translates to a Criteria::LIKE when passed a string with a % wildcard');
  365. $q = BookQuery::create()->filterByTitle('foo%', Criteria::NOT_LIKE);
  366. $q1 = BookQuery::create()->add(BookPeer::TITLE, 'foo%', Criteria::NOT_LIKE);
  367. $this->assertEquals($q1, $q, 'filterByStringColumn() accepts a comparison when passed a string with a % wildcard');
  368. $q = BookQuery::create()->filterByTitle('foo%', Criteria::EQUAL);
  369. $q1 = BookQuery::create()->add(BookPeer::TITLE, 'foo%', Criteria::EQUAL);
  370. $this->assertEquals($q1, $q, 'filterByStringColumn() accepts a comparison when passed a string with a % wildcard');
  371. $q = BookQuery::create()->filterByTitle('*foo');
  372. $q1 = BookQuery::create()->add(BookPeer::TITLE, '%foo', Criteria::LIKE);
  373. $this->assertEquals($q1, $q, 'filterByStringColumn() translates to a Criteria::LIKE when passed a string with a * wildcard, and turns * into %');
  374. $q = BookQuery::create()->filterByTitle('*f%o*o%');
  375. $q1 = BookQuery::create()->add(BookPeer::TITLE, '%f%o%o%', Criteria::LIKE);
  376. $this->assertEquals($q1, $q, 'filterByStringColumn() translates to a Criteria::LIKE when passed a string with mixed wildcards, and turns *s into %s');
  377. }
  378. public function testFilterByBoolean()
  379. {
  380. $q = ReviewQuery::create()->filterByRecommended(true);
  381. $q1 = ReviewQuery::create()->add(ReviewPeer::RECOMMENDED, true, Criteria::EQUAL);
  382. $this->assertEquals($q1, $q, 'filterByBooleanColumn() translates to a Criteria::EQUAL by default');
  383. $q = ReviewQuery::create()->filterByRecommended(true, Criteria::NOT_EQUAL);
  384. $q1 = ReviewQuery::create()->add(ReviewPeer::RECOMMENDED, true, Criteria::NOT_EQUAL);
  385. $this->assertEquals($q1, $q, 'filterByBooleanColumn() accepts an optional comparison operator');
  386. $q = ReviewQuery::create()->filterByRecommended(false);
  387. $q1 = ReviewQuery::create()->add(ReviewPeer::RECOMMENDED, false, Criteria::EQUAL);
  388. $this->assertEquals($q1, $q, 'filterByBooleanColumn() translates to a Criteria::EQUAL by default');
  389. $q = ReviewQuery::create()->setModelAlias('b', true)->filterByRecommended(true);
  390. $q1 = ReviewQuery::create()->setModelAlias('b', true)->add('b.RECOMMENDED', true, Criteria::EQUAL);
  391. $this->assertEquals($q1, $q, 'filterByBooleanColumn() uses true table alias if set');
  392. $q = ReviewQuery::create()->filterByRecommended('true');
  393. $q1 = ReviewQuery::create()->add(ReviewPeer::RECOMMENDED, true, Criteria::EQUAL);
  394. $this->assertEquals($q1, $q, 'filterByBooleanColumn() translates to a = true when passed a true string');
  395. $q = ReviewQuery::create()->filterByRecommended('yes');
  396. $q1 = ReviewQuery::create()->add(ReviewPeer::RECOMMENDED, true, Criteria::EQUAL);
  397. $this->assertEquals($q1, $q, 'filterByBooleanColumn() translates to a = true when passed a true string');
  398. $q = ReviewQuery::create()->filterByRecommended('1');
  399. $q1 = ReviewQuery::create()->add(ReviewPeer::RECOMMENDED, true, Criteria::EQUAL);
  400. $this->assertEquals($q1, $q, 'filterByBooleanColumn() translates to a = true when passed a true string');
  401. $q = ReviewQuery::create()->filterByRecommended('false');
  402. $q1 = ReviewQuery::create()->add(ReviewPeer::RECOMMENDED, false, Criteria::EQUAL);
  403. $this->assertEquals($q1, $q, 'filterByBooleanColumn() translates to a = false when passed a false string');
  404. $q = ReviewQuery::create()->filterByRecommended('no');
  405. $q1 = ReviewQuery::create()->add(ReviewPeer::RECOMMENDED, false, Criteria::EQUAL);
  406. $this->assertEquals($q1, $q, 'filterByBooleanColumn() translates to a = false when passed a false string');
  407. $q = ReviewQuery::create()->filterByRecommended('0');
  408. $q1 = ReviewQuery::create()->add(ReviewPeer::RECOMMENDED, false, Criteria::EQUAL);
  409. $this->assertEquals($q1, $q, 'filterByBooleanColumn() translates to a = false when passed a false string');
  410. }
  411. public function testFilterByFk()
  412. {
  413. $this->assertTrue(method_exists('BookQuery', 'filterByAuthor'), 'QueryBuilder adds filterByFk() methods');
  414. $this->assertTrue(method_exists('BookQuery', 'filterByPublisher'), 'QueryBuilder adds filterByFk() methods for all fkeys');
  415. $this->assertTrue(method_exists('EssayQuery', 'filterByAuthorRelatedByFirstAuthor'), 'QueryBuilder adds filterByFk() methods for several fkeys on the same table');
  416. $this->assertTrue(method_exists('EssayQuery', 'filterByAuthorRelatedBySecondAuthor'), 'QueryBuilder adds filterByFk() methods for several fkeys on the same table');
  417. }
  418. public function testFilterByFkSimpleKey()
  419. {
  420. BookstoreDataPopulator::depopulate();
  421. BookstoreDataPopulator::populate();
  422. // prepare the test data
  423. $testBook = BookQuery::create()
  424. ->innerJoin('Book.Author') // just in case there are books with no author
  425. ->findOne();
  426. $testAuthor = $testBook->getAuthor();
  427. $book = BookQuery::create()
  428. ->filterByAuthor($testAuthor)
  429. ->findOne();
  430. $this->assertEquals($testBook, $book, 'Generated query handles filterByFk() methods correctly for simple fkeys');
  431. $q = BookQuery::create()->filterByAuthor($testAuthor);
  432. $q1 = BookQuery::create()->add(BookPeer::AUTHOR_ID, $testAuthor->getId(), Criteria::EQUAL);
  433. $this->assertEquals($q1, $q, 'filterByFk() translates to a Criteria::EQUAL by default');
  434. $q = BookQuery::create()->filterByAuthor($testAuthor, Criteria::NOT_EQUAL);
  435. $q1 = BookQuery::create()->add(BookPeer::AUTHOR_ID, $testAuthor->getId(), Criteria::NOT_EQUAL);
  436. $this->assertEquals($q1, $q, 'filterByFk() accepts an optional comparison operator');
  437. }
  438. public function testFilterByFkCompositeKey()
  439. {
  440. BookstoreDataPopulator::depopulate();
  441. BookstoreDataPopulator::populate();
  442. BookstoreDataPopulator::populateOpinionFavorite();
  443. // prepare the test data
  444. $testOpinion = BookOpinionQuery::create()
  445. ->innerJoin('BookOpinion.ReaderFavorite') // just in case there are books with no author
  446. ->findOne();
  447. $testFavorite = $testOpinion->getReaderFavorite();
  448. $favorite = ReaderFavoriteQuery::create()
  449. ->filterByBookOpinion($testOpinion)
  450. ->findOne();
  451. $this->assertEquals($testFavorite, $favorite, 'Generated query handles filterByFk() methods correctly for composite fkeys');
  452. }
  453. public function testFilterByRefFk()
  454. {
  455. $this->assertTrue(method_exists('BookQuery', 'filterByReview'), 'QueryBuilder adds filterByRefFk() methods');
  456. $this->assertTrue(method_exists('BookQuery', 'filterByMedia'), 'QueryBuilder adds filterByRefFk() methods for all fkeys');
  457. $this->assertTrue(method_exists('AuthorQuery', 'filterByEssayRelatedByFirstAuthor'), 'QueryBuilder adds filterByRefFk() methods for several fkeys on the same table');
  458. $this->assertTrue(method_exists('AuthorQuery', 'filterByEssayRelatedBySecondAuthor'), 'QueryBuilder adds filterByRefFk() methods for several fkeys on the same table');
  459. }
  460. public function testFilterByRefFkSimpleKey()
  461. {
  462. BookstoreDataPopulator::depopulate();
  463. BookstoreDataPopulator::populate();
  464. // prepare the test data
  465. $testBook = BookQuery::create()
  466. ->innerJoin('Book.Author') // just in case there are books with no author
  467. ->findOne();
  468. $testAuthor = $testBook->getAuthor();
  469. $author = AuthorQuery::create()
  470. ->filterByBook($testBook)
  471. ->findOne();
  472. $this->assertEquals($testAuthor, $author, 'Generated query handles filterByRefFk() methods correctly for simple fkeys');
  473. $q = AuthorQuery::create()->filterByBook($testBook);
  474. $q1 = AuthorQuery::create()->add(AuthorPeer::ID, $testBook->getAuthorId(), Criteria::EQUAL);
  475. $this->assertEquals($q1, $q, 'filterByRefFk() translates to a Criteria::EQUAL by default');
  476. $q = AuthorQuery::create()->filterByBook($testBook, Criteria::NOT_EQUAL);
  477. $q1 = AuthorQuery::create()->add(AuthorPeer::ID, $testBook->getAuthorId(), Criteria::NOT_EQUAL);
  478. $this->assertEquals($q1, $q, 'filterByRefFk() accepts an optional comparison operator');
  479. }
  480. public function testFilterByRefFkCompositeKey()
  481. {
  482. BookstoreDataPopulator::depopulate();
  483. BookstoreDataPopulator::populate();
  484. BookstoreDataPopulator::populateOpinionFavorite();
  485. // prepare the test data
  486. $testOpinion = BookOpinionQuery::create()
  487. ->innerJoin('BookOpinion.ReaderFavorite') // just in case there are books with no author
  488. ->findOne();
  489. $testFavorite = $testOpinion->getReaderFavorite();
  490. $opinion = BookOpinionQuery::create()
  491. ->filterByReaderFavorite($testFavorite)
  492. ->findOne();
  493. $this->assertEquals($testOpinion, $opinion, 'Generated query handles filterByRefFk() methods correctly for composite fkeys');
  494. }
  495. public function testFilterByCrossFK()
  496. {
  497. $this->assertTrue(method_exists('BookQuery', 'filterByBookClubList'), 'Generated query handles filterByCrossRefFK() for many-to-many relationships');
  498. $this->assertFalse(method_exists('BookQuery', 'filterByBook'), 'Generated query handles filterByCrossRefFK() for many-to-many relationships');
  499. BookstoreDataPopulator::depopulate();
  500. BookstoreDataPopulator::populate();
  501. $blc1 = BookClubListQuery::create()->findOneByGroupLeader('Crazyleggs');
  502. $nbBooks = BookQuery::create()
  503. ->filterByBookClubList($blc1)
  504. ->count();
  505. $this->assertEquals(2, $nbBooks, 'Generated query handles filterByCrossRefFK() methods correctly');
  506. }
  507. public function testJoinFk()
  508. {
  509. $q = BookQuery::create()
  510. ->joinAuthor();
  511. $q1 = BookQuery::create()
  512. ->join('Book.Author', Criteria::LEFT_JOIN);
  513. $this->assertTrue($q->equals($q1), 'joinFk() translates to a left join on non-required columns');
  514. $q = ReviewQuery::create()
  515. ->joinBook();
  516. $q1 = ReviewQuery::create()
  517. ->join('Review.Book', Criteria::INNER_JOIN);
  518. $this->assertTrue($q->equals($q1), 'joinFk() translates to an inner join on required columns');
  519. $q = BookQuery::create()
  520. ->joinAuthor('a');
  521. $q1 = BookQuery::create()
  522. ->join('Book.Author a', Criteria::LEFT_JOIN);
  523. $this->assertTrue($q->equals($q1), 'joinFk() accepts a relation alias as first parameter');
  524. $q = BookQuery::create()
  525. ->joinAuthor('', Criteria::INNER_JOIN);
  526. $q1 = BookQuery::create()
  527. ->join('Book.Author', Criteria::INNER_JOIN);
  528. $this->assertTrue($q->equals($q1), 'joinFk() accepts a join type as second parameter');
  529. $q = EssayQuery::create()
  530. ->joinAuthorRelatedBySecondAuthor();
  531. $q1 = EssayQuery::create()
  532. ->join('Essay.AuthorRelatedBySecondAuthor', "INNER JOIN");
  533. $this->assertTrue($q->equals($q1), 'joinFk() translates to a "INNER JOIN" when this is defined as defaultJoin in the schema');
  534. }
  535. public function testJoinFkAlias()
  536. {
  537. $q = BookQuery::create('b')
  538. ->joinAuthor('a');
  539. $q1 = BookQuery::create('b')
  540. ->join('b.Author a', Criteria::LEFT_JOIN);
  541. $this->assertTrue($q->equals($q1), 'joinFk() works fine with table aliases');
  542. $q = BookQuery::create()
  543. ->setModelAlias('b', true)
  544. ->joinAuthor('a');
  545. $q1 = BookQuery::create()
  546. ->setModelAlias('b', true)
  547. ->join('b.Author a', Criteria::LEFT_JOIN);
  548. $this->assertTrue($q->equals($q1), 'joinFk() works fine with true table aliases');
  549. }
  550. public function testJoinRefFk()
  551. {
  552. $q = AuthorQuery::create()
  553. ->joinBook();
  554. $q1 = AuthorQuery::create()
  555. ->join('Author.Book', Criteria::LEFT_JOIN);
  556. $this->assertTrue($q->equals($q1), 'joinRefFk() translates to a left join on non-required columns');
  557. $q = BookQuery::create()
  558. ->joinreview();
  559. $q1 = BookQuery::create()
  560. ->join('Book.Review', Criteria::INNER_JOIN);
  561. $this->assertTrue($q->equals($q1), 'joinRefFk() translates to an inner join on required columns');
  562. $q = AuthorQuery::create()
  563. ->joinBook('b');
  564. $q1 = AuthorQuery::create()
  565. ->join('Author.Book b', Criteria::LEFT_JOIN);
  566. $this->assertTrue($q->equals($q1), 'joinRefFk() accepts a relation alias as first parameter');
  567. $q = AuthorQuery::create()
  568. ->joinBook('', Criteria::INNER_JOIN);
  569. $q1 = AuthorQuery::create()
  570. ->join('Author.Book', Criteria::INNER_JOIN);
  571. $this->assertTrue($q->equals($q1), 'joinRefFk() accepts a join type as second parameter');
  572. $q = AuthorQuery::create()
  573. ->joinEssayRelatedBySecondAuthor();
  574. $q1 = AuthorQuery::create()
  575. ->join('Author.EssayRelatedBySecondAuthor', Criteria::INNER_JOIN);
  576. $this->assertTrue($q->equals($q1), 'joinRefFk() translates to a "INNER JOIN" when this is defined as defaultJoin in the schema');
  577. }
  578. public function testUseFkQuerySimple()
  579. {
  580. $q = BookQuery::create()
  581. ->useAuthorQuery()
  582. ->filterByFirstName('Leo')
  583. ->endUse();
  584. $q1 = BookQuery::create()
  585. ->join('Book.Author', Criteria::LEFT_JOIN)
  586. ->add(AuthorPeer::FIRST_NAME, 'Leo', Criteria::EQUAL);
  587. $this->assertTrue($q->equals($q1), 'useFkQuery() translates to a condition on a left join on non-required columns');
  588. $q = ReviewQuery::create()
  589. ->useBookQuery()
  590. ->filterByTitle('War And Peace')
  591. ->endUse();
  592. $q1 = ReviewQuery::create()
  593. ->join('Review.Book', Criteria::INNER_JOIN)
  594. ->add(BookPeer::TITLE, 'War And Peace', Criteria::EQUAL);
  595. $this->assertTrue($q->equals($q1), 'useFkQuery() translates to a condition on aninner join on required columns');
  596. }
  597. public function testUseFkQueryJoinType()
  598. {
  599. $q = BookQuery::create()
  600. ->useAuthorQuery(null, Criteria::LEFT_JOIN)
  601. ->filterByFirstName('Leo')
  602. ->endUse();
  603. $q1 = BookQuery::create()
  604. ->join('Book.Author', Criteria::LEFT_JOIN)
  605. ->add(AuthorPeer::FIRST_NAME, 'Leo', Criteria::EQUAL);
  606. $this->assertTrue($q->equals($q1), 'useFkQuery() accepts a join type as second parameter');
  607. }
  608. public function testUseFkQueryAlias()
  609. {
  610. $q = BookQuery::create()
  611. ->useAuthorQuery('a')
  612. ->filterByFirstName('Leo')
  613. ->endUse();
  614. $join = new ModelJoin();
  615. $join->setJoinType(Criteria::LEFT_JOIN);
  616. $join->setTableMap(AuthorPeer::getTableMap());
  617. $join->setRelationMap(BookPeer::getTableMap()->getRelation('Author'), null, 'a');
  618. $join->setRelationAlias('a');
  619. $q1 = BookQuery::create()
  620. ->addAlias('a', AuthorPeer::TABLE_NAME)
  621. ->addJoinObject($join, 'a')
  622. ->add('a.FIRST_NAME', 'Leo', Criteria::EQUAL);
  623. $this->assertTrue($q->equals($q1), 'useFkQuery() uses the first argument as a table alias');
  624. }
  625. public function testUseFkQueryMixed()
  626. {
  627. $q = BookQuery::create()
  628. ->useAuthorQuery()
  629. ->filterByFirstName('Leo')
  630. ->endUse()
  631. ->filterByTitle('War And Peace');
  632. $q1 = BookQuery::create()
  633. ->join('Book.Author', Criteria::LEFT_JOIN)
  634. ->add(AuthorPeer::FIRST_NAME, 'Leo', Criteria::EQUAL)
  635. ->add(BookPeer::TITLE, 'War And Peace', Criteria::EQUAL);
  636. $this->assertTrue($q->equals($q1), 'useFkQuery() allows combining conditions on main and related query');
  637. }
  638. public function testUseFkQueryTwice()
  639. {
  640. $q = BookQuery::create()
  641. ->useAuthorQuery()
  642. ->filterByFirstName('Leo')
  643. ->endUse()
  644. ->useAuthorQuery()
  645. ->filterByLastName('Tolstoi')
  646. ->endUse();
  647. $q1 = BookQuery::create()
  648. ->join('Book.Author', Criteria::LEFT_JOIN)
  649. ->add(AuthorPeer::FIRST_NAME, 'Leo', Criteria::EQUAL)
  650. ->add(AuthorPeer::LAST_NAME, 'Tolstoi', Criteria::EQUAL);
  651. $this->assertTrue($q->equals($q1), 'useFkQuery() called twice on the same relation does not create two joins');
  652. }
  653. public function testUseFkQueryTwiceTwoAliases()
  654. {
  655. $q = BookQuery::create()
  656. ->useAuthorQuery('a')
  657. ->filterByFirstName('Leo')
  658. ->endUse()
  659. ->useAuthorQuery('b')
  660. ->filterByLastName('Tolstoi')
  661. ->endUse();
  662. $join1 = new ModelJoin();
  663. $join1->setJoinType(Criteria::LEFT_JOIN);
  664. $join1->setTableMap(AuthorPeer::getTableMap());
  665. $join1->setRelationMap(BookPeer::getTableMap()->getRelation('Author'), null, 'a');
  666. $join1->setRelationAlias('a');
  667. $join2 = new ModelJoin();
  668. $join2->setJoinType(Criteria::LEFT_JOIN);
  669. $join2->setTableMap(AuthorPeer::getTableMap());
  670. $join2->setRelationMap(BookPeer::getTableMap()->getRelation('Author'), null, 'b');
  671. $join2->setRelationAlias('b');
  672. $q1 = BookQuery::create()
  673. ->addAlias('a', AuthorPeer::TABLE_NAME)
  674. ->addJoinObject($join1, 'a')
  675. ->add('a.FIRST_NAME', 'Leo', Criteria::EQUAL)
  676. ->addAlias('b', AuthorPeer::TABLE_NAME)
  677. ->addJoinObject($join2, 'b')
  678. ->add('b.LAST_NAME', 'Tolstoi', Criteria::EQUAL);
  679. $this->assertTrue($q->equals($q1), 'useFkQuery() called twice on the same relation with two aliases creates two joins');
  680. }
  681. public function testUseFkQueryNested()
  682. {
  683. $q = ReviewQuery::create()
  684. ->useBookQuery()
  685. ->useAuthorQuery()
  686. ->filterByFirstName('Leo')
  687. ->endUse()
  688. ->endUse();
  689. $q1 = ReviewQuery::create()
  690. ->join('Review.Book', Criteria::INNER_JOIN)
  691. ->join('Book.Author', Criteria::LEFT_JOIN)
  692. ->add(AuthorPeer::FIRST_NAME, 'Leo', Criteria::EQUAL);
  693. // embedded queries create joins that keep a relation to the parent
  694. // as this is not testable, we need to use another testing technique
  695. $params = array();
  696. $result = BasePeer::createSelectSql($q, $params);
  697. $expectedParams = array();
  698. $expectedResult = BasePeer::createSelectSql($q1, $expectedParams);
  699. $this->assertEquals($expectedParams, $params, 'useFkQuery() called nested creates two joins');
  700. $this->assertEquals($expectedResult, $result, 'useFkQuery() called nested creates two joins');
  701. }
  702. public function testUseFkQueryTwoRelations()
  703. {
  704. $q = BookQuery::create()
  705. ->useAuthorQuery()
  706. ->filterByFirstName('Leo')
  707. ->endUse()
  708. ->usePublisherQuery()
  709. ->filterByName('Penguin')
  710. ->endUse();
  711. $q1 = BookQuery::create()
  712. ->join('Book.Author', Criteria::LEFT_JOIN)
  713. ->add(AuthorPeer::FIRST_NAME, 'Leo', Criteria::EQUAL)
  714. ->join('Book.Publisher', Criteria::LEFT_JOIN)
  715. ->add(PublisherPeer::NAME, 'Penguin', Criteria::EQUAL);
  716. $this->assertTrue($q->equals($q1), 'useFkQuery() called twice on two relations creates two joins');
  717. }
  718. public function testPrune()
  719. {
  720. $q = BookQuery::create()->prune();
  721. $this->assertTrue($q instanceof BookQuery, 'prune() returns the current Query object');
  722. }
  723. public function testPruneSimpleKey()
  724. {
  725. BookstoreDataPopulator::depopulate();
  726. BookstoreDataPopulator::populate();
  727. $nbBooks = BookQuery::create()->prune()->count();
  728. $this->assertEquals(4, $nbBooks, 'prune() does nothing when passed a null object');
  729. $testBook = BookQuery::create()->findOne();
  730. $nbBooks = BookQuery::create()->prune($testBook)->count();
  731. $this->assertEquals(3, $nbBooks, 'prune() removes an object from the result');
  732. }
  733. public function testPruneCompositeKey()
  734. {
  735. BookstoreDataPopulator::depopulate();
  736. BookstoreDataPopulator::populate();
  737. // save all books to make sure related objects are also saved - BookstoreDataPopulator keeps some unsaved
  738. $c = new ModelCriteria('bookstore', 'Book');
  739. $books = $c->find();
  740. foreach ($books as $book) {
  741. $book->save();
  742. }
  743. BookPeer::clearInstancePool();
  744. $nbBookListRel = BookListRelQuery::create()->prune()->count();
  745. $this->assertEquals(2, $nbBookListRel, 'prune() does nothing when passed a null object');
  746. $testBookListRel = BookListRelQuery::create()->findOne();
  747. $nbBookListRel = BookListRelQuery::create()->prune($testBookListRel)->count();
  748. $this->assertEquals(1, $nbBookListRel, 'prune() removes an object from the result');
  749. }
  750. }
  751. class myCustomBookQuery extends BookQuery
  752. {
  753. public static function create($modelAlias = null, $criteria = null)
  754. {
  755. if ($criteria instanceof myCustomBookQuery) {
  756. return $criteria;
  757. }
  758. $query = new myCustomBookQuery();
  759. if (null !== $modelAlias) {
  760. $query->setModelAlias($modelAlias);
  761. }
  762. if ($criteria instanceof Criteria) {
  763. $query->mergeWith($criteria);
  764. }
  765. return $query;
  766. }
  767. }