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