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
 */

namespace Propel\Tests\Runtime\Query;

use Propel\Tests\Helpers\Bookstore\BookstoreTestBase;
use Propel\Tests\Helpers\Bookstore\BookstoreDataPopulator;

use Propel\Tests\Bookstore\Author;
use Propel\Tests\Bookstore\AuthorPeer;
use Propel\Tests\Bookstore\AuthorQuery;
use Propel\Tests\Bookstore\Book;
use Propel\Tests\Bookstore\BookPeer;
use Propel\Tests\Bookstore\BookQuery;
use Propel\Tests\Bookstore\Book2;
use Propel\Tests\Bookstore\Book2Query;
use Propel\Tests\Bookstore\BookstoreEmployeePeer;
use Propel\Tests\Bookstore\Publisher;
use Propel\Tests\Bookstore\PublisherPeer;
use Propel\Tests\Bookstore\ReviewPeer;

use Propel\Runtime\Propel;
use Propel\Runtime\Collection\Collection;
use Propel\Runtime\Collection\ObjectCollection;
use Propel\Runtime\Exception\InvalidArgumentException;
use Propel\Runtime\Exception\LogicException;
use Propel\Runtime\Exception\PropelException;
use Propel\Runtime\Exception\UnexpectedValueException;
use Propel\Runtime\Formatter\AbstractFormatter;
use Propel\Runtime\Formatter\StatementFormatter;
use Propel\Runtime\Util\BasePeer;
use Propel\Runtime\Util\PropelModelPager;
use Propel\Runtime\Query\Criteria;
use Propel\Runtime\Query\Exception\UnknownColumnException;
use Propel\Runtime\Query\Exception\UnknownModelException;
use Propel\Runtime\Query\Exception\UnknownRelationException;
use Propel\Runtime\Query\ModelCriteria;

use \PDO;

/**
 * Test class for ModelCriteria.
 *
 * @author     Francois Zaninotto
 * @version    $Id$
 */
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', 'Propel\Tests\Bookstore\Book');
        $this->assertEquals('Propel\Tests\Bookstore\Book', $c->getModelName(), 'getModelName() returns the name of the class associated to the model class');
    }

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

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

        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $c->setFormatter(ModelCriteria::FORMAT_STATEMENT);
        $this->assertTrue($c->getFormatter() instanceof StatementFormatter, 'setFormatter() accepts the name of a AbstractFormatter class');

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

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

    }

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

            //without NS
            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'"),

        );
    }

    /**
     * @dataProvider conditionsForTestReplaceNames
     */
    public function testReplaceNames($origClause, $columnPhpName = false, $modifiedClause)
    {
        $c = new TestableModelCriteria('bookstore', 'Propel\Tests\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);
    }

    static public function conditionsForTestReplaceMultipleNames()
    {
        return array(
            array('(Propel\Tests\Bookstore\Book.Id+Book.Id)=1', array('Id', 'Id'), '(book.ID+book.ID)=1'), // match multiple names
            array('CONCAT(Propel\Tests\Bookstore\Book.Title,"Book.Id")= ?', array('Title', 'Id'), 'CONCAT(book.TITLE,"Book.Id")= ?'), // ignore names in strings
            array('CONCAT(Propel\Tests\Bookstore\Book.Title," Book.Id ")= ?', array('Title', 'Id'), 'CONCAT(book.TITLE," Book.Id ")= ?'), // ignore names in strings
            array('MATCH (Propel\Tests\Bookstore\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', 'Propel\Tests\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', 'Propel\Tests\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', 'Propel\Tests\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', 'Propel\Tests\Bookstore\Book');
        $c->setModelAlias('b', true);
        $c->where('b.Title = ?', 'foo');
        $c->join('b.Author a');
        $c->where('a.FirstName = ?', 'john');

        if (in_array($this->getDriver(), array('mysql'))) {
            $sql = "SELECT  FROM `book` `b` INNER JOIN `author` `a` ON (b.AUTHOR_ID=a.ID) WHERE b.TITLE = :p1 AND a.FIRST_NAME = :p2";
        } else {
            $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', 'Propel\Tests\Bookstore\Book');
        $c->condition('cond1', 'Propel\Tests\Bookstore\Book.Title <> ?', 'foo');
        $c->condition('cond2', 'Propel\Tests\Bookstore\Book.Title like ?', '%bar%');
        $c->combine(array('cond1', 'cond2'), 'or');

        if (in_array($this->getDriver(), array('mysql'))) {
            $sql = "SELECT  FROM `book` WHERE (book.TITLE <> :p1 OR book.TITLE like :p2)";
        } else {
            $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 function testConditionCustomOperator()
    {
        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $c->withColumn('SUBSTRING(Book.Title, 1, 4)', 'title_start');
        $c->condition('cond1', 'Book.Title <> ?', 'foo');
        $c->condition('cond2', 'title_start like ?', '%bar%', PDO::PARAM_STR);
        $c->combine(array('cond1', 'cond2'), 'or');

        if (in_array($this->getDriver(), array('mysql'))) {
            $sql = "SELECT book.ID, book.TITLE, book.ISBN, book.PRICE, book.PUBLISHER_ID, book.AUTHOR_ID, SUBSTRING(book.TITLE, 1, 4) AS title_start FROM `book` WHERE (book.TITLE <> :p1 OR title_start like :p2)";
        } else {
            $sql = "SELECT book.ID, book.TITLE, book.ISBN, book.PRICE, book.PUBLISHER_ID, book.AUTHOR_ID, SUBSTRING(book.TITLE, 1, 4) AS title_start FROM book WHERE (book.TITLE <> :p1 OR title_start like :p2)";
        }

        $params = array(
            array('table' => 'book', 'column' => 'TITLE', 'value' => 'foo'),
            array('table' => null, 'type' => PDO::PARAM_STR, 'value' => '%bar%'),
        );
        $this->assertCriteriaTranslation($c, $sql, $params, 'condition() accepts RAW sql parameters');
    }

    static public function conditionsForTestWhere()
    {
        return array(
            array('Propel\Tests\Bookstore\Book.Title = ?', 'foo', 'book.TITLE = :p1', array(array('table' => 'book', 'column' => 'TITLE', 'value' => 'foo'))),
            array('Propel\Tests\Bookstore\Book.AuthorId = ?', 12, 'book.AUTHOR_ID = :p1', array(array('table' => 'book', 'column' => 'AUTHOR_ID', 'value' => 12))),
            array('Propel\Tests\Bookstore\Book.AuthorId IS NULL', null, 'book.AUTHOR_ID IS NULL', array()),
            array('Propel\Tests\Bookstore\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('Propel\Tests\Bookstore\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('Propel\Tests\Bookstore\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('Propel\Tests\Bookstore\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('Propel\Tests\Bookstore\Book.Id IN ?', array(), '1<>1', array()),
            array('Propel\Tests\Bookstore\Book.Id not in ?', array(), '1=1', array()),
            array('UPPER(Propel\Tests\Bookstore\Book.Title) = ?', 'foo', 'UPPER(book.TITLE) = :p1', array(array('table' => 'book', 'column' => 'TITLE', 'value' => 'foo'))),
            array('MATCH (Propel\Tests\Bookstore\Book.Title,Propel\Tests\Bookstore\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', 'Propel\Tests\Bookstore\Book');
        $c->where($clause, $value);

        if (in_array($this->getDriver(), array('mysql'))) {
            $sql = 'SELECT  FROM `book` WHERE ' . $sql;
        } else {
            $sql = 'SELECT  FROM book WHERE ' . $sql;
        }

        $this->assertCriteriaTranslation($c, $sql, $params, 'where() accepts a string clause');
    }

    public function testWhereUsesDefaultOperator()
    {
        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $c->where('Propel\Tests\Bookstore\Book.Id = ?', 12);
        $c->_or();
        $c->where('Propel\Tests\Bookstore\Book.Title = ?', 'foo');

        if (in_array($this->getDriver(), array('mysql'))) {
            $sql = 'SELECT  FROM `book` WHERE (book.ID = :p1 OR book.TITLE = :p2)';
        } else {
            $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', 'Propel\Tests\Bookstore\Book');
        $c->where('Propel\Tests\Bookstore\Book.Id IN ?', array(1, 2, 3));
        $c->where('Propel\Tests\Bookstore\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'),
        );

        if (in_array($this->getDriver(), array('mysql'))) {
            $sql = 'SELECT  FROM `book` WHERE (book.ID IN (:p1,:p2,:p3) AND book.ID <> :p4)';
        } else {
            $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', 'Propel\Tests\Bookstore\Book');
        $c->condition('cond1', 'Propel\Tests\Bookstore\Book.Title <> ?', 'foo');
        $c->condition('cond2', 'Propel\Tests\Bookstore\Book.Title like ?', '%bar%');
        $c->where(array('cond1', 'cond2'));

        if (in_array($this->getDriver(), array('mysql'))) {
            $sql = "SELECT  FROM `book` WHERE (book.TITLE <> :p1 AND book.TITLE like :p2)";
        } else {
            $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', 'Propel\Tests\Bookstore\Book');
        $c->condition('cond1', 'Propel\Tests\Bookstore\Book.Title <> ?', 'foo');
        $c->condition('cond2', 'Propel\Tests\Bookstore\Book.Title like ?', '%bar%');
        $c->where(array('cond1', 'cond2'), Criteria::LOGICAL_OR);

        if (in_array($this->getDriver(), array('mysql'))) {
            $sql = "SELECT  FROM `book` WHERE (book.TITLE <> :p1 OR book.TITLE like :p2)";
        } else {
            $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', 'Propel\Tests\Bookstore\Book', 'b');
        $c->where('b.Title = ?', 'foo');
        $c->where('1=1');

        if (in_array($this->getDriver(), array('mysql'))) {
            $sql = "SELECT  FROM `book` WHERE book.TITLE = :p1 AND 1=1";
        } else {
            $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', 'Propel\Tests\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', 'Propel\Tests\Bookstore\Book', 'b');
        $c->where('UPPER(b.Title) = ?', 'foo');

        if (in_array($this->getDriver(), array('mysql'))) {
            $sql = "SELECT  FROM `book` WHERE UPPER(book.TITLE) = :p1";
        } else {
            $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 testWhereTypeValue()
    {
        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book', 'b');
        $c->where('LOCATE(\'foo\', b.Title) = ?', true, PDO::PARAM_BOOL);

        if (in_array($this->getDriver(), array('mysql'))) {
            $sql = "SELECT  FROM `book` WHERE LOCATE('foo', book.TITLE) = :p1";
        } else {
            $sql = "SELECT  FROM book WHERE LOCATE('foo', book.TITLE) = :p1";
        }

        $params = array(
            array('table' => null, 'type' => PDO::PARAM_BOOL, 'value' => true),
        );
        $this->assertCriteriaTranslation($c, $sql, $params, 'where() accepts a complex calculation');
        $c->find($this->con);

        if (in_array($this->getDriver(), array('mysql'))) {
            $expected = "SELECT book.ID, book.TITLE, book.ISBN, book.PRICE, book.PUBLISHER_ID, book.AUTHOR_ID FROM `book` WHERE LOCATE('foo', book.TITLE) = true";
        } else {
            $expected = "SELECT book.ID, book.TITLE, book.ISBN, book.PRICE, book.PUBLISHER_ID, book.AUTHOR_ID FROM book WHERE LOCATE('foo', book.TITLE) = true";
        }

        $this->assertEquals($expected, $this->con->getLastExecutedQuery());
    }

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

        if (in_array($this->getDriver(), array('mysql'))) {
            $sql = "SELECT  FROM `book` WHERE (book.TITLE <> :p1 OR book.TITLE like :p2)";
        } else {
            $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', 'Propel\Tests\Bookstore\Book');
        $c->where('Propel\Tests\Bookstore\Book.Id = ?', 12);
        $c->condition('cond1', 'Propel\Tests\Bookstore\Book.Title <> ?', 'foo');
        $c->condition('cond2', 'Propel\Tests\Bookstore\Book.Title like ?', '%bar%');
        $c->_or()->where(array('cond1', 'cond2'));

        if (in_array($this->getDriver(), array('mysql'))) {
            $sql = "SELECT  FROM `book` WHERE (book.ID = :p1 OR (book.TITLE <> :p2 AND book.TITLE like :p3))";
        } else {
            $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', 'Propel\Tests\Bookstore\Book');
        $c->where('Propel\Tests\Bookstore\Book.Id = ?', 12);
        $c->condition('cond1', 'Propel\Tests\Bookstore\Book.Title <> ?', 'foo');
        $c->condition('cond2', 'Propel\Tests\Bookstore\Book.Title like ?', '%bar%');
        $c->_or()->where(array('cond1', 'cond2'), Criteria::LOGICAL_OR);

        if (in_array($this->getDriver(), array('mysql'))) {
            $sql = "SELECT  FROM `book` WHERE (book.ID = :p1 OR (book.TITLE <> :p2 OR book.TITLE like :p3))";
        } else {
            $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', 'Propel\Tests\Bookstore\Book');
        $c->where('Propel\Tests\Bookstore\Book.Title = ?', 'foo');
        $c->add(BookPeer::ID, array(1, 2), Criteria::IN);

        if (in_array($this->getDriver(), array('mysql'))) {
            $sql = 'SELECT  FROM `book` WHERE book.TITLE = :p1 AND book.ID IN (:p2,:p3)';
        } else {
            $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', 'Propel\Tests\Bookstore\Book');
        $c->filterBy('Title', 'foo');

        if (in_array($this->getDriver(), array('mysql'))) {
            $sql = 'SELECT  FROM `book` WHERE book.TITLE=:p1';
        } else {
            $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', 'Propel\Tests\Bookstore\Book');
        $c->filterBy('Title', 'foo', Criteria::NOT_EQUAL);

        if (in_array($this->getDriver(), array('mysql'))) {
            $sql = 'SELECT  FROM `book` WHERE book.TITLE<>:p1';
        } else {
            $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', 'Propel\Tests\Bookstore\Book', 'b');
        $c->filterBy('Title', 'foo');

        if (in_array($this->getDriver(), array('mysql'))) {
            $sql = 'SELECT  FROM `book` WHERE book.TITLE=:p1';
        } else {
            $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', 'Propel\Tests\Bookstore\Book');
        $c->having('Propel\Tests\Bookstore\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', 'Propel\Tests\Bookstore\Book');
        $c->condition('cond1', 'Propel\Tests\Bookstore\Book.Title <> ?', 'foo');
        $c->condition('cond2', 'Propel\Tests\Bookstore\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', 'Propel\Tests\Bookstore\Book');
        $c->condition('cond1', 'Propel\Tests\Bookstore\Book.Title <> ?', 'foo');
        $c->condition('cond2', 'Propel\Tests\Bookstore\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 testHavingWithColumn()
    {
        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $c->withColumn('SUBSTRING(Book.Title, 1, 4)', 'title_start');
        $c->having('title_start = ?', 'foo', PDO::PARAM_STR);

        if (in_array($this->getDriver(), array('mysql'))) {
            $sql = 'SELECT book.ID, book.TITLE, book.ISBN, book.PRICE, book.PUBLISHER_ID, book.AUTHOR_ID, SUBSTRING(book.TITLE, 1, 4) AS title_start FROM `book` HAVING title_start = :p1';
        } else {
            $sql = 'SELECT book.ID, book.TITLE, book.ISBN, book.PRICE, book.PUBLISHER_ID, book.AUTHOR_ID, SUBSTRING(book.TITLE, 1, 4) AS title_start FROM book HAVING title_start = :p1';
        }

        $params = array(
            array('table' => null, 'type' => 2, 'value' => 'foo'),
        );
        $this->assertCriteriaTranslation($c, $sql, $params, 'having() accepts a string clause');
        $c->find($this->con);

        if (in_array($this->getDriver(), array('mysql'))) {
            $expected = 'SELECT book.ID, book.TITLE, book.ISBN, book.PRICE, book.PUBLISHER_ID, book.AUTHOR_ID, SUBSTRING(book.TITLE, 1, 4) AS title_start FROM `book` HAVING title_start = \'foo\'';
        } else {
            $expected = 'SELECT book.ID, book.TITLE, book.ISBN, book.PRICE, book.PUBLISHER_ID, book.AUTHOR_ID, SUBSTRING(book.TITLE, 1, 4) AS title_start FROM book HAVING title_start = \'foo\'';
        }

        $this->assertEquals($expected, $this->con->getLastExecutedQuery());
    }

    public function testOrderBy()
    {
        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $c->orderBy('Propel\Tests\Bookstore\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', 'Propel\Tests\Bookstore\Book');
        $c->orderBy('Propel\Tests\Bookstore\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', 'Propel\Tests\Bookstore\Book');
        try {
            $c->orderBy('Propel\Tests\Bookstore\Book.Foo');
            $this->fail('orderBy() throws an exception when called with an unkown column name');
        } catch (UnknownColumnException $e) {
            $this->assertTrue(true, 'orderBy() throws an exception when called with an unkown column name');
        }
        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        try {
            $c->orderBy('Propel\Tests\Bookstore\Book.Title', 'foo');
            $this->fail('orderBy() throws an exception when called with an unkown order');
        } catch (UnexpectedValueException $e) {
            $this->assertTrue(true, 'orderBy() throws an exception when called with an unkown order');
        }
    }

    public function testOrderBySimpleColumn()
    {
        $c = new ModelCriteria('bookstore', 'Propel\Tests\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', 'Propel\Tests\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', 'Propel\Tests\Bookstore\Book');
        $c->groupBy('Propel\Tests\Bookstore\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', 'Propel\Tests\Bookstore\Book');
        try {
            $c->groupBy('Book.Foo');
            $this->fail('groupBy() throws an exception when called with an unkown column name');
        } catch (UnknownColumnException $e) {
            $this->assertTrue(true, 'groupBy() throws an exception when called with an unkown column name');
        }
    }

    public function testGroupBySimpleColumn()
    {
        $c = new ModelCriteria('bookstore', 'Propel\Tests\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', 'Propel\Tests\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 \Propel\Runtime\Exception\ClassNotFoundException
     */
    public function testGroupByClassThrowsExceptionOnUnknownClass()
    {
        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $c->groupByClass('Author');
    }

    public function testGroupByClass()
    {
        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $c->groupByClass('Propel\Tests\Bookstore\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', 'Propel\Tests\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', 'Propel\Tests\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', 'Propel\Tests\Bookstore\Author');
        $c->join('Propel\Tests\Bookstore\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', 'Propel\Tests\Bookstore\Author');
        $c->join('Propel\Tests\Bookstore\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', 'Propel\Tests\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', 'Propel\Tests\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', 'Propel\Tests\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', 'Propel\Tests\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', 'Propel\Tests\Bookstore\Book');
        $c->join('Propel\Tests\Bookstore\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', 'Propel\Tests\Bookstore\Book');
        try {
            $c->join('Propel\Tests\Bookstore\Book.Foo');
            $this->fail('join() throws an exception when called with a non-existing relation');
        } catch (UnknownRelationException $e) {
            $this->assertTrue(true, 'join() throws an exception when called with a non-existing relation');
        }

        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $c->join('Propel\Tests\Bookstore\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', 'Propel\Tests\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::getServiceContainer()->getConnection(BookPeer::DATABASE_NAME);
        BookstoreDataPopulator::depopulate($con);
        BookstoreDataPopulator::populate($con);

        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $c->join('Propel\Tests\Bookstore\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', 'Propel\Tests\Bookstore\BookstoreEmployee');
        $c->join('Propel\Tests\Bookstore\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', 'Propel\Tests\Bookstore\ReaderFavorite');
        $c->join('Propel\Tests\Bookstore\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', 'Propel\Tests\Bookstore\Book');
        $c->join('Propel\Tests\Bookstore\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', 'Propel\Tests\Bookstore\Book');
        $c->join('Propel\Tests\Bookstore\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', 'Propel\Tests\Bookstore\Book');
        $c->join('Propel\Tests\Bookstore\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', 'Propel\Tests\Bookstore\Book');
        $c->join('Propel\Tests\Bookstore\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', 'Propel\Tests\Bookstore\Book');
        $c->join('Propel\Tests\Bookstore\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', 'Propel\Tests\Bookstore\Book');
        $c->join('Propel\Tests\Bookstore\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', 'Propel\Tests\Bookstore\Author');
        $c->join('Propel\Tests\Bookstore\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', 'Propel\Tests\Bookstore\BookstoreEmployee');
        $c->join('Propel\Tests\Bookstore\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', 'Propel\Tests\Bookstore\BookstoreEmployeeAccount');
        $c->join('Propel\Tests\Bookstore\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', 'Propel\Tests\Bookstore\Author');
        $c->join('Propel\Tests\Bookstore\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', 'Propel\Tests\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', 'Propel\Tests\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', 'Propel\Tests\Bookstore\Book');
        $c->join('Propel\Tests\Bookstore\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', 'Propel\Tests\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::getServiceContainer()->getConnection(BookPeer::DATABASE_NAME);
        $c = new ModelCriteria('bookstore', 'Propel\Tests\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', 'Propel\Tests\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', 'Propel\Tests\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', 'Propel\Tests\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', 'Propel\Tests\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::getServiceContainer()->getConnection(BookPeer::DATABASE_NAME);
        $c = new ModelCriteria('bookstore', 'Propel\Tests\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', 'Propel\Tests\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::getServiceContainer()->getConnection(BookPeer::DATABASE_NAME);
        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $c->join('Propel\Tests\Bookstore\Book.Author', Criteria::INNER_JOIN);
        $c->addJoinCondition('Author', 'Propel\Tests\Bookstore\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::getServiceContainer()->getConnection(BookPeer::DATABASE_NAME);
        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $c->join('Propel\Tests\Bookstore\Book.Author', Criteria::INNER_JOIN);
        $c->addJoinCondition('Author', 'Propel\Tests\Bookstore\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::getServiceContainer()->getConnection(BookPeer::DATABASE_NAME);
        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $c->join('Propel\Tests\Bookstore\Book.Author', Criteria::INNER_JOIN);
        $c->addJoinCondition('Author', 'Propel\Tests\Bookstore\Book.Title = ?', 'foo');
        $c->addJoinCondition('Author', 'Propel\Tests\Bookstore\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::getServiceContainer()->getConnection(BookPeer::DATABASE_NAME);
        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $c->where('Propel\Tests\Bookstore\Book.Title LIKE ?', 'foo%');
        $c->join('Propel\Tests\Bookstore\Book.Author', Criteria::INNER_JOIN);
        $c->addJoinCondition('Author', 'Propel\Tests\Bookstore\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::getServiceContainer()->getConnection(BookPeer::DATABASE_NAME);
        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $c->join('Propel\Tests\Bookstore\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::getServiceContainer()->getConnection(BookPeer::DATABASE_NAME);
        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $c->join('Propel\Tests\Bookstore\Book.Author', Criteria::INNER_JOIN);
        $c->addJoinCondition('Author', 'Propel\Tests\Bookstore\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::getServiceContainer()->getConnection(BookPeer::DATABASE_NAME);
        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $c->join('Propel\Tests\Bookstore\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::getServiceContainer()->getConnection(BookPeer::DATABASE_NAME);
        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $c->join('Propel\Tests\Bookstore\Book.Author', Criteria::INNER_JOIN);
        $c->condition('cond1', 'Propel\Tests\Bookstore\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', 'Propel\Tests\Bookstore\Book');
        $c->join('Propel\Tests\Bookstore\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', 'Propel\Tests\Bookstore\Book');
        $c->join('Propel\Tests\Bookstore\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->assertInstanceOf('Propel\Runtime\Query\ModelWith', $withs['Author'], 'with() references the ModelWith object');
    }

    /**
     * @expectedException \Propel\Runtime\Query\Exception\UnknownRelationException
     */
    public function testWithThrowsExceptionWhenJoinLacks()
    {
        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $c->with('Propel\Tests\Bookstore\Author');
    }

    public function testWithAlias()
    {
        $c = new TestableModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $c->join('Propel\Tests\Bookstore\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 \Propel\Runtime\Query\Exception\UnknownRelationException
     */
    public function testWithThrowsExceptionWhenNotUsingAlias()
    {
        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $c->join('Propel\Tests\Bookstore\Book.Author a');
        $c->with('Propel\Tests\Bookstore\Author');
    }

    public function testWithAddsSelectColumns()
    {
        $c = new TestableModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        BookPeer::addSelectColumns($c);
        $c->join('Propel\Tests\Bookstore\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', 'Propel\Tests\Bookstore\Book');
        BookPeer::addSelectColumns($c);
        $c->join('Propel\Tests\Bookstore\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', 'Propel\Tests\Bookstore\Book');
        $c->join('Propel\Tests\Bookstore\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', 'Propel\Tests\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', 'Propel\Tests\Bookstore\Author');
        AuthorPeer::addSelectColumns($c);
        $c->leftJoin('Propel\Tests\Bookstore\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', 'Propel\Tests\Bookstore\Book');
        $c->joinWith('Propel\Tests\Bookstore\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', 'Propel\Tests\Bookstore\Book');
        $c->joinWith('Propel\Tests\Bookstore\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', 'Propel\Tests\Bookstore\Book');
        $c->joinWith('Propel\Tests\Bookstore\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', 'Propel\Tests\Bookstore\Review');
        $c->joinWith('Review.Book');
        $c->joinWith('Propel\Tests\Bookstore\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', 'Propel\Tests\Bookstore\Book');
        $c->join('Propel\Tests\Bookstore\Book.Review');
        $c->joinWith('Propel\Tests\Bookstore\Book.Author');
        $c->joinWith('Propel\Tests\Bookstore\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');
    }

    static public function conditionsForTestWithColumn()
    {
        return array(
            array('Propel\Tests\Bookstore\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(Propel\Tests\Bookstore\Book.Title, Propel\Tests\Bookstore\Book.ISBN)', 'foo', 'CONCAT(book.TITLE, book.ISBN) AS foo'),
        );
    }

    /**
     * @dataProvider conditionsForTestWithColumn
     */
    public function testWithColumn($clause, $alias, $selectTranslation)
    {
        $c = new ModelCriteria('bookstore', 'Propel\Tests\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');
    }

    static public 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, Propel\Tests\Bookstore\Author.Age, 120) + \' GMT\'", 'GMTCreatedAt', "CONVERT(varchar, author.AGE, 120) + \' GMT\' AS GMTCreatedAt"),
            array("(Propel\Tests\Bookstore\Author.FirstName + ' ' + Propel\Tests\Bookstore\Author.LastName)", 'AuthorFullname', "(author.FIRST_NAME + ' ' + author.LAST_NAME) AS AuthorFullname"),
            array("('\"' + Propel\Tests\Bookstore\Author.FirstName + ' ' + Propel\Tests\Bookstore\Author.LastName + '\"')", 'QuotedAuthorFullname', "('\"' + author.FIRST_NAME + ' ' + author.LAST_NAME + '\"') AS QuotedAuthorFullname"),

            // Examples for simple string concatenation needed for Sqlite
            // Sqlite has no CONCAT() function so uses || to join strings.  || can also be used to join strings in PQSql and Oracle
            array("(Propel\Tests\Bookstore\Author.FirstName || ' ' || Propel\Tests\Bookstore\Author.LastName)", 'AuthorFullname', "(author.FIRST_NAME || ' ' || author.LAST_NAME) AS AuthorFullname"),
            array("('\"' || Propel\Tests\Bookstore\Author.FirstName || ' ' || Propel\Tests\Bookstore\Author.LastName || '\"')", 'QuotedAuthorFullname', "('\"' || author.FIRST_NAME || ' ' || author.LAST_NAME || '\"') AS QuotedAuthorFullname"),
        );
    }

    /**
     * @dataProvider conditionsForTestWithColumnAndQuotes
     */
    public function testWithColumnAndQuotes($clause, $alias, $selectTranslation)
    {
        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Author');
        $c->withColumn($clause, $alias);
        $sql = 'SELECT author.ID, author.FIRST_NAME, author.LAST_NAME, author.EMAIL, author.AGE, ' . $selectTranslation . ' FROM `author`';
        $params = array();
        $this->assertCriteriaTranslation($c, $sql, $params, 'withColumn() adds a calculated column using quotes to the select clause');
    }

    public function testWithColumnAndSelectColumns()
    {
        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $c->withColumn('UPPER(Propel\Tests\Bookstore\Book.Title)', 'foo');
        $sql = 'SELECT book.ID, book.TITLE, book.ISBN, book.PRICE, book.PUBLISHER_ID, book.AUTHOR_ID, UPPER(book.TITLE) AS foo FROM `book`';
        $params = array();
        $this->assertCriteriaTranslation($c, $sql, $params, 'withColumn() adds the object columns if the criteria has no select columns');

        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $c->addSelectColumn('book.ID');
        $c->withColumn('UPPER(Propel\Tests\Bookstore\Book.Title)', 'foo');
        $sql = 'SELECT book.ID, UPPER(book.TITLE) AS foo FROM `book`';
        $params = array();
        $this->assertCriteriaTranslation($c, $sql, $params, 'withColumn() does not add the object columns if the criteria already has select columns');

        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $c->addSelectColumn('book.ID');
        $c->withColumn('UPPER(Propel\Tests\Bookstore\Book.Title)', 'foo');
        $c->addSelectColumn('book.TITLE');
        $sql = 'SELECT book.ID, book.TITLE, UPPER(book.TITLE) AS foo FROM `book`';
        $params = array();
        $this->assertCriteriaTranslation($c, $sql, $params, 'withColumn() does adds as column after the select columns even though the withColumn() method was called first');

        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $c->addSelectColumn('book.ID');
        $c->withColumn('UPPER(Propel\Tests\Bookstore\Book.Title)', 'foo');
        $c->withColumn('UPPER(Propel\Tests\Bookstore\Book.ISBN)', 'isbn');
        $sql = 'SELECT book.ID, UPPER(book.TITLE) AS foo, UPPER(book.ISBN) AS isbn FROM `book`';
        $params = array();
        $this->assertCriteriaTranslation($c, $sql, $params, 'withColumn() called repeatedly adds several as colums');
    }

    public function testKeepQuery()
    {
        $c = BookQuery::create();
        $this->assertTrue($c->isKeepQuery(), 'keepQuery is enabled by default');
        $c->keepQuery(false);
        $this->assertFalse($c->isKeepQuery(), 'keepQuery(false) disables the keepQuery property');
        $c->keepQuery();
        $this->assertTrue($c->isKeepQuery(), 'keepQuery() enables the keepQuery property');
    }

    public function testKeepQueryFind()
    {
        $c = BookQuery::create();
        $c->filterByTitle('foo');
        $c->find();
        $this->assertEquals(array(), $c->getSelectColumns(), 'find() clones the query by default');

        $c = BookQuery::create();
        $c->filterByTitle('foo');
        $c->keepQuery(false);
        $c->find();
        $expected = array('book.ID', 'book.TITLE', 'book.ISBN', 'book.PRICE', 'book.PUBLISHER_ID', 'book.AUTHOR_ID');
        $this->assertEquals($expected, $c->getSelectColumns(), 'keepQuery(false) forces find() to use the original query');
    }

    public function testKeepQueryFindOne()
    {
        $c = BookQuery::create();
        $c->filterByTitle('foo');
        $c->findOne();
        $this->assertEquals(0, $c->getLimit(), 'findOne() clones the query by default');

        $c = BookQuery::create();
        $c->filterByTitle('foo');
        $c->keepQuery(false);
        $c->findOne();
        $this->assertEquals(1, $c->getLimit(), 'keepQuery(false) forces findOne() to use the original query');
    }

    public function testKeepQueryFindPk()
    {
        $c = BookQuery::create();
        $c->findPk(1);
        $this->assertEquals(array(), $c->getSelectColumns(), 'findPk() clones the query by default');

        $c = BookQuery::create('b');
        $c->keepQuery(false);
        $c->findPk(1);
        $expected = array('book.ID', 'book.TITLE', 'book.ISBN', 'book.PRICE', 'book.PUBLISHER_ID', 'book.AUTHOR_ID');
        $this->assertEquals($expected, $c->getSelectColumns(), 'keepQuery(false) forces findPk() to use the original query');
    }

    public function testKeepQueryCount()
    {
        $c = BookQuery::create();
        $c->orderByTitle();
        $c->count();
        $this->assertEquals(array('book.TITLE ASC'), $c->getOrderByColumns(), 'count() clones the query by default');

        $c = BookQuery::create();
        $c->orderByTitle();
        $c->keepQuery(false);
        $c->count();
        $this->assertEquals(array(), $c->getOrderByColumns(), 'keepQuery() forces count() to use the original query');
    }

    public function testFind()
    {
        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book', 'b');
        $c->where('b.Title = ?', 'foo');
        $books = $c->find();
        $this->assertTrue($books instanceof Collection, 'find() returns a collection by default');
        $this->assertEquals(0, count($books), 'find() returns an empty array when the query returns no result');

        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book', 'b');
        $c->join('b.Author a');
        $c->where('a.FirstName = ?', 'Neal');
        $books = $c->find();
        $this->assertTrue($books instanceof Collection, 'find() returns a collection by default');
        $this->assertEquals(1, count($books), 'find() returns as many rows as the results in the query');
        $book = $books->shift();
        $this->assertTrue($book instanceof Book, 'find() returns an array of Model objects by default');
        $this->assertEquals('Quicksilver', $book->getTitle(), 'find() returns the model objects matching the query');
    }

    public function testFindAddsSelectColumns()
    {
        $con = Propel::getServiceContainer()->getConnection(BookPeer::DATABASE_NAME);
        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $books = $c->find($con);
        $sql = "SELECT book.ID, book.TITLE, book.ISBN, book.PRICE, book.PUBLISHER_ID, book.AUTHOR_ID FROM `book`";
        $this->assertEquals($sql, $con->getLastExecutedQuery(), 'find() adds the select columns of the current model');
    }

    public function testFindTrueAliasAddsSelectColumns()
    {
        $con = Propel::getServiceContainer()->getConnection(BookPeer::DATABASE_NAME);
        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $c->setModelAlias('b', true);
        $books = $c->find($con);
        $sql = "SELECT b.ID, b.TITLE, b.ISBN, b.PRICE, b.PUBLISHER_ID, b.AUTHOR_ID FROM `book` `b`";
        $this->assertEquals($sql, $con->getLastExecutedQuery(), 'find() uses the true model alias if available');
    }

    public function testFindOne()
    {
        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book', 'b');
        $c->where('b.Title = ?', 'foo');
        $book = $c->findOne();
        $this->assertNull($book, 'findOne() returns null when the query returns no result');

        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book', 'b');
        $c->orderBy('b.Title');
        $book = $c->findOne();
        $this->assertTrue($book instanceof Book, 'findOne() returns a Model object by default');
        $this->assertEquals('Don Juan', $book->getTitle(), 'find() returns the model objects matching the query');
    }

    public function testFindOneOrCreateNotExists()
    {
        BookQuery::create()->deleteAll();
        $book = BookQuery::create('b')
            ->where('b.Title = ?', 'foo')
            ->filterByPrice(125)
            ->findOneOrCreate();
        $this->assertTrue($book instanceof Book, 'findOneOrCreate() returns an instance of the model when the request has no result');
        $this->assertTrue($book->isNew(), 'findOneOrCreate() returns a new instance of the model when the request has no result');
        $this->assertEquals('foo', $book->getTitle(), 'findOneOrCreate() returns a populated objects based on the conditions');
        $this->assertEquals(125, $book->getPrice(), 'findOneOrCreate() returns a populated objects based on the conditions');
    }

    public function testFindOneOrCreateNotExistsFormatter()
    {
        BookQuery::create()->deleteAll();
        $book = BookQuery::create('b')
            ->where('b.Title = ?', 'foo')
            ->filterByPrice(125)
            ->setFormatter(ModelCriteria::FORMAT_ARRAY)
            ->findOneOrCreate();
        $this->assertTrue(is_array($book), 'findOneOrCreate() uses the query formatter even when the request has no result');
        $this->assertEquals('foo', $book['Title'], 'findOneOrCreate() returns a populated array based on the conditions');
        $this->assertEquals(125, $book['Price'], 'findOneOrCreate() returns a populated array based on the conditions');
    }

    public function testFindOneOrCreateExists()
    {
        BookQuery::create()->deleteAll();
        $book = new Book();
        $book->setTitle('foo');
        $book->setPrice(125);
        $book->save();
        $book = BookQuery::create('b')
            ->where('b.Title = ?', 'foo')
            ->filterByPrice(125)
            ->findOneOrCreate();
        $this->assertTrue($book instanceof Book, 'findOneOrCreate() returns an instance of the model when the request has one result');
        $this->assertFalse($book->isNew(), 'findOneOrCreate() returns an existing instance of the model when the request has one result');
        $this->assertEquals('foo', $book->getTitle(), 'findOneOrCreate() returns a populated objects based on the conditions');
        $this->assertEquals(125, $book->getPrice(), 'findOneOrCreate() returns a populated objects based on the conditions');
    }

    /**
     * @expectedException Propel\Runtime\Exception\PropelException
     */
    public function testFindOneOrCreateThrowsExceptionWhenQueryContainsJoin()
    {
        $book = BookQuery::create('b')
            ->filterByPrice(125)
            ->useAuthorQuery()
                ->filterByFirstName('Leo')
            ->endUse()
            ->findOneOrCreate();
    }

    public function testFindOneOrCreateMakesOneQueryWhenRecordNotExists()
    {
        $con = Propel::getServiceContainer()->getConnection(BookPeer::DATABASE_NAME);
        BookQuery::create()->deleteAll($con);
        $count = $con->getQueryCount();
        $book = BookQuery::create('b')
            ->filterByPrice(125)
            ->findOneOrCreate($con);
        $this->assertEquals($count + 1, $con->getQueryCount(), 'findOneOrCreate() makes only a single query when the record doesn\'t exist');
    }

    public function testFindOneOrCreateMakesOneQueryWhenRecordExists()
    {
        $con = Propel::getServiceContainer()->getConnection(BookPeer::DATABASE_NAME);
        BookQuery::create()->deleteAll($con);
        $book = new Book();
        $book->setPrice(125);
        $book->save($con);
        $count = $con->getQueryCount();
        $book = BookQuery::create('b')
            ->filterByPrice(125)
            ->findOneOrCreate($con);
        $this->assertEquals($count + 1, $con->getQueryCount(), 'findOneOrCreate() makes only a single query when the record exists');
    }

    public function testFindOneOrCreateWithEnums()
    {
        Book2Query::create()->deleteAll();

        $book = Book2Query::create('b')
            ->where('b.Title = ?', 'bar')
            ->filterByStyle('poetry')
            ->findOneOrCreate();
        $this->assertTrue($book instanceof Book2, 'findOneOrCreate() returns an instance of the model when the request has no result');
        $this->assertTrue($book->isNew(), 'findOneOrCreate() returns a new instance of the model when the request has no result');
        $this->assertEquals('bar', $book->getTitle(), 'findOneOrCreate() returns a populated objects based on the conditions');
        $this->assertEquals('poetry', $book->getStyle(), 'findOneOrCreate() returns a populated objects based on the conditions');

        $book = Book2Query::create('b')
            ->where('b.Title = ?', 'foobar')
            ->filterByStyle('essay')
            ->findOneOrCreate();
        $this->assertTrue($book instanceof Book2, 'findOneOrCreate() returns an instance of the model when the request has no result');
        $this->assertTrue($book->isNew(), 'findOneOrCreate() returns a new instance of the model when the request has no result');
        $this->assertEquals('foobar', $book->getTitle(), 'findOneOrCreate() returns a populated objects based on the conditions');
        $this->assertEquals('essay', $book->getStyle(), 'findOneOrCreate() returns a populated objects based on the conditions');

        $book = Book2Query::create('b')
            ->where('b.Style = ?', 'novel')
            ->findOneOrCreate();
        $this->assertTrue($book instanceof Book2, 'findOneOrCreate() returns an instance of the model when the request has no result');
        $this->assertTrue($book->isNew(), 'findOneOrCreate() returns a new instance of the model when the request has no result');
        $this->assertEquals('novel', $book->getStyle(), 'findOneOrCreate() returns a populated objects based on the conditions');
    }

    public function testFindOneOrCreateWithArrays()
    {
        Book2Query::create()->deleteAll();

        $book = Book2Query::create('b')
            ->filterByTag('russian')
            ->findOneOrCreate();
        $this->assertTrue($book instanceof Book2, 'findOneOrCreate() returns an instance of the model when the request has no result');
        $this->assertTrue($book->isNew(), 'findOneOrCreate() returns a new instance of the model when the request has no result');
        $this->assertTrue(is_array($book->getTags()), 'findOneOrCreate() returns a populated objects based on the conditions');
        $this->assertSame(array('russian'), $book->getTags(), 'findOneOrCreate() returns a populated objects based on the conditions');

        $book = Book2Query::create('b')
            ->filterByTags(array('poetry'))
            ->findOneOrCreate();
        $this->assertTrue($book instanceof Book2, 'findOneOrCreate() returns an instance of the model when the request has no result');
        $this->assertTrue($book->isNew(), 'findOneOrCreate() returns a new instance of the model when the request has no result');
        $this->assertTrue(is_array($book->getTags()), 'findOneOrCreate() returns a populated objects based on the conditions');
        $this->assertSame(array('poetry'), $book->getTags(), 'findOneOrCreate() returns a populated objects based on the conditions');
    }

    public function testFindPkSimpleKey()
    {
        BookstoreDataPopulator::depopulate();

        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $book = $c->findPk(765432);
        $this->assertNull($book, 'findPk() returns null when the primary key is not found');

        BookstoreDataPopulator::populate();

        // retrieve the test data
        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $testBook = $c->findOne();

        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $book = $c->findPk($testBook->getId());
        $this->assertEquals($testBook, $book, 'findPk() returns a model object corresponding to the pk');
    }

    public function testFindPksSimpleKey()
    {
        BookstoreDataPopulator::depopulate();

        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $books = $c->findPks(array(765432, 434535));
        $this->assertTrue($books instanceof Collection, 'findPks() returns a Collection');
        $this->assertEquals(0, count($books), 'findPks() returns an empty collection when the primary keys are not found');

        BookstoreDataPopulator::populate();

        // retrieve the test data
        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $testBooks = $c->find();
        $testBook1 = $testBooks->pop();
        $testBook2 = $testBooks->pop();

        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $books = $c->findPks(array($testBook1->getId(), $testBook2->getId()));
        $this->assertEquals(array($testBook2, $testBook1), $books->getData(), 'findPks() returns an array of model objects corresponding to the pks');
    }

    public function testFindPkCompositeKey()
    {
        BookstoreDataPopulator::depopulate();

        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\BookListRel');
        $bookListRel = $c->findPk(array(1, 2));
        $this->assertNull($bookListRel, 'findPk() returns null when the composite primary key is not found');

        Propel::enableInstancePooling();
        BookstoreDataPopulator::populate();

        // save all books to make sure related objects are also saved - BookstoreDataPopulator keeps some unsaved
        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $books = $c->find();
        foreach ($books as $book) {
            $book->save();
        }

        // retrieve the test data
        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\BookListRel');
        $bookListRelTest = $c->findOne();
        $pk = $bookListRelTest->getPrimaryKey();

        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\BookListRel');
        $bookListRel = $c->findPk($pk);
        $this->assertEquals($bookListRelTest, $bookListRel, 'findPk() can find objects with composite primary keys');
    }

    /**
     * @expectedException \Propel\Runtime\Exception\PropelException
     */
    public function testFindPksCompositeKey()
    {
        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\BookListRel');
        $bookListRel = $c->findPks(array(array(1, 2)));

    }

    public function testFindBy()
    {
        try {
            $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
            $books = $c->findBy('Foo', 'Bar');
            $this->fail('findBy() throws an exception when called on an unknown column name');
        } catch (UnknownColumnException $e) {
            $this->assertTrue(true, 'findBy() throws an exception when called on an unknown column name');
        }

        $con = Propel::getServiceContainer()->getConnection(BookPeer::DATABASE_NAME);
        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $books = $c->findBy('Title', 'Don Juan', $con);
        $expectedSQL = "SELECT book.ID, book.TITLE, book.ISBN, book.PRICE, book.PUBLISHER_ID, book.AUTHOR_ID FROM `book` WHERE book.TITLE='Don Juan'";
        $this->assertEquals($expectedSQL, $con->getLastExecutedQuery(), 'findBy() adds simple column conditions');
        $this->assertTrue($books instanceof Collection, 'findBy() issues a find()');
        $this->assertEquals(1, count($books), 'findBy() adds simple column conditions');
        $book = $books->shift();
        $this->assertTrue($book instanceof Book, 'findBy() returns an array of Model objects by default');
        $this->assertEquals('Don Juan', $book->getTitle(), 'findBy() returns the model objects matching the query');
    }

    public function testFindByArray()
    {
        $con = Propel::getServiceContainer()->getConnection(BookPeer::DATABASE_NAME);
        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $books = $c->findByArray(array('Title' => 'Don Juan', 'ISBN' => 12345), $con);
        $expectedSQL = "SELECT book.ID, book.TITLE, book.ISBN, book.PRICE, book.PUBLISHER_ID, book.AUTHOR_ID FROM `book` WHERE book.TITLE='Don Juan' AND book.ISBN=12345";
        $this->assertEquals($expectedSQL, $con->getLastExecutedQuery(), 'findByArray() adds multiple column conditions');
    }

    public function testFindOneBy()
    {
        try {
            $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
            $book = $c->findOneBy('Foo', 'Bar');
            $this->fail('findOneBy() throws an exception when called on an unknown column name');
        } catch (UnknownColumnException $e) {
            $this->assertTrue(true, 'findOneBy() throws an exception when called on an unknown column name');
        }

        $con = Propel::getServiceContainer()->getConnection(BookPeer::DATABASE_NAME);
        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $book = $c->findOneBy('Title', 'Don Juan', $con);
        $expectedSQL = "SELECT book.ID, book.TITLE, book.ISBN, book.PRICE, book.PUBLISHER_ID, book.AUTHOR_ID FROM `book` WHERE book.TITLE='Don Juan' LIMIT 1";
        $this->assertEquals($expectedSQL, $con->getLastExecutedQuery(), 'findOneBy() adds simple column conditions');
        $this->assertTrue($book instanceof Book, 'findOneBy() returns a Model object by default');
        $this->assertEquals('Don Juan', $book->getTitle(), 'findOneBy() returns the model object matching the query');
    }

    public function testFindOneByArray()
    {
        $con = Propel::getServiceContainer()->getConnection(BookPeer::DATABASE_NAME);
        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $book = $c->findOneByArray(array('Title' => 'Don Juan', 'ISBN' => 12345), $con);
        $expectedSQL = "SELECT book.ID, book.TITLE, book.ISBN, book.PRICE, book.PUBLISHER_ID, book.AUTHOR_ID FROM `book` WHERE book.TITLE='Don Juan' AND book.ISBN=12345 LIMIT 1";
        $this->assertEquals($expectedSQL, $con->getLastExecutedQuery(), 'findOneBy() adds multiple column conditions');
    }

    public function testCount()
    {
        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book', 'b');
        $c->where('b.Title = ?', 'foo');
        $nbBooks = $c->count();
        $this->assertTrue(is_int($nbBooks), 'count() returns an integer');
        $this->assertEquals(0, $nbBooks, 'count() returns 0 when the query returns no result');

        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book', 'b');
        $c->join('b.Author a');
        $c->where('a.FirstName = ?', 'Neal');
        $nbBooks = $c->count();
        $this->assertTrue(is_int($nbBooks), 'count() returns an integer');
        $this->assertEquals(1, $nbBooks, 'count() returns the number of results in the query');
    }

    public function testPaginate()
    {
        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book', 'b');
        $c->join('b.Author a');
        $c->where('a.FirstName = ?', 'Neal');
        $books = $c->paginate(1, 5);
        $this->assertTrue($books instanceof PropelModelPager, 'paginate() returns a PropelModelPager');
        $this->assertEquals(1, count($books), 'paginate() returns a countable pager with the correct count');
        foreach ($books as $book) {
            $this->assertEquals('Neal', $book->getAuthor()->getFirstName(), 'paginate() returns an iterable pager');
        }
    }

    public function testDelete()
    {
        BookstoreDataPopulator::depopulate();
        BookstoreDataPopulator::populate();

        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        try {
            $nbBooks = $c->delete();
            $this->fail('delete() throws an exception when called on an empty Criteria');
        } catch (PropelException $e) {
            $this->assertTrue(true, 'delete() throws an exception when called on an empty Criteria');
        }

        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book', 'b');
        $c->where('b.Title = ?', 'foo');
        $nbBooks = $c->delete();
        $this->assertTrue(is_int($nbBooks), 'delete() returns an integer');
        $this->assertEquals(0, $nbBooks, 'delete() returns 0 when the query deleted no rows');

        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book', 'b');
        $c->where('b.Title = ?', 'Don Juan');
        $nbBooks = $c->delete();
        $this->assertTrue(is_int($nbBooks), 'delete() returns an integer');
        $this->assertEquals(1, $nbBooks, 'delete() returns the number of the deleted rows');

        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $nbBooks = $c->count();
        $this->assertEquals(3, $nbBooks, 'delete() deletes rows in the database');
    }

    public function testDeleteUsingTableAlias()
    {
        $con = Propel::getServiceContainer()->getConnection(BookPeer::DATABASE_NAME);

        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $c->setModelAlias('b', false);
        $c->where('b.Title = ?', 'foo');
        $c->delete();
        $expectedSQL = "DELETE FROM `book` WHERE book.TITLE = 'foo'";
        $this->assertEquals($expectedSQL, $con->getLastExecutedQuery(), 'delete() also works on tables with table alias');

        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $c->setModelAlias('b', true);
        $c->where('b.Title = ?', 'foo');
        $c->delete();
        $expectedSQL = "DELETE b FROM `book` AS b WHERE b.TITLE = 'foo'";
        $this->assertEquals($expectedSQL, $con->getLastExecutedQuery(), 'delete() also works on tables with true table alias');
    }

    public function testDeleteAll()
    {
        BookstoreDataPopulator::depopulate();
        BookstoreDataPopulator::populate();

        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $nbBooks = $c->deleteAll();
        $this->assertTrue(is_int($nbBooks), 'deleteAll() returns an integer');
        $this->assertEquals(4, $nbBooks, 'deleteAll() returns the number of deleted rows');

        BookstoreDataPopulator::depopulate();
        BookstoreDataPopulator::populate();

        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book', 'b');
        $c->where('b.Title = ?', 'Don Juan');
        $nbBooks = $c->deleteAll();
        $this->assertEquals(4, $nbBooks, 'deleteAll() ignores conditions on the criteria');
    }

    public function testUpdate()
    {
        $con = Propel::getServiceContainer()->getConnection(BookPeer::DATABASE_NAME);
        BookstoreDataPopulator::depopulate($con);
        BookstoreDataPopulator::populate($con);

        $count = $con->getQueryCount();
        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $nbBooks = $c->update(array('Title' => 'foo'), $con);
        $this->assertEquals(4, $nbBooks, 'update() returns the number of updated rows');
        $this->assertEquals($count + 1, $con->getQueryCount(), 'update() updates all the objects in one query by default');

        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book', 'b');
        $c->where('b.Title = ?', 'foo');
        $nbBooks = $c->count();
        $this->assertEquals(4, $nbBooks, 'update() updates all records by default');

        BookstoreDataPopulator::depopulate($con);
        BookstoreDataPopulator::populate($con);

        $count = $con->getQueryCount();
        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book', 'b');
        $c->where('b.Title = ?', 'Don Juan');
        $nbBooks = $c->update(array('ISBN' => '3456'), $con);
        $this->assertEquals(1, $nbBooks, 'update() updates only the records matching the criteria');
        $this->assertEquals($count + 1, $con->getQueryCount(), 'update() updates all the objects in one query by default');

        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book', 'b');
        $c->where('b.Title = ?', 'Don Juan');
        $book = $c->findOne();
        $this->assertEquals('3456', $book->getISBN(), 'update() updates only the records matching the criteria');
    }

    public function testUpdateUsingTableAlias()
    {
        $con = Propel::getServiceContainer()->getConnection(BookPeer::DATABASE_NAME);

        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $c->setModelAlias('b', false);
        $c->where('b.Title = ?', 'foo');
        $c->update(array('Title' => 'foo2'), $con);
        $expectedSQL = "UPDATE `book` SET `TITLE`='foo2' WHERE book.TITLE = 'foo'";
        $this->assertEquals($expectedSQL, $con->getLastExecutedQuery(), 'update() also works on tables with table alias');

        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $c->setModelAlias('b', true);
        $c->where('b.Title = ?', 'foo');
        $c->update(array('Title' => 'foo2'), $con);
        $expectedSQL = "UPDATE `book` `b` SET `TITLE`='foo2' WHERE b.TITLE = 'foo'";
        $this->assertEquals($expectedSQL, $con->getLastExecutedQuery(), 'update() also works on tables with true table alias');
    }

    public function testUpdateOneByOne()
    {
        $con = Propel::getServiceContainer()->getConnection(BookPeer::DATABASE_NAME);
        BookstoreDataPopulator::depopulate($con);
        BookstoreDataPopulator::populate($con);

        // save all books to make sure related objects are also saved - BookstoreDataPopulator keeps some unsaved
        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $books = $c->find();
        foreach ($books as $book) {
            $book->save();
        }

        $count = $con->getQueryCount();
        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $nbBooks = $c->update(array('Title' => 'foo'), $con, true);
        $this->assertEquals(4, $nbBooks, 'update() returns the number of updated rows');
        $this->assertEquals($count + 1 + 4, $con->getQueryCount(), 'update() updates the objects one by one when called with true as last parameter');

        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book', 'b');
        $c->where('b.Title = ?', 'foo');
        $nbBooks = $c->count();
        $this->assertEquals(4, $nbBooks, 'update() updates all records by default');

        BookstoreDataPopulator::depopulate($con);
        BookstoreDataPopulator::populate($con);

        // save all books to make sure related objects are also saved - BookstoreDataPopulator keeps some unsaved
        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $books = $c->find();
        foreach ($books as $book) {
            $book->save();
        }

        $count = $con->getQueryCount();
        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book', 'b');
        $c->where('b.Title = ?', 'Don Juan');
        $nbBooks = $c->update(array('ISBN' => '3456'), $con, true);
        $this->assertEquals(1, $nbBooks, 'update() updates only the records matching the criteria');
        $this->assertEquals($count + 1 + 1, $con->getQueryCount(), 'update() updates the objects one by one when called with true as last parameter');

        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book', 'b');
        $c->where('b.Title = ?', 'Don Juan');
        $book = $c->findOne();
        $this->assertEquals('3456', $book->getISBN(), 'update() updates only the records matching the criteria');
    }

    static public function conditionsForTestGetRelationName()
    {
        return array(
            array('Author', 'Author'),
            array('Book.Author', 'Author'),
            array('Author.Book', 'Book'),
            array('Book.Author a', 'a'),
        );
    }

    /**
     * @dataProvider conditionsForTestGetRelationName
     */
    public function testGetRelationName($relation, $relationName)
    {
        $this->assertEquals($relationName, ModelCriteria::getrelationName($relation));
    }

    public function testMagicJoin()
    {
        $con = Propel::getServiceContainer()->getConnection(BookPeer::DATABASE_NAME);

        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book', 'b');
        $c->leftJoin('b.Author a');
        $c->where('a.FirstName = ?', 'Leo');
        $books = $c->findOne($con);
        $expectedSQL = "SELECT book.ID, book.TITLE, book.ISBN, book.PRICE, book.PUBLISHER_ID, book.AUTHOR_ID FROM `book` LEFT JOIN `author` `a` ON (book.AUTHOR_ID=a.ID) WHERE a.FIRST_NAME = 'Leo' LIMIT 1";
        $this->assertEquals($expectedSQL, $con->getLastExecutedQuery(), 'leftJoin($x) is turned into join($x, Criteria::LEFT_JOIN)');

        $books = BookQuery::create()
            ->leftJoinAuthor('a')
            ->where('a.FirstName = ?', 'Leo')
            ->findOne($con);
        $expectedSQL = "SELECT book.ID, book.TITLE, book.ISBN, book.PRICE, book.PUBLISHER_ID, book.AUTHOR_ID FROM `book` LEFT JOIN `author` `a` ON (book.AUTHOR_ID=a.ID) WHERE a.FIRST_NAME = 'Leo' LIMIT 1";
        $this->assertEquals($expectedSQL, $con->getLastExecutedQuery(), 'leftJoinX() is turned into join($x, Criteria::LEFT_JOIN)');

        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book', 'b');
        $c->innerJoin('b.Author a');
        $c->where('a.FirstName = ?', 'Leo');
        $books = $c->findOne($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' LIMIT 1";
        $this->assertEquals($expectedSQL, $con->getLastExecutedQuery(), 'innerJoin($x) is turned into join($x, Criteria::INNER_JOIN)');

        $books = BookQuery::create()
            ->innerJoinAuthor('a')
            ->where('a.FirstName = ?', 'Leo')
            ->findOne($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' LIMIT 1";
        $this->assertEquals($expectedSQL, $con->getLastExecutedQuery(), 'innerJoinX() is turned into join($x, Criteria::INNER_JOIN)');

        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book', 'b');
        $c->rightJoin('b.Author a');
        $c->where('a.FirstName = ?', 'Leo');
        $books = $c->findOne($con);
        $expectedSQL = "SELECT book.ID, book.TITLE, book.ISBN, book.PRICE, book.PUBLISHER_ID, book.AUTHOR_ID FROM `book` RIGHT JOIN `author` `a` ON (book.AUTHOR_ID=a.ID) WHERE a.FIRST_NAME = 'Leo' LIMIT 1";
        $this->assertEquals($expectedSQL, $con->getLastExecutedQuery(), 'rightJoin($x) is turned into join($x, Criteria::RIGHT_JOIN)');

        $books = BookQuery::create()
            ->rightJoinAuthor('a')
            ->where('a.FirstName = ?', 'Leo')
            ->findOne($con);
        $expectedSQL = "SELECT book.ID, book.TITLE, book.ISBN, book.PRICE, book.PUBLISHER_ID, book.AUTHOR_ID FROM `book` RIGHT JOIN `author` `a` ON (book.AUTHOR_ID=a.ID) WHERE a.FIRST_NAME = 'Leo' LIMIT 1";
        $this->assertEquals($expectedSQL, $con->getLastExecutedQuery(), 'rightJoinX() is turned into join($x, Criteria::RIGHT_JOIN)');

        $books = BookQuery::create()
            ->leftJoinAuthor()
            ->where('Author.FirstName = ?', 'Leo')
            ->findOne($con);
        $expectedSQL = "SELECT book.ID, book.TITLE, book.ISBN, book.PRICE, book.PUBLISHER_ID, book.AUTHOR_ID FROM `book` LEFT JOIN `author` ON (book.AUTHOR_ID=author.ID) WHERE author.FIRST_NAME = 'Leo' LIMIT 1";
        $this->assertEquals($expectedSQL, $con->getLastExecutedQuery(), 'leftJoinX() is turned into join($x, Criteria::LEFT_JOIN)');
    }

    public function testMagicJoinWith()
    {
        $c = new TestableModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $c->leftJoinWith('Propel\Tests\Bookstore\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(), 'leftJoinWith() adds the join with the alias');
        $joins = $c->getJoins();
        $join = $joins['a'];
        $this->assertEquals(Criteria::LEFT_JOIN, $join->getJoinType(), 'leftJoinWith() adds a LEFT JOIN');
    }

    public function testMagicJoinWithRelation()
    {
        $c = new TestableModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $c->joinWithAuthor();
        $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(), 'joinWithXXX() adds the join with the XXX relation');
        $joins = $c->getJoins();
        $join = $joins['Author'];
        $this->assertEquals(Criteria::INNER_JOIN, $join->getJoinType(), 'joinWithXXX() adds an INNER JOIN');
    }

    public function testMagicJoinWithTypeAndRelation()
    {
        $c = new TestableModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $c->leftJoinWithAuthor();
        $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(), 'leftJoinWithXXX() adds the join with the XXX relation');
        $joins = $c->getJoins();
        $join = $joins['Author'];
        $this->assertEquals(Criteria::LEFT_JOIN, $join->getJoinType(), 'leftJoinWithXXX() adds an INNER JOIN');
    }

    public function testMagicFind()
    {
        $con = Propel::getServiceContainer()->getConnection(BookPeer::DATABASE_NAME);

        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $books = $c->findByTitle('Don Juan');
        $expectedSQL = "SELECT book.ID, book.TITLE, book.ISBN, book.PRICE, book.PUBLISHER_ID, book.AUTHOR_ID FROM `book` WHERE book.TITLE='Don Juan'";
        $this->assertEquals($expectedSQL, $con->getLastExecutedQuery(), 'findByXXX($value) is turned into findBy(XXX, $value)');

        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $books = $c->findByTitleAndISBN('Don Juan', 1234);
        $expectedSQL = "SELECT book.ID, book.TITLE, book.ISBN, book.PRICE, book.PUBLISHER_ID, book.AUTHOR_ID FROM `book` WHERE book.TITLE='Don Juan' AND book.ISBN=1234";
        $this->assertEquals($expectedSQL, $con->getLastExecutedQuery(), 'findByXXXAndYYY($value) is turned into findBy(array(XXX,YYY), $value)');

        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $book = $c->findOneByTitle('Don Juan');
        $expectedSQL = "SELECT book.ID, book.TITLE, book.ISBN, book.PRICE, book.PUBLISHER_ID, book.AUTHOR_ID FROM `book` WHERE book.TITLE='Don Juan' LIMIT 1";
        $this->assertEquals($expectedSQL, $con->getLastExecutedQuery(), 'findOneByXXX($value) is turned into findOneBy(XXX, $value)');

        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $book = $c->findOneByTitleAndISBN('Don Juan', 1234);
        $expectedSQL = "SELECT book.ID, book.TITLE, book.ISBN, book.PRICE, book.PUBLISHER_ID, book.AUTHOR_ID FROM `book` WHERE book.TITLE='Don Juan' AND book.ISBN=1234 LIMIT 1";
        $this->assertEquals($expectedSQL, $con->getLastExecutedQuery(), 'findOneByXXX($value) is turned into findOneBy(XXX, $value)');
    }

    public function testMagicFilterBy()
    {
        $con = Propel::getServiceContainer()->getConnection(BookPeer::DATABASE_NAME);

        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $books = $c->filterByTitle('Don Juan')->find($con);
        $expectedSQL = "SELECT book.ID, book.TITLE, book.ISBN, book.PRICE, book.PUBLISHER_ID, book.AUTHOR_ID FROM `book` WHERE book.TITLE='Don Juan'";
        $this->assertEquals($expectedSQL, $con->getLastExecutedQuery(), 'filterByXXX($value) is turned into filterBy(XXX, $value)');
    }

    public function testMagicOrderBy()
    {
        $con = Propel::getServiceContainer()->getConnection(BookPeer::DATABASE_NAME);

        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $books = $c->orderByTitle()->find($con);
        $expectedSQL = "SELECT book.ID, book.TITLE, book.ISBN, book.PRICE, book.PUBLISHER_ID, book.AUTHOR_ID FROM `book` ORDER BY book.TITLE ASC";
        $this->assertEquals($expectedSQL, $con->getLastExecutedQuery(), 'orderByXXX() is turned into orderBy(XXX)');

        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $books = $c->orderByTitle(Criteria::DESC)->find($con);
        $expectedSQL = "SELECT book.ID, book.TITLE, book.ISBN, book.PRICE, book.PUBLISHER_ID, book.AUTHOR_ID FROM `book` ORDER BY book.TITLE DESC";
        $this->assertEquals($expectedSQL, $con->getLastExecutedQuery(), 'orderByXXX($direction) is turned into orderBy(XXX, $direction)');
    }

    public function testMagicGroupBy()
    {
        $con = Propel::getServiceContainer()->getConnection(BookPeer::DATABASE_NAME);

        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $books = $c->groupByTitle()->find($con);
        $expectedSQL = "SELECT book.ID, book.TITLE, book.ISBN, book.PRICE, book.PUBLISHER_ID, book.AUTHOR_ID FROM `book` GROUP BY book.TITLE";
        $this->assertEquals($expectedSQL, $con->getLastExecutedQuery(), 'groupByXXX() is turned into groupBy(XXX)');
    }

    public function testUseQuery()
    {
        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book', 'b');
        $c->thisIsMe = true;
        $c->where('b.Title = ?', 'foo');
        $c->setOffset(10);
        $c->leftJoin('b.Author');

        $c2 = $c->useQuery('Author');
        $this->assertTrue($c2 instanceof AuthorQuery, 'useQuery() returns a secondary Criteria');
        $this->assertEquals($c, $c2->getPrimaryCriteria(), 'useQuery() sets the primary Criteria os the secondary Criteria');
        $c2->where('Author.FirstName = ?', 'john');
        $c2->limit(5);

        $c = $c2->endUse();
        $this->assertTrue($c->thisIsMe, 'endUse() returns the Primary Criteria');
        $this->assertEquals('Propel\Tests\Bookstore\Book', $c->getModelName(), 'endUse() returns the Primary Criteria');

        $con = Propel::getServiceContainer()->getConnection(BookPeer::DATABASE_NAME);
        $c->find($con);
        $expectedSQL = "SELECT book.ID, book.TITLE, book.ISBN, book.PRICE, book.PUBLISHER_ID, book.AUTHOR_ID FROM `book` LEFT JOIN `author` ON (book.AUTHOR_ID=author.ID) WHERE book.TITLE = 'foo' AND author.FIRST_NAME = 'john' LIMIT 10, 5";
        $this->assertEquals($expectedSQL, $con->getLastExecutedQuery(), 'useQuery() and endUse() allow to merge a secondary criteria');
    }

    public function testUseQueryAlias()
    {
        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book', 'b');
        $c->thisIsMe = true;
        $c->where('b.Title = ?', 'foo');
        $c->setOffset(10);
        $c->leftJoin('b.Author a');

        $c2 = $c->useQuery('a');
        $this->assertTrue($c2 instanceof AuthorQuery, 'useQuery() returns a secondary Criteria');
        $this->assertEquals($c, $c2->getPrimaryCriteria(), 'useQuery() sets the primary Criteria os the secondary Criteria');
        $this->assertEquals(array('a' => 'author'), $c2->getAliases(), 'useQuery() sets the secondary Criteria alias correctly');
        $c2->where('a.FirstName = ?', 'john');
        $c2->limit(5);

        $c = $c2->endUse();
        $this->assertTrue($c->thisIsMe, 'endUse() returns the Primary Criteria');
        $this->assertEquals('Propel\Tests\Bookstore\Book', $c->getModelName(), 'endUse() returns the Primary Criteria');

        $con = Propel::getServiceContainer()->getConnection(BookPeer::DATABASE_NAME);
        $c->find($con);
        $expectedSQL = "SELECT book.ID, book.TITLE, book.ISBN, book.PRICE, book.PUBLISHER_ID, book.AUTHOR_ID FROM `book` LEFT JOIN `author` `a` ON (book.AUTHOR_ID=a.ID) WHERE book.TITLE = 'foo' AND a.FIRST_NAME = 'john' LIMIT 10, 5";
        $this->assertEquals($expectedSQL, $con->getLastExecutedQuery(), 'useQuery() and endUse() allow to merge a secondary criteria');
    }

    public function testUseQueryCustomClass()
    {
        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book', 'b');
        $c->thisIsMe = true;
        $c->where('b.Title = ?', 'foo');
        $c->setLimit(10);
        $c->leftJoin('b.Author a');

        $c2 = $c->useQuery('a', 'Propel\Tests\Runtime\Query\ModelCriteriaForUseQuery');
        $this->assertTrue($c2 instanceof ModelCriteriaForUseQuery, 'useQuery() returns a secondary Criteria with the custom class');
        $c2->withNoName();
        $c = $c2->endUse();

        $con = Propel::getServiceContainer()->getConnection(BookPeer::DATABASE_NAME);
        $c->find($con);
        $expectedSQL = "SELECT book.ID, book.TITLE, book.ISBN, book.PRICE, book.PUBLISHER_ID, book.AUTHOR_ID FROM `book` LEFT JOIN `author` `a` ON (book.AUTHOR_ID=a.ID) WHERE book.TITLE = 'foo' AND a.FIRST_NAME IS NOT NULL  AND a.LAST_NAME IS NOT NULL LIMIT 10";
        $this->assertEquals($expectedSQL, $con->getLastExecutedQuery(), 'useQuery() and endUse() allow to merge a custom secondary criteria');
    }

    public function testUseQueryJoinWithFind()
    {
        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Review');
        $c->joinWith('Book');

        $c2 = $c->useQuery('Book');

        $joins = $c->getJoins();
        $this->assertEquals($c->getPreviousJoin(), null, 'The default value for previousJoin remains null');
        $this->assertEquals($c2->getPreviousJoin(), $joins['Book'], 'useQuery() sets the previousJoin');

        // join Book with Author, which is possible since previousJoin is set, which makes resolving of relations possible during hydration
        $c2->joinWith('Author');

        $c = $c2->endUse();

        $con = Propel::getServiceContainer()->getConnection(BookPeer::DATABASE_NAME);
        $c->find($con);

        if (in_array($this->getDriver(), array('mysql'))) {
            $expectedSQL = "SELECT review.ID, review.REVIEWED_BY, review.REVIEW_DATE, review.RECOMMENDED, review.STATUS, review.BOOK_ID, book.ID, book.TITLE, book.ISBN, book.PRICE, book.PUBLISHER_ID, book.AUTHOR_ID, author.ID, author.FIRST_NAME, author.LAST_NAME, author.EMAIL, author.AGE FROM `review` INNER JOIN `book` ON (review.BOOK_ID=book.ID) INNER JOIN `author` ON (book.AUTHOR_ID=author.ID)";
        } else {
            $expectedSQL = "SELECT review.ID, review.REVIEWED_BY, review.REVIEW_DATE, review.RECOMMENDED, review.STATUS, review.BOOK_ID, book.ID, book.TITLE, book.ISBN, book.PRICE, book.PUBLISHER_ID, book.AUTHOR_ID, author.ID, author.FIRST_NAME, author.LAST_NAME, author.EMAIL, author.AGE FROM review INNER JOIN book ON (review.BOOK_ID=book.ID) INNER JOIN author ON (book.AUTHOR_ID=author.ID)";
        }

        $this->assertEquals($expectedSQL, $con->getLastExecutedQuery(), 'useQuery() and joinWith() can be used together and form a correct query');
    }

    public function testUseQueryCustomRelationPhpName()
    {
        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\BookstoreContest');
        $c->leftJoin('Propel\Tests\Bookstore\BookstoreContest.Work');
        $c2 = $c->useQuery('Work');
        $this->assertTrue($c2 instanceof BookQuery, 'useQuery() returns a secondary Criteria');
        $this->assertEquals($c, $c2->getPrimaryCriteria(), 'useQuery() sets the primary Criteria os the secondary Criteria');
        //$this->assertEquals(array('a' => 'author'), $c2->getAliases(), 'useQuery() sets the secondary Criteria alias correctly');
        $c2->where('Work.Title = ?', 'War And Peace');

        $c = $c2->endUse();
        $this->assertEquals('Propel\Tests\Bookstore\BookstoreContest', $c->getModelName(), 'endUse() returns the Primary Criteria');

        $con = Propel::getServiceContainer()->getConnection(BookPeer::DATABASE_NAME);
        $c->find($con);

        if (in_array($this->getDriver(), array('mysql'))) {
            $expectedSQL = "SELECT bookstore_contest.BOOKSTORE_ID, bookstore_contest.CONTEST_ID, bookstore_contest.PRIZE_BOOK_ID FROM `bookstore_contest` LEFT JOIN `book` ON (bookstore_contest.PRIZE_BOOK_ID=book.ID) WHERE book.TITLE = 'War And Peace'";
        } else {
            $expectedSQL = "SELECT bookstore_contest.BOOKSTORE_ID, bookstore_contest.CONTEST_ID, bookstore_contest.PRIZE_BOOK_ID FROM bookstore_contest LEFT JOIN book ON (bookstore_contest.PRIZE_BOOK_ID=book.ID) WHERE book.TITLE = 'War And Peace'";
        }

        $this->assertEquals($expectedSQL, $con->getLastExecutedQuery(), 'useQuery() and endUse() allow to merge a secondary criteria');
    }

    public function testUseQueryCustomRelationPhpNameAndAlias()
    {
        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\BookstoreContest');
        $c->leftJoin('Propel\Tests\Bookstore\BookstoreContest.Work w');
        $c2 = $c->useQuery('w');
        $this->assertTrue($c2 instanceof BookQuery, 'useQuery() returns a secondary Criteria');
        $this->assertEquals($c, $c2->getPrimaryCriteria(), 'useQuery() sets the primary Criteria os the secondary Criteria');
        $this->assertEquals(array('w' => 'book'), $c2->getAliases(), 'useQuery() sets the secondary Criteria alias correctly');
        $c2->where('w.Title = ?', 'War And Peace');

        $c = $c2->endUse();
        $this->assertEquals('Propel\Tests\Bookstore\BookstoreContest', $c->getModelName(), 'endUse() returns the Primary Criteria');

        $con = Propel::getServiceContainer()->getConnection(BookPeer::DATABASE_NAME);
        $c->find($con);

        if (in_array($this->getDriver(), array('mysql'))) {
            $expectedSQL = "SELECT bookstore_contest.BOOKSTORE_ID, bookstore_contest.CONTEST_ID, bookstore_contest.PRIZE_BOOK_ID FROM `bookstore_contest` LEFT JOIN `book` `w` ON (bookstore_contest.PRIZE_BOOK_ID=w.ID) WHERE w.TITLE = 'War And Peace'";
        } else {
            $expectedSQL = "SELECT bookstore_contest.BOOKSTORE_ID, bookstore_contest.CONTEST_ID, bookstore_contest.PRIZE_BOOK_ID FROM bookstore_contest LEFT JOIN book w ON (bookstore_contest.PRIZE_BOOK_ID=w.ID) WHERE w.TITLE = 'War And Peace'";
        }

        $this->assertEquals($expectedSQL, $con->getLastExecutedQuery(), 'useQuery() and endUse() allow to merge a secondary criteria');
    }

    public function testMergeWithJoins()
    {
        $c1 = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book', 'b');
        $c1->leftJoin('b.Author a');
        $c2 = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Author');
        $c1->mergeWith($c2);
        $joins = $c1->getJoins();
        $this->assertEquals(1, count($joins), 'mergeWith() does not remove an existing join');
        $this->assertEquals('LEFT JOIN author a ON (book.AUTHOR_ID=a.ID)', $joins['a']->toString(), 'mergeWith() does not remove an existing join');
        $c1 = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book', 'b');
        $c2 = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book', 'b');
        $c2->leftJoin('b.Author a');
        $c1->mergeWith($c2);
        $joins = $c1->getJoins();
        $this->assertEquals(1, count($joins), 'mergeWith() merge joins to an empty join');
        $this->assertEquals('LEFT JOIN author a ON (book.AUTHOR_ID=a.ID)', $joins['a']->toString(), 'mergeWith() merge joins to an empty join');

        $c1 = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book', 'b');
        $c1->leftJoin('b.Author a');
        $c2 = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book', 'b');
        $c2->innerJoin('b.Publisher p');
        $c1->mergeWith($c2);
        $joins = $c1->getJoins();
        $this->assertEquals(2, count($joins), 'mergeWith() merge joins to an existing join');
        $this->assertEquals('LEFT JOIN author a ON (book.AUTHOR_ID=a.ID)', $joins['a']->toString(), 'mergeWith() merge joins to an empty join');
        $this->assertEquals('INNER JOIN publisher p ON (book.PUBLISHER_ID=p.ID)', $joins['p']->toString(), 'mergeWith() merge joins to an empty join');
    }

    public function testMergeWithWiths()
    {
        $c1 = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book', 'b');
        $c1->leftJoinWith('b.Author a');
        $c2 = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Author');
        $c1->mergeWith($c2);
        $with = $c1->getWith();
        $this->assertEquals(1, count($with), 'mergeWith() does not remove an existing join');
        $this->assertEquals('modelName: Propel\Tests\Bookstore\Author, relationName: Author, relationMethod: setAuthor, leftPhpName: , rightPhpName: a', $with['a']->__toString(), 'mergeWith() does not remove an existing join');

        $c1 = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book', 'b');
        $c2 = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book', 'b');
        $c2->leftJoinWith('b.Author a');
        $c1->mergeWith($c2);
        $with = $c1->getWith();
        $this->assertEquals(1, count($with), 'mergeWith() merge joins to an empty join');
        $this->assertEquals('modelName: Propel\Tests\Bookstore\Author, relationName: Author, relationMethod: setAuthor, leftPhpName: , rightPhpName: a', $with['a']->__toString(), 'mergeWith() merge joins to an empty join');

        $c1 = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book', 'b');
        $c1->leftJoinWith('b.Author a');
        $c2 = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book', 'b');
        $c2->innerJoinWith('b.Publisher p');
        $c1->mergeWith($c2);
        $with = $c1->getWith();
        $this->assertEquals(2, count($with), 'mergeWith() merge joins to an existing join');
        $this->assertEquals('modelName: Propel\Tests\Bookstore\Author, relationName: Author, relationMethod: setAuthor, leftPhpName: , rightPhpName: a', $with['a']->__toString(), 'mergeWith() merge joins to an empty join');
        $this->assertEquals('modelName: Propel\Tests\Bookstore\Publisher, relationName: Publisher, relationMethod: setPublisher, leftPhpName: , rightPhpName: p', $with['p']->__toString(), 'mergeWith() merge joins to an empty join');

    }

    public function testGetAliasedColName()
    {
        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $this->assertEquals(BookPeer::TITLE, $c->getAliasedColName(BookPeer::TITLE), 'getAliasedColName() returns the input when the table has no alias');

        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $c->setModelAlias('foo');
        $this->assertEquals(BookPeer::TITLE, $c->getAliasedColName(BookPeer::TITLE), 'getAliasedColName() returns the input when the table has a query alias');

        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $c->setModelAlias('foo', true);
        $this->assertEquals('foo.TITLE', $c->getAliasedColName(BookPeer::TITLE), 'getAliasedColName() returns the column name with table alias when the table has a true alias');
    }

    public function testAddUsingAliasNoAlias()
    {
        $c1 = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $c1->addUsingAlias(BookPeer::TITLE, 'foo');
        $c2 = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $c2->add(BookPeer::TITLE, 'foo');
        $this->assertEquals($c2, $c1, 'addUsingalias() translates to add() when the table has no alias');
    }

    public function testAddUsingAliasQueryAlias()
    {
        $c1 = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book', 'b');
        $c1->addUsingAlias(BookPeer::TITLE, 'foo');
        $c2 = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book', 'b');
        $c2->add(BookPeer::TITLE, 'foo');
        $this->assertEquals($c2, $c1, 'addUsingalias() translates the colname using the table alias before calling add() when the table has a true alias');
    }

    public function testAddUsingAliasTrueAlias()
    {
        $c1 = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $c1->setModelAlias('b', true);
        $c1->addUsingAlias(BookPeer::TITLE, 'foo');
        $c2 = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $c2->setModelAlias('b', true);
        $c2->add('b.TITLE', 'foo');
        $this->assertEquals($c2, $c1, 'addUsingalias() translates to add() when the table has a true alias');
    }

    public function testAddUsingAliasTwice()
    {
        $c1 = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $c1->addUsingAlias(BookPeer::TITLE, 'foo');
        $c1->addUsingAlias(BookPeer::TITLE, 'bar');
        $c2 = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $c2->add(BookPeer::TITLE, 'foo');
        $c2->addAnd(BookPeer::TITLE, 'bar');
        $this->assertEquals($c2, $c1, 'addUsingalias() translates to addAnd() when the table already has a condition on the column');
    }

    public function testAddUsingAliasTrueAliasTwice()
    {
        $c1 = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $c1->setModelAlias('b', true);
        $c1->addUsingAlias(BookPeer::TITLE, 'foo');
        $c1->addUsingAlias(BookPeer::TITLE, 'bar');
        $c2 = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Book');
        $c2->setModelAlias('b', true);
        $c2->add('b.TITLE', 'foo');
        $c2->addAnd('b.TITLE', 'bar');
        $this->assertEquals($c2, $c1, 'addUsingalias() translates to addAnd() when the table already has a condition on the column');
    }

    public function testClone()
    {
        $bookQuery1 = BookQuery::create()
            ->filterByPrice(1);
        $bookQuery2 = clone $bookQuery1;
        $bookQuery2
            ->filterByPrice(2);
        $params = array();
        $sql = BasePeer::createSelectSql($bookQuery1, $params);

        if (in_array($this->getDriver(), array('mysql'))) {
            $expected = 'SELECT  FROM `book` WHERE book.PRICE=:p1';
        } else {
            $expected = 'SELECT  FROM book WHERE book.PRICE=:p1';
        }

        $this->assertEquals($expected, $sql, 'conditions applied on a cloned query don\'t get applied on the original query');
    }

    public function testMagicFindByObject()
    {
        $con = Propel::getServiceContainer()->getConnection(BookPeer::DATABASE_NAME);
        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Author');
        $testAuthor = $c->findOne();
        $q = BookQuery::create()
            ->findByAuthor($testAuthor);

        if (in_array($this->getDriver(), array('mysql'))) {
            $expectedSQL = "SELECT book.ID, book.TITLE, book.ISBN, book.PRICE, book.PUBLISHER_ID, book.AUTHOR_ID FROM `book` WHERE book.AUTHOR_ID=" . $testAuthor->getId();
        } else {
            $expectedSQL = "SELECT book.ID, book.TITLE, book.ISBN, book.PRICE, book.PUBLISHER_ID, book.AUTHOR_ID FROM book WHERE book.AUTHOR_ID=" . $testAuthor->getId();
        }

        $this->assertEquals($expectedSQL, $con->getLastExecutedQuery(), 'findByXXX($value) is turned into findBy(XXX, $value)');

        $c = new ModelCriteria('bookstore', 'Propel\Tests\Bookstore\Author');
        $testAuthor = $c->findOne();
        $q = BookQuery::create()
            ->findByAuthorAndISBN($testAuthor, 1234);
        $expectedSQL = "SELECT book.ID, book.TITLE, book.ISBN, book.PRICE, book.PUBLISHER_ID, book.AUTHOR_ID FROM `book` WHERE book.AUTHOR_ID=" . $testAuthor->getId() . " AND book.ISBN=1234";
        $this->assertEquals($expectedSQL, $con->getLastExecutedQuery(), 'findByXXXAndYYY($value) is turned into findBy(array(XXX, YYY), $value)');
    }
}