/test/testsuite/runtime/query/ModelCriteriaTest.php

<?php

/**
 * This file is part of the Propel package.
 * For the full copyright and license information, please view the LICENSE
 * file that was distributed with this source code.
 *
 * @license    MIT License
 */

require_once dirname(__FILE__) . '/../../../tools/helpers/bookstore/BookstoreTestBase.php';
require_once dirname(__FILE__) . '/../../../tools/helpers/bookstore/BookstoreDataPopulator.php';

/**
 * Test class for ModelCriteria.
 *
 * @author     Francois Zaninotto
 * @version    $Id$
 * @package    runtime.query
 */
class ModelCriteriaTest extends BookstoreTestBase
{
	protected function assertCriteriaTranslation($criteria, $expectedSql, $expectedParams, $message = '')
	{
		$params = array();
		$result = BasePeer::createSelectSql($criteria, $params);

		$this->assertEquals($expectedSql, $result, $message);
		$this->assertEquals($expectedParams, $params, $message);
	}

	public function testGetModelName()
	{
		$c = new ModelCriteria('bookstore', 'Book');
		$this->assertEquals('Book', $c->getModelName(), 'getModelName() returns the name of the class associated to the model class');
	}

	public function testGetModelPeerName()
	{
		$c = new ModelCriteria('bookstore', 'Book');
		$this->assertEquals('BookPeer', $c->getModelPeerName(), 'getModelPeerName() returns the name of the Peer class associated to the model class');
	}

	public function testFormatter()
	{
		$c = new ModelCriteria('bookstore', 'Book');
		$this->assertTrue($c->getFormatter() instanceof PropelFormatter, 'getFormatter() returns a PropelFormatter instance');

		$c = new ModelCriteria('bookstore', 'Book');
		$c->setFormatter(ModelCriteria::FORMAT_STATEMENT);
		$this->assertTrue($c->getFormatter() instanceof PropelStatementFormatter, 'setFormatter() accepts the name of a PropelFormatter class');

		try {
			$c->setFormatter('Book');
			$this->fail('setFormatter() throws an exception when passed the name of a class not extending PropelFormatter');
		} catch(PropelException $e) {
			$this->assertTrue(true, 'setFormatter() throws an exception when passed the name of a class not extending PropelFormatter');
		}
		$c = new ModelCriteria('bookstore', 'Book');
		$formatter = new PropelStatementFormatter();
		$c->setFormatter($formatter);
		$this->assertTrue($c->getFormatter() instanceof PropelStatementFormatter, 'setFormatter() accepts a PropelFormatter instance');

		try {
			$formatter = new Book();
			$c->setFormatter($formatter);
			$this->fail('setFormatter() throws an exception when passed an object not extending PropelFormatter');
		} catch(PropelException $e) {
			$this->assertTrue(true, 'setFormatter() throws an exception when passedan object not extending PropelFormatter');
		}

	}

	public static function conditionsForTestReplaceNames()
	{
		return array(
			array('Book.Title = ?', 'Title', 'book.TITLE = ?'), // basic case
			array('Book.Title=?', 'Title', 'book.TITLE=?'), // without spaces
			array('Book.Id<= ?', 'Id', 'book.ID<= ?'), // with non-equal comparator
			array('Book.AuthorId LIKE ?', 'AuthorId', 'book.AUTHOR_ID LIKE ?'), // with SQL keyword separator
			array('(Book.AuthorId) LIKE ?', 'AuthorId', '(book.AUTHOR_ID) LIKE ?'), // with parenthesis
			array('(Book.Id*1.5)=1', 'Id', '(book.ID*1.5)=1'), // ignore numbers
			// dealing with quotes
			array("Book.Id + ' ' + Book.AuthorId", null, "book.ID + ' ' + book.AUTHOR_ID"),
			array("'Book.Id' + Book.AuthorId", null, "'Book.Id' + book.AUTHOR_ID"),
			array("Book.Id + 'Book.AuthorId'", null, "book.ID + 'Book.AuthorId'"),
			array('1=1', null, '1=1'), // with no name
			array('', null, '') // with empty string
		);
	}

	/**
	 * @dataProvider conditionsForTestReplaceNames
	 */
	public function testReplaceNames($origClause, $columnPhpName = false, $modifiedClause)
	{
		$c = new TestableModelCriteria('bookstore', 'Book');
		$this->doTestReplaceNames($c, BookPeer::getTableMap(), $origClause, $columnPhpName = false, $modifiedClause);
	}

	public function doTestReplaceNames($c, $tableMap, $origClause, $columnPhpName = false, $modifiedClause)
	{
		$c->replaceNames($origClause);
		$columns = $c->replacedColumns;
		if ($columnPhpName) {
			$this->assertEquals(array($tableMap->getColumnByPhpName($columnPhpName)), $columns);
		}
		$this->assertEquals($modifiedClause, $origClause);
	}

	public static function conditionsForTestReplaceMultipleNames()
	{
		return array(
			array('(Book.Id+Book.Id)=1', array('Id', 'Id'), '(book.ID+book.ID)=1'), // match multiple names
			array('CONCAT(Book.Title,"Book.Id")= ?', array('Title', 'Id'), 'CONCAT(book.TITLE,"Book.Id")= ?'), // ignore names in strings
			array('CONCAT(Book.Title," Book.Id ")= ?', array('Title', 'Id'), 'CONCAT(book.TITLE," Book.Id ")= ?'), // ignore names in strings
			array('MATCH (Book.Title,Book.ISBN) AGAINST (?)', array('Title', 'ISBN'), 'MATCH (book.TITLE,book.ISBN) AGAINST (?)'),
		);
	}

	/**
	 * @dataProvider conditionsForTestReplaceMultipleNames
	 */
	public function testReplaceMultipleNames($origClause, $expectedColumns, $modifiedClause)
	{
		$c = new TestableModelCriteria('bookstore', 'Book');
		$c->replaceNames($origClause);
		$foundColumns = $c->replacedColumns;
		foreach ($foundColumns as $column) {
			$expectedColumn = BookPeer::getTableMap()->getColumnByPhpName(array_shift($expectedColumns));
			$this->assertEquals($expectedColumn, $column);
		}
		$this->assertEquals($modifiedClause, $origClause);
	}

	public function testTableAlias()
	{
		$c = new ModelCriteria('bookstore', 'Book');
		$c->setModelAlias('b');
		$c->where('b.Title = ?', 'foo');

		$sql = "SELECT  FROM `book` WHERE book.TITLE = :p1";
		$params = array(
			array('table' => 'book', 'column' => 'TITLE', 'value' => 'foo'),
		);
		$this->assertCriteriaTranslation($c, $sql, $params, 'setModelAlias() allows the definition of the alias after constrution');

		$c = new ModelCriteria('bookstore', 'Book', 'b');
		$c->where('b.Title = ?', 'foo');

		$sql = "SELECT  FROM `book` WHERE book.TITLE = :p1";
		$params = array(
			array('table' => 'book', 'column' => 'TITLE', 'value' => 'foo'),
		);
		$this->assertCriteriaTranslation($c, $sql, $params, 'A ModelCriteria accepts a model name with an alias');
	}

	public function testTrueTableAlias()
	{
		$c = new ModelCriteria('bookstore', 'Book');
		$c->setModelAlias('b', true);
		$c->where('b.Title = ?', 'foo');
		$c->join('b.Author a');
		$c->where('a.FirstName = ?', 'john');


		$sql = "SELECT  FROM `book` `b` INNER JOIN `author` `a` ON (b.AUTHOR_ID=a.ID) WHERE b.TITLE = :p1 AND a.FIRST_NAME = :p2";
		$params = array(
			array('table' => 'book', 'column' => 'TITLE', 'value' => 'foo'),
			array('table' => 'author', 'column' => 'FIRST_NAME', 'value' => 'john'),
		);
		$this->assertCriteriaTranslation($c, $sql, $params, 'setModelAlias() allows the definition of a true SQL alias after constrution');
	}

	public function testCondition()
	{
		$c = new ModelCriteria('bookstore', 'Book');
		$c->condition('cond1', 'Book.Title <> ?', 'foo');
		$c->condition('cond2', 'Book.Title like ?', '%bar%');
		$c->combine(array('cond1', 'cond2'), 'or');

		$sql = "SELECT  FROM `book` WHERE (book.TITLE <> :p1 OR book.TITLE like :p2)";
		$params = array(
			array('table' => 'book', 'column' => 'TITLE', 'value' => 'foo'),
			array('table' => 'book', 'column' => 'TITLE', 'value' => '%bar%'),
		);
		$this->assertCriteriaTranslation($c, $sql, $params, 'condition() can store condition for later combination');
	}

	public static function conditionsForTestWhere()
	{
		return array(
			array('Book.Title = ?', 'foo', 'book.TITLE = :p1', array(array('table' => 'book', 'column' => 'TITLE', 'value' => 'foo'))),
			array('Book.AuthorId = ?', 12, 'book.AUTHOR_ID = :p1', array(array('table' => 'book', 'column' => 'AUTHOR_ID', 'value' => 12))),
			array('Book.AuthorId IS NULL', null, 'book.AUTHOR_ID IS NULL', array()),
			array('Book.Id BETWEEN ? AND ?', array(3, 4), 'book.ID BETWEEN :p1 AND :p2', array(array('table' => 'book', 'column' => 'ID', 'value' => 3), array('table' => 'book', 'column' => 'ID', 'value' => 4))),
			array('Book.Id betWEen ? and ?', array(3, 4), 'book.ID betWEen :p1 and :p2', array(array('table' => 'book', 'column' => 'ID', 'value' => 3), array('table' => 'book', 'column' => 'ID', 'value' => 4))),
			array('Book.Id IN ?', array(1, 2, 3), 'book.ID IN (:p1,:p2,:p3)', array(array('table' => 'book', 'column' => 'ID', 'value' => 1), array('table' => 'book', 'column' => 'ID', 'value' => 2), array('table' => 'book', 'column' => 'ID', 'value' => 3))),
			array('Book.Id in ?', array(1, 2, 3), 'book.ID in (:p1,:p2,:p3)', array(array('table' => 'book', 'column' => 'ID', 'value' => 1), array('table' => 'book', 'column' => 'ID', 'value' => 2), array('table' => 'book', 'column' => 'ID', 'value' => 3))),
			array('Book.Id IN ?', array(), '1<>1', array()),
			array('Book.Id not in ?', array(), '1=1', array()),
			array('UPPER(Book.Title) = ?', 'foo', 'UPPER(book.TITLE) = :p1', array(array('table' => 'book', 'column' => 'TITLE', 'value' => 'foo'))),
			array('MATCH (Book.Title,Book.ISBN) AGAINST (?)', 'foo', 'MATCH (book.TITLE,book.ISBN) AGAINST (:p1)', array(array('table' => 'book', 'column' => 'TITLE', 'value' => 'foo'))),
		);
	}

	/**
	 * @dataProvider conditionsForTestWhere
	 */
	public function testWhere($clause, $value, $sql, $params)
	{
		$c = new ModelCriteria('bookstore', 'Book');
		$c->where($clause, $value);
		$sql = 'SELECT  FROM `book` WHERE ' . $sql;
		$this->assertCriteriaTranslation($c, $sql, $params, 'where() accepts a string clause');
	}

	public function testWhereUsesDefaultOperator()
	{
		$c = new ModelCriteria('bookstore', 'Book');
		$c->where('Book.Id = ?', 12);
		$c->_or();
		$c->where('Book.Title = ?', 'foo');
		$sql = 'SELECT  FROM `book` WHERE (book.ID = :p1 OR book.TITLE = :p2)';
		$params = array(
			array('table' => 'book', 'column' => 'ID', 'value' => '12'),
			array('table' => 'book', 'column' => 'TITLE', 'value' => 'foo'),
		);
		$this->assertCriteriaTranslation($c, $sql, $params, 'where() uses the default operator');
	}

	public function testWhereTwiceSameColumn()
	{
		$c = new ModelCriteria('bookstore', 'Book');
		$c->where('Book.Id IN ?', array(1, 2, 3));
		$c->where('Book.Id <> ?', 5);
		$params = array(
			array('table' => 'book', 'column' => 'ID', 'value' => '1'),
			array('table' => 'book', 'column' => 'ID', 'value' => '2'),
			array('table' => 'book', 'column' => 'ID', 'value' => '3'),
			array('table' => 'book', 'column' => 'ID', 'value' => '5'),
		);
		$sql = 'SELECT  FROM `book` WHERE (book.ID IN (:p1,:p2,:p3) AND book.ID <> :p4)';
		$this->assertCriteriaTranslation($c, $sql, $params, 'where() adds clauses on the same column correctly');
	}

	public function testWhereConditions()
	{
		$c = new ModelCriteria('bookstore', 'Book');
		$c->condition('cond1', 'Book.Title <> ?', 'foo');
		$c->condition('cond2', 'Book.Title like ?', '%bar%');
		$c->where(array('cond1', 'cond2'));

		$sql = "SELECT  FROM `book` WHERE (book.TITLE <> :p1 AND book.TITLE like :p2)";
		$params = array(
			array('table' => 'book', 'column' => 'TITLE', 'value' => 'foo'),
			array('table' => 'book', 'column' => 'TITLE', 'value' => '%bar%'),
		);
		$this->assertCriteriaTranslation($c, $sql, $params, 'where() accepts an array of named conditions');

		$c = new ModelCriteria('bookstore', 'Book');
		$c->condition('cond1', 'Book.Title <> ?', 'foo');
		$c->condition('cond2', 'Book.Title like ?', '%bar%');
		$c->where(array('cond1', 'cond2'), Criteria::LOGICAL_OR);

		$sql = "SELECT  FROM `book` WHERE (book.TITLE <> :p1 OR book.TITLE like :p2)";
		$this->assertCriteriaTranslation($c, $sql, $params, 'where() accepts an array of named conditions with operator');
	}

	public function testWhereNoReplacement()
	{
		$c = new ModelCriteria('bookstore', 'Book', 'b');
		$c->where('b.Title = ?', 'foo');
		$c->where('1=1');

		$sql = "SELECT  FROM `book` WHERE book.TITLE = :p1 AND 1=1";
		$params = array(
			array('table' => 'book', 'column' => 'TITLE', 'value' => 'foo'),
		);
		$this->assertCriteriaTranslation($c, $sql, $params, 'where() results in a Criteria::CUSTOM if no column name is matched');

		$c = new ModelCriteria('bookstore', 'Book');
		try {
			$c->where('b.Title = ?', 'foo');
			$this->fail('where() throws an exception when it finds a ? but cannot determine a column');
		} catch (PropelException $e) {
			$this->assertTrue(true, 'where() throws an exception when it finds a ? but cannot determine a column');
		}
	}

	public function testWhereFunction()
	{
		$c = new ModelCriteria('bookstore', 'Book', 'b');
		$c->where('UPPER(b.Title) = ?', 'foo');

		$sql = "SELECT  FROM `book` WHERE UPPER(book.TITLE) = :p1";
		$params = array(
			array('table' => 'book', 'column' => 'TITLE', 'value' => 'foo'),
		);
		$this->assertCriteriaTranslation($c, $sql, $params, 'where() accepts a complex calculation');
	}

	public function testOrWhere()
	{
		$c = new ModelCriteria('bookstore', 'Book');
		$c->where('Book.Title <> ?', 'foo');
		$c->orWhere('Book.Title like ?', '%bar%');

		$sql = "SELECT  FROM `book` WHERE (book.TITLE <> :p1 OR book.TITLE like :p2)";
		$params = array(
			array('table' => 'book', 'column' => 'TITLE', 'value' => 'foo'),
			array('table' => 'book', 'column' => 'TITLE', 'value' => '%bar%'),
		);
		$this->assertCriteriaTranslation($c, $sql, $params, 'orWhere() combines the clause with the previous one using  OR');
	}

	public function testOrWhereConditions()
	{
		$c = new ModelCriteria('bookstore', 'Book');
		$c->where('Book.Id = ?', 12);
		$c->condition('cond1', 'Book.Title <> ?', 'foo');
		$c->condition('cond2', 'Book.Title like ?', '%bar%');
		$c->orWhere(array('cond1', 'cond2'));

		$sql = "SELECT  FROM `book` WHERE (book.ID = :p1 OR (book.TITLE <> :p2 AND book.TITLE like :p3))";
		$params = array(
			array('table' => 'book', 'column' => 'ID', 'value' => 12),
			array('table' => 'book', 'column' => 'TITLE', 'value' => 'foo'),
			array('table' => 'book', 'column' => 'TITLE', 'value' => '%bar%'),
		);
		$this->assertCriteriaTranslation($c, $sql, $params, 'orWhere() accepts an array of named conditions');

		$c = new ModelCriteria('bookstore', 'Book');
		$c->where('Book.Id = ?', 12);
		$c->condition('cond1', 'Book.Title <> ?', 'foo');
		$c->condition('cond2', 'Book.Title like ?', '%bar%');
		$c->orWhere(array('cond1', 'cond2'), Criteria::LOGICAL_OR);

		$sql = "SELECT  FROM `book` WHERE (book.ID = :p1 OR (book.TITLE <> :p2 OR book.TITLE like :p3))";
		$this->assertCriteriaTranslation($c, $sql, $params, 'orWhere() accepts an array of named conditions with operator');
	}

	public function testMixedCriteria()
	{
		$c = new ModelCriteria('bookstore', 'Book');
		$c->where('Book.Title = ?', 'foo');
		$c->add(BookPeer::ID, array(1, 2), Criteria::IN);

		$sql = 'SELECT  FROM `book` WHERE book.TITLE = :p1 AND book.ID IN (:p2,:p3)';
		$params =  array(
			array('table' => 'book', 'column' => 'TITLE', 'value' => 'foo'),
			array('table' => 'book', 'column' => 'ID', 'value' => 1),
			array('table' => 'book', 'column' => 'ID', 'value' => 2)
		);
		$this->assertCriteriaTranslation($c, $sql, $params, 'ModelCriteria accepts Criteria operators');
	}

	public function testFilterBy()
	{
		$c = new ModelCriteria('bookstore', 'Book');
		$c->filterBy('Title', 'foo');

		$sql = 'SELECT  FROM `book` WHERE book.TITLE=:p1';
		$params =  array(
			array('table' => 'book', 'column' => 'TITLE', 'value' => 'foo'),
		);
		$this->assertCriteriaTranslation($c, $sql, $params, 'filterBy() accepts a simple column name');

		$c = new ModelCriteria('bookstore', 'Book');
		$c->filterBy('Title', 'foo', Criteria::NOT_EQUAL);

		$sql = 'SELECT  FROM `book` WHERE book.TITLE<>:p1';
		$params =  array(
			array('table' => 'book', 'column' => 'TITLE', 'value' => 'foo'),
		);
		$this->assertCriteriaTranslation($c, $sql, $params, 'filterBy() accepts a sicustom comparator');

		$c = new ModelCriteria('bookstore', 'Book', 'b');
		$c->filterBy('Title', 'foo');

		$sql = 'SELECT  FROM `book` WHERE book.TITLE=:p1';
		$params =  array(
			array('table' => 'book', 'column' => 'TITLE', 'value' => 'foo'),
		);
		$this->assertCriteriaTranslation($c, $sql, $params, 'filterBy() accepts a simple column name, even if initialized with an alias');
	}

	public function testHaving()
	{
		$c = new ModelCriteria('bookstore', 'Book');
		$c->having('Book.Title <> ?', 'foo');

		$sql = "SELECT  FROM  HAVING book.TITLE <> :p1";
		$params = array(
			array('table' => 'book', 'column' => 'TITLE', 'value' => 'foo'),
		);
		$this->assertCriteriaTranslation($c, $sql, $params, 'having() accepts a string clause');
	}

	public function testHavingConditions()
	{
		$c = new ModelCriteria('bookstore', 'Book');
		$c->condition('cond1', 'Book.Title <> ?', 'foo');
		$c->condition('cond2', 'Book.Title like ?', '%bar%');
		$c->having(array('cond1', 'cond2'));

		$sql = "SELECT  FROM  HAVING (book.TITLE <> :p1 AND book.TITLE like :p2)";
		$params = array(
			array('table' => 'book', 'column' => 'TITLE', 'value' => 'foo'),
			array('table' => 'book', 'column' => 'TITLE', 'value' => '%bar%'),
		);
		$this->assertCriteriaTranslation($c, $sql, $params, 'having() accepts an array of named conditions');

		$c = new ModelCriteria('bookstore', 'Book');
		$c->condition('cond1', 'Book.Title <> ?', 'foo');
		$c->condition('cond2', 'Book.Title like ?', '%bar%');
		$c->having(array('cond1', 'cond2'), Criteria::LOGICAL_OR);

		$sql = "SELECT  FROM  HAVING (book.TITLE <> :p1 OR book.TITLE like :p2)";
		$this->assertCriteriaTranslation($c, $sql, $params, 'having() accepts an array of named conditions with an operator');
	}

	public function testOrderBy()
	{
		$c = new ModelCriteria('bookstore', 'Book');
		$c->orderBy('Book.Title');

		$sql = 'SELECT  FROM  ORDER BY book.TITLE ASC';
		$params = array();
		$this->assertCriteriaTranslation($c, $sql, $params, 'orderBy() accepts a column name and adds an ORDER BY clause');

		$c = new ModelCriteria('bookstore', 'Book');
		$c->orderBy('Book.Title', 'desc');

		$sql = 'SELECT  FROM  ORDER BY book.TITLE DESC';
		$this->assertCriteriaTranslation($c, $sql, $params, 'orderBy() accepts an order parameter');

		$c = new ModelCriteria('bookstore', 'Book');
		try {
			$c->orderBy('Book.Foo');
			$this->fail('orderBy() throws an exception when called with an unkown column name');
		} catch (PropelException $e) {
			$this->assertTrue(true, 'orderBy() throws an exception when called with an unkown column name');
		}
		$c = new ModelCriteria('bookstore', 'Book');
		try {
			$c->orderBy('Book.Title', 'foo');
			$this->fail('orderBy() throws an exception when called with an unkown order');
		} catch (PropelException $e) {
			$this->assertTrue(true, 'orderBy() throws an exception when called with an unkown order');
		}
	}

	public function testOrderBySimpleColumn()
	{
		$c = new ModelCriteria('bookstore', 'Book');
		$c->orderBy('Title');

		$sql = 'SELECT  FROM  ORDER BY book.TITLE ASC';
		$params = array();
		$this->assertCriteriaTranslation($c, $sql, $params, 'orderBy() accepts a simple column name and adds an ORDER BY clause');
	}

	public function testOrderByAlias()
	{
		$c = new ModelCriteria('bookstore', 'Book');
		$c->addAsColumn('t', BookPeer::TITLE);
		$c->orderBy('t');

		$sql = 'SELECT book.TITLE AS t FROM  ORDER BY t ASC';
		$params = array();
		$this->assertCriteriaTranslation($c, $sql, $params, 'orderBy() accepts a column alias and adds an ORDER BY clause');
	}

	public function testGroupBy()
	{
		$c = new ModelCriteria('bookstore', 'Book');
		$c->groupBy('Book.AuthorId');

		$sql = 'SELECT  FROM  GROUP BY book.AUTHOR_ID';
		$params = array();
		$this->assertCriteriaTranslation($c, $sql, $params, 'groupBy() accepts a column name and adds a GROUP BY clause');

		$c = new ModelCriteria('bookstore', 'Book');
		try {
			$c->groupBy('Book.Foo');
			$this->fail('groupBy() throws an exception when called with an unkown column name');
		} catch (PropelException $e) {
			$this->assertTrue(true, 'groupBy() throws an exception when called with an unkown column name');
		}
	}

	public function testGroupBySimpleColumn()
	{
		$c = new ModelCriteria('bookstore', 'Book');
		$c->groupBy('AuthorId');

		$sql = 'SELECT  FROM  GROUP BY book.AUTHOR_ID';
		$params = array();
		$this->assertCriteriaTranslation($c, $sql, $params, 'groupBy() accepts a simple column name and adds a GROUP BY clause');
	}

	public function testGroupByAlias()
	{
		$c = new ModelCriteria('bookstore', 'Book');
		$c->addAsColumn('t', BookPeer::TITLE);
		$c->groupBy('t');

		$sql = 'SELECT book.TITLE AS t FROM  GROUP BY t';
		$params = array();
		$this->assertCriteriaTranslation($c, $sql, $params, 'groupBy() accepts a column alias and adds a GROUP BY clause');
	}

	/**
	 * @expectedException PropelException
	 */
	public function testGroupByClassThrowsExceptionOnUnkownClass()
	{
		$c = new ModelCriteria('bookstore', 'Book');
		$c->groupByClass('Author');
	}

	public function testGroupByClass()
	{
		$c = new ModelCriteria('bookstore', 'Book');
		$c->groupByClass('Book');

		$sql = 'SELECT  FROM  GROUP BY book.ID,book.TITLE,book.ISBN,book.PRICE,book.PUBLISHER_ID,book.AUTHOR_ID';
		$params = array();
		$this->assertCriteriaTranslation($c, $sql, $params, 'groupByClass() accepts a class name and adds a GROUP BY clause for all columns of the class');
	}

	public function testGroupByClassAlias()
	{
		$c = new ModelCriteria('bookstore', 'Book', 'b');
		$c->groupByClass('b');

		$sql = 'SELECT  FROM  GROUP BY book.ID,book.TITLE,book.ISBN,book.PRICE,book.PUBLISHER_ID,book.AUTHOR_ID';
		$params = array();
		$this->assertCriteriaTranslation($c, $sql, $params, 'groupByClass() accepts a class alias and adds a GROUP BY clause for all columns of the class');
	}

	public function testGroupByClassTrueAlias()
	{
		$c = new ModelCriteria('bookstore', 'Book');
		$c->setModelAlias('b', true);
		$c->groupByClass('b');

		$sql = 'SELECT  FROM  GROUP BY b.ID,b.TITLE,b.ISBN,b.PRICE,b.PUBLISHER_ID,b.AUTHOR_ID';
		$params = array();
		$this->assertCriteriaTranslation($c, $sql, $params, 'groupByClass() accepts a true class alias and adds a GROUP BY clause for all columns of the class');
	}

	public function testGroupByClassJoinedModel()
	{
		$c = new ModelCriteria('bookstore', 'Author');
		$c->join('Author.Book');
		$c->groupByClass('Book');

		$sql = 'SELECT  FROM `author` INNER JOIN `book` ON (author.ID=book.AUTHOR_ID) GROUP BY book.ID,book.TITLE,book.ISBN,book.PRICE,book.PUBLISHER_ID,book.AUTHOR_ID';
		$params = array();
		$this->assertCriteriaTranslation($c, $sql, $params, 'groupByClass() accepts the class name of a joined model');
	}

	public function testGroupByClassJoinedModelWithAlias()
	{
		$c = new ModelCriteria('bookstore', 'Author');
		$c->join('Author.Book b');
		$c->groupByClass('b');

		$sql = 'SELECT  FROM `author` INNER JOIN `book` `b` ON (author.ID=b.AUTHOR_ID) GROUP BY b.ID,b.TITLE,b.ISBN,b.PRICE,b.PUBLISHER_ID,b.AUTHOR_ID';
		$params = array();
		$this->assertCriteriaTranslation($c, $sql, $params, 'groupByClass() accepts the alias of a joined model');
	}

	public function testDistinct()
	{
		$c = new ModelCriteria('bookstore', 'Book');
		$c->distinct();
		$sql = 'SELECT DISTINCT  FROM ';
		$params = array();
		$this->assertCriteriaTranslation($c, $sql, $params, 'distinct() adds a DISTINCT clause');
	}

	public function testLimit()
	{
		$c = new ModelCriteria('bookstore', 'Book');
		$c->limit(10);
		$sql = 'SELECT  FROM  LIMIT 10';
		$params = array();
		$this->assertCriteriaTranslation($c, $sql, $params, 'limit() adds a LIMIT clause');
	}

	public function testOffset()
	{
		$c = new ModelCriteria('bookstore', 'Book');
		$c->limit(50);
		$c->offset(10);
		$sql = 'SELECT  FROM  LIMIT 10, 50';
		$params = array();
		$this->assertCriteriaTranslation($c, $sql, $params, 'offset() adds an OFFSET clause');
	}




	public function testAddJoin()
	{
		$c = new ModelCriteria('bookstore', 'Book');
		$c->addJoin(BookPeer::AUTHOR_ID, AuthorPeer::ID);
		$c->addJoin(BookPeer::PUBLISHER_ID, PublisherPeer::ID);
		$sql = 'SELECT  FROM `book` INNER JOIN `author` ON (book.AUTHOR_ID=author.ID) INNER JOIN `publisher` ON (book.PUBLISHER_ID=publisher.ID)';
		$params = array();
		$this->assertCriteriaTranslation($c, $sql, $params, 'addJoin() works the same as in Criteria');
	}

	public function testJoin()
	{
		$c = new ModelCriteria('bookstore', 'Book');
		$c->join('Book.Author');
		$sql = 'SELECT  FROM `book` INNER JOIN `author` ON (book.AUTHOR_ID=author.ID)';
		$params = array();
		$this->assertCriteriaTranslation($c, $sql, $params, 'join() uses a relation to guess the columns');

		$c = new ModelCriteria('bookstore', 'Book');
		try {
			$c->join('Book.Foo');
			$this->fail('join() throws an exception when called with a non-existing relation');
		} catch (PropelException $e) {
			$this->assertTrue(true, 'join() throws an exception when called with a non-existing relation');
		}

		$c = new ModelCriteria('bookstore', 'Book');
		$c->join('Book.Author');
		$c->where('Author.FirstName = ?', 'Leo');
		$sql = 'SELECT  FROM `book` INNER JOIN `author` ON (book.AUTHOR_ID=author.ID) WHERE author.FIRST_NAME = :p1';
		$params = array(
			array('table' => 'author', 'column' => 'FIRST_NAME', 'value' => 'Leo'),
		);
		$this->assertCriteriaTranslation($c, $sql, $params, 'join() uses a relation to guess the columns');

		$c = new ModelCriteria('bookstore', 'Book');
		$c->join('Author');
		$c->where('Author.FirstName = ?', 'Leo');
		$sql = 'SELECT  FROM `book` INNER JOIN `author` ON (book.AUTHOR_ID=author.ID) WHERE author.FIRST_NAME = :p1';
		$params = array(
			array('table' => 'author', 'column' => 'FIRST_NAME', 'value' => 'Leo'),
		);
		$this->assertCriteriaTranslation($c, $sql, $params, 'join() uses the current model name when given a simple relation name');
	}

	public function testJoinQuery()
	{
		$con = Propel::getConnection(BookPeer::DATABASE_NAME);
		BookstoreDataPopulator::depopulate($con);
		BookstoreDataPopulator::populate($con);

		$c = new ModelCriteria('bookstore', 'Book');
		$c->join('Book.Author');
		$c->where('Author.FirstName = ?', 'Neal');
		$books = BookPeer::doSelect($c);
		$expectedSQL = "SELECT book.ID, book.TITLE, book.ISBN, book.PRICE, book.PUBLISHER_ID, book.AUTHOR_ID FROM `book` INNER JOIN `author` ON (book.AUTHOR_ID=author.ID) WHERE author.FIRST_NAME = 'Neal'";
		$this->assertEquals($expectedSQL, $con->getLastExecutedQuery(), 'join() issues a real JOIN query');
		$this->assertEquals(1, count($books), 'join() issues a real JOIN query');
	}

	public function testJoinRelationName()
	{
		$c = new ModelCriteria('bookstore', 'BookstoreEmployee');
		$c->join('BookstoreEmployee.Supervisor');
		$sql = 'SELECT  FROM  INNER JOIN `bookstore_employee` ON (bookstore_employee.SUPERVISOR_ID=bookstore_employee.ID)';
		$params = array();
		$this->assertCriteriaTranslation($c, $sql, $params, 'join() uses relation names as defined in schema.xml');
	}

	public function testJoinComposite()
	{
		$c = new ModelCriteria('bookstore', 'ReaderFavorite');
		$c->join('ReaderFavorite.BookOpinion');
		$sql = 'SELECT  FROM `reader_favorite` INNER JOIN `book_opinion` ON (reader_favorite.BOOK_ID=book_opinion.BOOK_ID AND reader_favorite.READER_ID=book_opinion.READER_ID)';
		$params = array();
		$this->assertCriteriaTranslation($c, $sql, $params, 'join() knows how to create a JOIN clause for relationships with composite fkeys');
	}

	public function testJoinType()
	{
		$c = new ModelCriteria('bookstore', 'Book');
		$c->join('Book.Author');
		$sql = 'SELECT  FROM `book` INNER JOIN `author` ON (book.AUTHOR_ID=author.ID)';
		$params = array();
		$this->assertCriteriaTranslation($c, $sql, $params, 'join() adds an INNER JOIN by default');

		$c = new ModelCriteria('bookstore', 'Book');
		$c->join('Book.Author', Criteria::INNER_JOIN);
		$sql = 'SELECT  FROM `book` INNER JOIN `author` ON (book.AUTHOR_ID=author.ID)';
		$params = array();
		$this->assertCriteriaTranslation($c, $sql, $params, 'join() adds an INNER JOIN by default');

		$c = new ModelCriteria('bookstore', 'Book');
		$c->join('Book.Author', Criteria::LEFT_JOIN);
		$sql = 'SELECT  FROM `book` LEFT JOIN `author` ON (book.AUTHOR_ID=author.ID)';
		$params = array();
		$this->assertCriteriaTranslation($c, $sql, $params, 'join() can add a LEFT JOIN');

		$c = new ModelCriteria('bookstore', 'Book');
		$c->join('Book.Author', Criteria::RIGHT_JOIN);
		$sql = 'SELECT  FROM `book` RIGHT JOIN `author` ON (book.AUTHOR_ID=author.ID)';
		$params = array();
		$this->assertCriteriaTranslation($c, $sql, $params, 'join() can add a RIGHT JOIN');

		$c = new ModelCriteria('bookstore', 'Book');
		$c->join('Book.Author', 'incorrect join');
		$sql = 'SELECT  FROM `book` incorrect join `author` ON (book.AUTHOR_ID=author.ID)';
		$params = array();
		$this->assertCriteriaTranslation($c, $sql, $params, 'join() accepts any join string');
	}

	public function testJoinDirection()
	{
		$c = new ModelCriteria('bookstore', 'Book');
		$c->join('Book.Author');
		$sql = 'SELECT  FROM `book` INNER JOIN `author` ON (book.AUTHOR_ID=author.ID)';
		$params = array();
		$this->assertCriteriaTranslation($c, $sql, $params, 'join() adds a JOIN clause correctly for many to one relationship');

		$c = new ModelCriteria('bookstore', 'Author');
		$c->join('Author.Book');
		$sql = 'SELECT  FROM `author` INNER JOIN `book` ON (author.ID=book.AUTHOR_ID)';
		$params = array();
		$this->assertCriteriaTranslation($c, $sql, $params, 'join() adds a JOIN clause correctly for one to many relationship');

		$c = new ModelCriteria('bookstore', 'BookstoreEmployee');
		$c->join('BookstoreEmployee.BookstoreEmployeeAccount');
		$sql = 'SELECT  FROM `bookstore_employee` INNER JOIN `bookstore_employee_account` ON (bookstore_employee.ID=bookstore_employee_account.EMPLOYEE_ID)';
		$params = array();
		$this->assertCriteriaTranslation($c, $sql, $params, 'join() adds a JOIN clause correctly for one to one relationship');

		$c = new ModelCriteria('bookstore', 'BookstoreEmployeeAccount');
		$c->join('BookstoreEmployeeAccount.BookstoreEmployee');
		$sql = 'SELECT  FROM `bookstore_employee_account` INNER JOIN `bookstore_employee` ON (bookstore_employee_account.EMPLOYEE_ID=bookstore_employee.ID)';
		$params = array();
		$this->assertCriteriaTranslation($c, $sql, $params, 'join() adds a JOIN clause correctly for one to one relationship');
	}

	public function testJoinSeveral()
	{
		$c = new ModelCriteria('bookstore', 'Author');
		$c->join('Author.Book');
		$c->join('Book.Publisher');
		$c->where('Publisher.Name = ?', 'foo');
		$sql = 'SELECT  FROM `author` INNER JOIN `book` ON (author.ID=book.AUTHOR_ID) INNER JOIN `publisher` ON (book.PUBLISHER_ID=publisher.ID) WHERE publisher.NAME = :p1';
		$params = array(
			array('table' => 'publisher', 'column' => 'NAME', 'value' => 'foo'),
		);
		$this->assertCriteriaTranslation($c, $sql, $params, 'join() can guess relationships from related tables');
	}

	public function testJoinAlias()
	{
		$c = new ModelCriteria('bookstore', 'Book', 'b');
		$c->join('b.Author');
		$sql = 'SELECT  FROM `book` INNER JOIN `author` ON (book.AUTHOR_ID=author.ID)';
		$params = array();
		$this->assertCriteriaTranslation($c, $sql, $params, 'join() supports relation on main alias');

		$c = new ModelCriteria('bookstore', 'Book', 'b');
		$c->join('Author');
		$sql = 'SELECT  FROM `book` INNER JOIN `author` ON (book.AUTHOR_ID=author.ID)';
		$params = array();
		$this->assertCriteriaTranslation($c, $sql, $params, 'join() can use a simple relation name when the model has an alias');

		$c = new ModelCriteria('bookstore', 'Book');
		$c->join('Book.Author a');
		$sql = 'SELECT  FROM `book` INNER JOIN `author` `a` ON (book.AUTHOR_ID=a.ID)';
		$params = array();
		$this->assertCriteriaTranslation($c, $sql, $params, 'join() supports relation alias');

		$c = new ModelCriteria('bookstore', 'Book', 'b');
		$c->join('b.Author a');
		$sql = 'SELECT  FROM `book` INNER JOIN `author` `a` ON (book.AUTHOR_ID=a.ID)';
		$params = array();
		$this->assertCriteriaTranslation($c, $sql, $params, 'join() supports relation alias on main alias');

		$con = Propel::getConnection(BookPeer::DATABASE_NAME);
		$c = new ModelCriteria('bookstore', 'Book', 'b');
		$c->join('b.Author a');
		$c->where('a.FirstName = ?', 'Leo');
		$sql = 'SELECT  FROM `book` INNER JOIN `author` `a` ON (book.AUTHOR_ID=a.ID) WHERE a.FIRST_NAME = :p1';
		$params = array(
			array('table' => 'author', 'column' => 'FIRST_NAME', 'value' => 'Leo'),
		);
		$this->assertCriteriaTranslation($c, $sql, $params, 'join() allows the use of relation alias in where()');

		$c = new ModelCriteria('bookstore', 'Author', 'a');
		$c->join('a.Book b');
		$c->join('b.Publisher p');
		$c->where('p.Name = ?', 'foo');
		$sql = 'SELECT  FROM `author` INNER JOIN `book` `b` ON (author.ID=b.AUTHOR_ID) INNER JOIN `publisher` `p` ON (b.PUBLISHER_ID=p.ID) WHERE p.NAME = :p1';
		$params = array(
			array('table' => 'publisher', 'column' => 'NAME', 'value' => 'foo'),
		);
		$this->assertCriteriaTranslation($c, $sql, $params, 'join() allows the use of relation alias in further join()');
	}

	public function testJoinTrueTableAlias()
	{
		$c = new ModelCriteria('bookstore', 'Book');
		$c->setModelAlias('b', true);
		$c->join('b.Author');
		$sql = 'SELECT  FROM `book` `b` INNER JOIN `author` ON (b.AUTHOR_ID=author.ID)';
		$params = array();
		$this->assertCriteriaTranslation($c, $sql, $params, 'join() supports relation on true table alias');

		$c = new ModelCriteria('bookstore', 'Book');
		$c->setModelAlias('b', true);
		$c->join('Author');
		$sql = 'SELECT  FROM `book` `b` INNER JOIN `author` ON (b.AUTHOR_ID=author.ID)';
		$params = array();
		$this->assertCriteriaTranslation($c, $sql, $params, 'join() supports relation without alias name on true table alias');
	}

	public function testJoinOnSameTable()
	{
		$c = new ModelCriteria('bookstore', 'BookstoreEmployee', 'be');
		$c->join('be.Supervisor sup');
		$c->join('sup.Subordinate sub');
		$c->where('sub.Name = ?', 'Foo');
		$sql = 'SELECT  FROM `bookstore_employee` INNER JOIN `bookstore_employee` `sup` ON (bookstore_employee.SUPERVISOR_ID=sup.ID) INNER JOIN `bookstore_employee` `sub` ON (sup.ID=sub.SUPERVISOR_ID) WHERE sub.NAME = :p1';
		$params = array(
			array('table' => 'bookstore_employee', 'column' => 'NAME', 'value' => 'Foo'),
		);
		$this->assertCriteriaTranslation($c, $sql, $params, 'join() allows two joins on the same table thanks to aliases');
	}

	public function testJoinAliasQuery()
	{
		$con = Propel::getConnection(BookPeer::DATABASE_NAME);
		$c = new ModelCriteria('bookstore', 'Book', 'b');
		$c->join('b.Author a');
		$c->where('a.FirstName = ?', 'Leo');
		$books = BookPeer::doSelect($c, $con);
		$expectedSQL = "SELECT book.ID, book.TITLE, book.ISBN, book.PRICE, book.PUBLISHER_ID, book.AUTHOR_ID FROM `book` INNER JOIN `author` `a` ON (book.AUTHOR_ID=a.ID) WHERE a.FIRST_NAME = 'Leo'";
		$this->assertEquals($expectedSQL, $con->getLastExecutedQuery(), 'join() allows the use of relation alias in where()');

		$c = new ModelCriteria('bookstore', 'BookstoreEmployee', 'be');
		$c->join('be.Supervisor sup');
		$c->join('sup.Subordinate sub');
		$c->where('sub.Name = ?', 'Foo');
		$employees = BookstoreEmployeePeer::doSelect($c, $con);
		$expectedSQL = "SELECT bookstore_employee.ID, bookstore_employee.CLASS_KEY, bookstore_employee.NAME, bookstore_employee.JOB_TITLE, bookstore_employee.SUPERVISOR_ID FROM `bookstore_employee` INNER JOIN `bookstore_employee` `sup` ON (bookstore_employee.SUPERVISOR_ID=sup.ID) INNER JOIN `bookstore_employee` `sub` ON (sup.ID=sub.SUPERVISOR_ID) WHERE sub.NAME = 'Foo'";
		$this->assertEquals($expectedSQL, $con->getLastExecutedQuery(), 'join() allows the use of relation alias in further joins()');
	}

	public function testAddJoinConditionSimple()
	{
		$con = Propel::getConnection(BookPeer::DATABASE_NAME);
		$c = new ModelCriteria('bookstore', 'Book');
		$c->join('Book.Author', Criteria::INNER_JOIN);
		$c->addJoinCondition('Author', 'Book.Title IS NOT NULL');
		$books = BookPeer::doSelect($c, $con);
		$expectedSQL = "SELECT book.ID, book.TITLE, book.ISBN, book.PRICE, book.PUBLISHER_ID, book.AUTHOR_ID FROM `book` INNER JOIN `author` ON (book.AUTHOR_ID=author.ID AND book.TITLE IS NOT NULL)";
		$this->assertEquals($expectedSQL, $con->getLastExecutedQuery(), 'addJoinCondition() allows the use of custom conditions');
	}

	public function testAddJoinConditionBinding()
	{
		$con = Propel::getConnection(BookPeer::DATABASE_NAME);
		$c = new ModelCriteria('bookstore', 'Book');
		$c->join('Book.Author', Criteria::INNER_JOIN);
		$c->addJoinCondition('Author', 'Book.Title = ?', 'foo');
		$books = BookPeer::doSelect($c, $con);
		$expectedSQL = "SELECT book.ID, book.TITLE, book.ISBN, book.PRICE, book.PUBLISHER_ID, book.AUTHOR_ID FROM `book` INNER JOIN `author` ON (book.AUTHOR_ID=author.ID AND book.TITLE = 'foo')";
		$this->assertEquals($expectedSQL, $con->getLastExecutedQuery(), 'addJoinCondition() allows the use of custom conditions with values to bind');
	}

	public function testAddJoinConditionSeveral()
	{
		$con = Propel::getConnection(BookPeer::DATABASE_NAME);
		$c = new ModelCriteria('bookstore', 'Book');
		$c->join('Book.Author', Criteria::INNER_JOIN);
		$c->addJoinCondition('Author', 'Book.Title = ?', 'foo');
		$c->addJoinCondition('Author', 'Book.ISBN IS NOT NULL');
		$books = BookPeer::doSelect($c, $con);
		$expectedSQL = "SELECT book.ID, book.TITLE, book.ISBN, book.PRICE, book.PUBLISHER_ID, book.AUTHOR_ID FROM `book` INNER JOIN `author` ON ((book.AUTHOR_ID=author.ID AND book.TITLE = 'foo') AND book.ISBN IS NOT NULL)";
		$this->assertEquals($expectedSQL, $con->getLastExecutedQuery(), 'addJoinCondition() allows the use of several custom conditions');
	}

	public function testAddJoinConditionBindingAndWhere()
	{
		$con = Propel::getConnection(BookPeer::DATABASE_NAME);
		$c = new ModelCriteria('bookstore', 'Book');
		$c->where('Book.Title LIKE ?', 'foo%');
		$c->join('Book.Author', Criteria::INNER_JOIN);
		$c->addJoinCondition('Author', 'Book.Title = ?', 'foo');
		$books = BookPeer::doSelect($c, $con);
		$expectedSQL = "SELECT book.ID, book.TITLE, book.ISBN, book.PRICE, book.PUBLISHER_ID, book.AUTHOR_ID FROM `book` INNER JOIN `author` ON (book.AUTHOR_ID=author.ID AND book.TITLE = 'foo') WHERE book.TITLE LIKE 'foo%'";
		$this->assertEquals($expectedSQL, $con->getLastExecutedQuery(), 'addJoinCondition() allows the use of custom conditions with values and lives well with WHERE conditions');
	}

	public function testAddJoinConditionAlias()
	{
		$con = Propel::getConnection(BookPeer::DATABASE_NAME);
		$c = new ModelCriteria('bookstore', 'Book');
		$c->join('Book.Author a', Criteria::INNER_JOIN);
		$c->addJoinCondition('a', 'Book.Title IS NOT NULL');
		$books = BookPeer::doSelect($c, $con);
		$expectedSQL = "SELECT book.ID, book.TITLE, book.ISBN, book.PRICE, book.PUBLISHER_ID, book.AUTHOR_ID FROM `book` INNER JOIN `author` `a` ON (book.AUTHOR_ID=a.ID AND book.TITLE IS NOT NULL)";
		$this->assertEquals($expectedSQL, $con->getLastExecutedQuery(), 'addJoinCondition() allows the use of custom conditions even on aliased relations');
	}

	public function testAddJoinConditionOperator()
	{
		$con = Propel::getConnection(BookPeer::DATABASE_NAME);
		$c = new ModelCriteria('bookstore', 'Book');
		$c->join('Book.Author', Criteria::INNER_JOIN);
		$c->addJoinCondition('Author', 'Book.Title IS NOT NULL', null, Criteria::LOGICAL_OR);
		$books = BookPeer::doSelect($c, $con);
		$expectedSQL = "SELECT book.ID, book.TITLE, book.ISBN, book.PRICE, book.PUBLISHER_ID, book.AUTHOR_ID FROM `book` INNER JOIN `author` ON (book.AUTHOR_ID=author.ID OR book.TITLE IS NOT NULL)";
		$this->assertEquals($expectedSQL, $con->getLastExecutedQuery(), 'addJoinCondition() allows the use of custom conditions with a custom operator');
	}

	public function testSetJoinConditionCriterion()
	{
		$con = Propel::getConnection(BookPeer::DATABASE_NAME);
		$c = new ModelCriteria('bookstore', 'Book');
		$c->join('Book.Author', Criteria::INNER_JOIN);
		$criterion = $c->getNewCriterion(BookPeer::TITLE, BookPeer::TITLE . ' = ' . AuthorPeer::FIRST_NAME, Criteria::CUSTOM);
		$c->setJoinCondition('Author', $criterion);
		$books = BookPeer::doSelect($c, $con);
		$expectedSQL = "SELECT book.ID, book.TITLE, book.ISBN, book.PRICE, book.PUBLISHER_ID, book.AUTHOR_ID FROM `book` INNER JOIN `author` ON book.TITLE = author.FIRST_NAME";
		$this->assertEquals($expectedSQL, $con->getLastExecutedQuery(), 'setJoinCondition() can override a previous join condition with a Criterion');
	}

	public function testSetJoinConditionNamedCondition()
	{
		$con = Propel::getConnection(BookPeer::DATABASE_NAME);
		$c = new ModelCriteria('bookstore', 'Book');
		$c->join('Book.Author', Criteria::INNER_JOIN);
		$c->condition('cond1', 'Book.Title = Author.FirstName');
		$c->setJoinCondition('Author', 'cond1');
		$books = BookPeer::doSelect($c, $con);
		$expectedSQL = "SELECT book.ID, book.TITLE, book.ISBN, book.PRICE, book.PUBLISHER_ID, book.AUTHOR_ID FROM `book` INNER JOIN `author` ON book.TITLE = author.FIRST_NAME";
		$this->assertEquals($expectedSQL, $con->getLastExecutedQuery(), 'setJoinCondition() can override a previous join condition with a named condition');
	}

	public function testGetJoin()
	{
		$c = new ModelCriteria('bookstore', 'Book');
		$c->join('Book.Author');

		$joins = $c->getJoins();
		$this->assertEquals($joins['Author'], $c->getJoin('Author'), "getJoin() returns a specific Join from the ModelCriteria");
	}

	public function testWith()
	{
		$c = new TestableModelCriteria('bookstore', 'Book');
		$c->join('Book.Author');
		$c->with('Author');
		$withs = $c->getWith();
		$this->assertTrue(array_key_exists('Author', $withs), 'with() adds an entry to the internal list of Withs');
		$this->assertTrue($withs['Author'] instanceof ModelWith, 'with() references the ModelWith object');
	}

	/**
	 * @expectedException PropelException
	 */
	public function testWithThrowsExceptionWhenJoinLacks()
	{
		$c = new ModelCriteria('bookstore', 'Book');
		$c->with('Author');
	}

	public function testWithAlias()
	{
		$c = new TestableModelCriteria('bookstore', 'Book');
		$c->join('Book.Author a');
		$c->with('a');
		$withs = $c->getWith();
		$this->assertTrue(array_key_exists('a', $withs), 'with() uses the alias for the index of the internal list of Withs');
	}

	/**
	 * @expectedException PropelException
	 */
	public function testWithThrowsExceptionWhenNotUsingAlias()
	{
		$c = new ModelCriteria('bookstore', 'Book');
		$c->join('Book.Author a');
		$c->with('Author');
	}

	public function testWithAddsSelectColumns()
	{
		$c = new TestableModelCriteria('bookstore', 'Book');
		BookPeer::addSelectColumns($c);
		$c->join('Book.Author');
		$c->with('Author');
		$expectedColumns = array(
			BookPeer::ID,
			BookPeer::TITLE,
			BookPeer::ISBN,
			BookPeer::PRICE,
			BookPeer::PUBLISHER_ID,
			BookPeer::AUTHOR_ID,
			AuthorPeer::ID,
			AuthorPeer::FIRST_NAME,
			AuthorPeer::LAST_NAME,
			AuthorPeer::EMAIL,
			AuthorPeer::AGE
		);
		$this->assertEquals($expectedColumns, $c->getSelectColumns(), 'with() adds the columns of the related table');
	}

	public function testWithAliasAddsSelectColumns()
	{
		$c = new TestableModelCriteria('bookstore', 'Book');
		BookPeer::addSelectColumns($c);
		$c->join('Book.Author a');
		$c->with('a');
		$expectedColumns = array(
			BookPeer::ID,
			BookPeer::TITLE,
			BookPeer::ISBN,
			BookPeer::PRICE,
			BookPeer::PUBLISHER_ID,
			BookPeer::AUTHOR_ID,
			'a.ID',
			'a.FIRST_NAME',
			'a.LAST_NAME',
			'a.EMAIL',
			'a.AGE'
		);
		$this->assertEquals($expectedColumns, $c->getSelectColumns(), 'with() adds the columns of the related table');
	}

	public function testWithAddsSelectColumnsOfMainTable()
	{
		$c = new TestableModelCriteria('bookstore', 'Book');
		$c->join('Book.Author');
		$c->with('Author');
		$expectedColumns = array(
			BookPeer::ID,
			BookPeer::TITLE,
			BookPeer::ISBN,
			BookPeer::PRICE,
			BookPeer::PUBLISHER_ID,
			BookPeer::AUTHOR_ID,
			AuthorPeer::ID,
			AuthorPeer::FIRST_NAME,
			AuthorPeer::LAST_NAME,
			AuthorPeer::EMAIL,
			AuthorPeer::AGE
		);
		$this->assertEquals($expectedColumns, $c->getSelectColumns(), 'with() adds the columns of the main table if required');
	}

	public function testWithAliasAddsSelectColumnsOfMainTable()
	{
		$c = new TestableModelCriteria('bookstore', 'Book');
		$c->setModelAlias('b', true);
		$c->join('b.Author a');
		$c->with('a');
		$expectedColumns = array(
			'b.ID',
			'b.TITLE',
			'b.ISBN',
			'b.PRICE',
			'b.PUBLISHER_ID',
			'b.AUTHOR_ID',
			'a.ID',
			'a.FIRST_NAME',
			'a.LAST_NAME',
			'a.EMAIL',
			'a.AGE'
		);
		$this->assertEquals($expectedColumns, $c->getSelectColumns(), 'with() adds the columns of the main table with an alias if required');
	}

	public function testWithOneToManyAddsSelectColumns()
	{
		$c = new TestableModelCriteria('bookstore', 'Author');
		AuthorPeer::addSelectColumns($c);
		$c->leftJoin('Author.Book');
		$c->with('Book');
		$expectedColumns = array(
			AuthorPeer::ID,
			AuthorPeer::FIRST_NAME,
			AuthorPeer::LAST_NAME,
			AuthorPeer::EMAIL,
			AuthorPeer::AGE,
			BookPeer::ID,
			BookPeer::TITLE,
			BookPeer::ISBN,
			BookPeer::PRICE,
			BookPeer::PUBLISHER_ID,
			BookPeer::AUTHOR_ID,
		);
		$this->assertEquals($expectedColumns, $c->getSelectColumns(), 'with() adds the columns of the related table even in a one-to-many relationship');
	}

	public function testJoinWith()
	{
		$c = new TestableModelCriteria('bookstore', 'Book');
		$c->joinWith('Book.Author');
		$expectedColumns = array(
			BookPeer::ID,
			BookPeer::TITLE,
			BookPeer::ISBN,
			BookPeer::PRICE,
			BookPeer::PUBLISHER_ID,
			BookPeer::AUTHOR_ID,
			AuthorPeer::ID,
			AuthorPeer::FIRST_NAME,
			AuthorPeer::LAST_NAME,
			AuthorPeer::EMAIL,
			AuthorPeer::AGE
		);
		$this->assertEquals($expectedColumns, $c->getSelectColumns(), 'joinWith() adds the join');
		$joins = $c->getJoins();
		$join = $joins['Author'];
		$this->assertEquals(Criteria::INNER_JOIN, $join->getJoinType(), 'joinWith() adds an INNER JOIN by default');
	}

	public function testJoinWithType()
	{
		$c = new TestableModelCriteria('bookstore', 'Book');
		$c->joinWith('Book.Author', Criteria::LEFT_JOIN);
		$joins = $c->getJoins();
		$join = $joins['Author'];
		$this->assertEquals(Criteria::LEFT_JOIN, $join->getJoinType(), 'joinWith() accepts a join type as second parameter');
	}

	public function testJoinWithAlias()
	{
		$c = new TestableModelCriteria('bookstore', 'Book');
		$c->joinWith('Book.Author a');
		$expectedColumns = array(
			BookPeer::ID,
			BookPeer::TITLE,
			BookPeer::ISBN,
			BookPeer::PRICE,
			BookPeer::PUBLISHER_ID,
			BookPeer::AUTHOR_ID,
			'a.ID',
			'a.FIRST_NAME',
			'a.LAST_NAME',
			'a.EMAIL',
			'a.AGE'
		);
		$this->assertEquals($expectedColumns, $c->getSelectColumns(), 'joinWith() adds the join with the alias');
	}

	public function testJoinWithSeveral()
	{
		$c = new TestableModelCriteria('bookstore', 'Review');
		$c->joinWith('Review.Book');
		$c->joinWith('Book.Author');
		$c->joinWith('Book.Publisher');
		$expectedColumns = array(
			ReviewPeer::ID,
			ReviewPeer::REVIEWED_BY,
			ReviewPeer::REVIEW_DATE,
			ReviewPeer::RECOMMENDED,
			ReviewPeer::STATUS,
			ReviewPeer::BOOK_ID,
			BookPeer::ID,
			BookPeer::TITLE,
			BookPeer::ISBN,
			BookPeer::PRICE,
			BookPeer::PUBLISHER_ID,
			BookPeer::AUTHOR_ID,
			AuthorPeer::ID,
			AuthorPeer::FIRST_NAME,
			AuthorPeer::LAST_NAME,
			AuthorPeer::EMAIL,
			AuthorPeer::AGE,
			PublisherPeer::ID,
			PublisherPeer::NAME
		);
		$this->assertEquals($expectedColumns, $c->getSelectColumns(), 'joinWith() adds the with');
		$joins = $c->getJoins();
		$expectedJoinKeys = array('Book', 'Author', 'Publisher');
		$this->assertEquals($expectedJoinKeys, array_keys($joins), 'joinWith() adds the join');
	}

	public function testJoinWithTwice()
	{
		$c = new TestableModelCriteria('bookstore', 'Book');
		$c->join('Book.Review');
		$c->joinWith('Book.Author');
		$c->joinWith('Book.Review');
		$expectedColumns = array(
			BookPeer::ID,
			BookPeer::TITLE,
			BookPeer::ISBN,
			BookPeer::PRICE,
			BookPeer::PUBLISHER_ID,
			BookPeer::AUTHOR_ID,
			AuthorPeer::ID,
			AuthorPeer::FIRST_NAME,
			AuthorPeer::LAST_NAME,
			AuthorPeer::EMAIL,
			AuthorPeer::AGE,
			ReviewPeer::ID,
			ReviewPeer::REVIEWED_BY,
			ReviewPeer::REVIEW_DATE,
			ReviewPeer::RECOMMENDED,
			ReviewPeer::STATUS,
			ReviewPeer::BOOK_ID,
		);
		$this->assertEquals($expectedColumns, $c->getSelectColumns(), 'joinWith() adds the with');
		$joins = $c->getJoins();
		$expectedJoinKeys = array('Review', 'Author');
		$this->assertEquals($expectedJoinKeys, array_keys($joins), 'joinWith() adds the join');
	}

	public static function conditionsForTestWithColumn()
	{
		return array(
			array('Book.Title', 'BookTitle', 'book.TITLE AS BookTitle'),
			array('Book.Title', null, 'book.TITLE AS BookTitle'),
			array('UPPER(Book.Title)', null, 'UPPER(book.TITLE) AS UPPERBookTitle'),
			array('CONCAT(Book.Title, Book.ISBN)', 'foo', 'CONCAT(book.TITLE, book.ISBN) AS foo'),
		);
	}

	/**
	 * @dataProvider conditionsForTestWithColumn
	 */
	public function testWithColumn($clause, $alias, $selectTranslation)
	{
		$c = new ModelCriteria('bookstore', 'Book');
		$c->withColumn($clause, $alias);
		$sql = 'SELECT book.ID, book.TITLE, book.ISBN, book.PRICE, book.PUBLISHER_ID, book.AUTHOR_ID, ' . $selectTranslation . ' FROM `book`';
		$params = array();
		$this->assertCriteriaTranslation($c, $sql, $params, 'withColumn() adds a calculated column to the select clause');
	}

	public static function conditionsForTestWithColumnAndQuotes()
	{
		return array(
			// Examples for simple string concatenation needed for MSSQL.
			// MSSQL has no CONCAT() function so uses + to join strings.
			array("CONVERT(varchar, Author.Age, 120) + \' GMT\'", 'GMTCreatedAt', "CONVERT(varchar, author.AGE, 120) + \' GMT\' AS GMTCreatedAt"),
			array("(Author.FirstName + ' ' + Author.LastName)", 'AuthorFullname', "(author.FIRST_NAME + ' ' + author.LAST_NAME) AS AuthorFullname"),
			array("('\"' + Author.FirstName + ' ' + Author.LastName + '\"')", 'QuotedAuthorFullname', "('\"' + author.FIRST_NAME + ' ' + author.LAST_NAME + '\"') AS QuotedAuthorFullname"),

			//