public function testClonedCriteriaNotAffected2()
{
$this->c->addCond('cond1', "INVOICE.COST1", "1000", Criteria::GREATER_EQUAL);
$this->c->addCond('cond2', "INVOICE.COST2", "2000", Criteria::LESS_EQUAL);
$this->c->combine(['cond1', 'cond2'], Criteria::LOGICAL_AND, 'cond12');
$this->c->addCond('cond3', "INVOICE.COST3", "8000", Criteria::GREATER_EQUAL);
$this->c->addCond('cond4', "INVOICE.COST4", "9000", Criteria::LESS_EQUAL);
$this->c->combine(['cond3', 'cond4'], Criteria::LOGICAL_AND, 'cond34');
$clonedCriteria = clone $this->c;
$expect1 = $this->getSql("SELECT FROM ");
$params1 = [];
$result1 = $this->c->createSelectSql($params1);
$this->assertEquals($expect1, $result1);
$this->c->addCond('cond5', "INVOICE.COST5", "5000", Criteria::GREATER_EQUAL);
$this->c->combine(['cond34', 'cond5'], Criteria::LOGICAL_AND);
$expect2 = $this->getSql("SELECT FROM INVOICE WHERE ((INVOICE.COST3>=:p1 AND INVOICE.COST4<=:p2) AND INVOICE.COST5>=:p3)");
$expect_params2 = [['table' => 'INVOICE', 'column' => 'COST3', 'value' => '8000'], ['table' => 'INVOICE', 'column' => 'COST4', 'value' => '9000'], ['table' => 'INVOICE', 'column' => 'COST5', 'value' => '5000']];
$params2 = [];
$result2 = $this->c->createSelectSql($params2);
$this->assertEquals($expect2, $result2);
$this->assertEquals($expect_params2, $params2);
// Cloned criteria should not be affected by the combine of cond34 above
// we should still be able to use it in the clone for another combine
$clonedCriteria->combine(['cond12', 'cond34'], Criteria::LOGICAL_OR);
$expect3 = $this->getSql("SELECT FROM INVOICE WHERE ((INVOICE.COST1>=:p1 AND INVOICE.COST2<=:p2) OR (INVOICE.COST3>=:p3 AND INVOICE.COST4<=:p4))");
$expect_params3 = [['table' => 'INVOICE', 'column' => 'COST1', 'value' => '1000'], ['table' => 'INVOICE', 'column' => 'COST2', 'value' => '2000'], ['table' => 'INVOICE', 'column' => 'COST3', 'value' => '8000'], ['table' => 'INVOICE', 'column' => 'COST4', 'value' => '9000']];
$params3 = [];
$result3 = $clonedCriteria->createSelectSql($params3);
$this->assertEquals($expect3, $result3);
$this->assertEquals($expect_params3, $params3);
}
public function testCombineDirtyOperators()
{
$this->c->addCond('cond1', "INVOICE.COST1", "1000", Criteria::GREATER_EQUAL);
$this->c->addCond('cond2', "INVOICE.COST2", "2000", Criteria::LESS_EQUAL);
$this->c->combine(['cond1', 'cond2'], 'AnD', 'cond12');
$this->c->addCond('cond3', "INVOICE.COST3", "8000", Criteria::GREATER_EQUAL);
$this->c->addCond('cond4', "INVOICE.COST4", "9000", Criteria::LESS_EQUAL);
$this->c->combine(['cond3', 'cond4'], 'aNd', 'cond34');
$this->c->combine(['cond12', 'cond34'], 'oR');
$expect = $this->getSql("SELECT FROM INVOICE WHERE ((INVOICE.COST1>=:p1 AND INVOICE.COST2<=:p2) OR (INVOICE.COST3>=:p3 AND INVOICE.COST4<=:p4))");
$expect_params = [['table' => 'INVOICE', 'column' => 'COST1', 'value' => '1000'], ['table' => 'INVOICE', 'column' => 'COST2', 'value' => '2000'], ['table' => 'INVOICE', 'column' => 'COST3', 'value' => '8000'], ['table' => 'INVOICE', 'column' => 'COST4', 'value' => '9000']];
$params = [];
$result = $this->c->createSelectSql($params);
$this->assertEquals($expect, $result);
$this->assertEquals($expect_params, $params);
}
public function testMssqlApplyLimitWithOffsetMultipleOrderBy()
{
$db = Propel::getServiceContainer()->getAdapter(BookTableMap::DATABASE_NAME);
if (!$db instanceof MssqlAdapter) {
$this->markTestSkipped('Configured database vendor is not MsSQL');
}
$c = new Criteria(BookTableMap::DATABASE_NAME);
$c->addSelectColumn(BookTableMap::COL_ID);
$c->addSelectColumn(BookTableMap::COL_TITLE);
$c->addSelectColumn(PublisherTableMap::COL_NAME);
$c->addAsColumn('PublisherName', '(SELECT MAX(publisher.NAME) FROM publisher WHERE publisher.ID = book.PUBLISHER_ID)');
$c->addJoin(BookTableMap::COL_PUBLISHER_ID, PublisherTableMap::COL_ID, Criteria::LEFT_JOIN);
$c->addDescendingOrderByColumn('PublisherName');
$c->addAscendingOrderByColumn(BookTableMap::COL_TITLE);
$c->setOffset(20);
$c->setLimit(20);
$params = array();
$expectedSql = "SELECT [book.ID], [book.TITLE], [publisher.NAME], [PublisherName] FROM (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT MAX(publisher.NAME) FROM publisher WHERE publisher.ID = book.PUBLISHER_ID) DESC, book.TITLE ASC) AS [RowNumber], book.ID AS [book.ID], book.TITLE AS [book.TITLE], publisher.NAME AS [publisher.NAME], (SELECT MAX(publisher.NAME) FROM publisher WHERE publisher.ID = book.PUBLISHER_ID) AS [PublisherName] FROM book LEFT JOIN publisher ON (book.PUBLISHER_ID=publisher.ID)) AS derivedb WHERE RowNumber BETWEEN 21 AND 40";
$sql = $c->createSelectSql($params);
$this->assertEquals($expectedSql, $sql);
}
public function testAddSelectModifier()
{
$c = new Criteria();
$c->setDistinct();
$c->addSelectModifier('SQL_CALC_FOUND_ROWS');
$this->assertEquals(array(Criteria::DISTINCT, 'SQL_CALC_FOUND_ROWS'), $c->getSelectModifiers(), 'addSelectModifier() adds a select modifier to the Criteria');
$c->addSelectModifier('SQL_CALC_FOUND_ROWS');
$this->assertEquals(array(Criteria::DISTINCT, 'SQL_CALC_FOUND_ROWS'), $c->getSelectModifiers(), 'addSelectModifier() adds a select modifier only once');
$params = array();
$result = $c->createSelectSql($params);
$this->assertEquals('SELECT DISTINCT SQL_CALC_FOUND_ROWS FROM ', $result, 'addSelectModifier() adds a modifier to the final query');
}
/**
* Do Explain Plan for query object or query string
*
* @param ConnectionInterface $con propel connection
* @param Criteria|string $query query the criteria or the query string
*
* @throws PropelException
* @return PDOStatement A PDO statement executed using the connection, ready to be fetched
*/
public function doExplainPlan(ConnectionInterface $con, $query)
{
if ($query instanceof Criteria) {
$params = array();
$dbMap = Propel::getServiceContainer()->getDatabaseMap($query->getDbName());
$sql = $query->createSelectSql($params);
} else {
$sql = $query;
}
$stmt = $con->prepare($this->getExplainPlanQuery($sql));
if ($query instanceof Criteria) {
$this->bindValues($stmt, $params, $dbMap);
}
$stmt->execute();
return $stmt;
}
public function testaddUsingOperatorResetsDefaultOperator()
{
$c = new Criteria();
$c->addUsingOperator('foo1', 'bar1');
$c->_or();
$c->addUsingOperator('foo2', 'bar2');
$c->addUsingOperator('foo3', 'bar3');
$expected = 'SELECT FROM WHERE (foo1=:p1 OR foo2=:p2) AND foo3=:p3';
$params = array();
$result = $c->createSelectSql($params);
$this->assertEquals($expected, $result);
}
