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 testApplyLimitDuplicateColumnNameWithColumn()
{
Propel::setDb('oracle', new DBOracle());
$c = new Criteria();
$c->setDbName('oracle');
BookPeer::addSelectColumns($c);
AuthorPeer::addSelectColumns($c);
$c->addAsColumn('BOOK_PRICE', BookPeer::PRICE);
$c->setLimit(1);
$params = array();
$asColumns = $c->getAsColumns();
$sql = BasePeer::createSelectSql($c, $params);
$this->assertEquals('SELECT B.* FROM (SELECT A.*, rownum AS PROPEL_ROWNUM FROM (SELECT book.ID AS ORA_COL_ALIAS_0, book.TITLE AS ORA_COL_ALIAS_1, book.ISBN AS ORA_COL_ALIAS_2, book.PRICE AS ORA_COL_ALIAS_3, book.PUBLISHER_ID AS ORA_COL_ALIAS_4, book.AUTHOR_ID AS ORA_COL_ALIAS_5, author.ID AS ORA_COL_ALIAS_6, author.FIRST_NAME AS ORA_COL_ALIAS_7, author.LAST_NAME AS ORA_COL_ALIAS_8, author.EMAIL AS ORA_COL_ALIAS_9, author.AGE AS ORA_COL_ALIAS_10, book.PRICE AS BOOK_PRICE FROM book, author) A ) B WHERE B.PROPEL_ROWNUM <= 1', $sql, 'applyLimit() creates a subselect with aliased column names when a duplicate column name is found');
$this->assertEquals($asColumns, $c->getAsColumns(), 'createSelectSql supplementary add alias column');
}
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 = BasePeer::createSelectSql($c, $params);
$this->assertEquals($expected, $result);
}
/**
* Build a string representation of the Criteria.
*
* @return string A String with the representation of the Criteria.
*/
public function toString()
{
$sb = "Criteria:";
try {
$params = array();
$sb .= "\nSQL (may not be complete): " . BasePeer::createSelectSql($this, $params);
$sb .= "\nParams: ";
$paramstr = array();
foreach ($params as $param) {
$paramstr[] = $param['table'] . '.' . $param['column'] . ' => ' . var_export($param['value'], true);
}
$sb .= implode(", ", $paramstr);
} catch (Exception $exc) {
$sb .= "(Error: " . $exc->getMessage() . ")";
}
return $sb;
}
/**
* Method to create an SQL query based on values in a Criteria.
*
* This method creates only prepared statement SQL (using ? where values
* will go). The second parameter ($params) stores the values that need
* to be set before the statement is executed. The reason we do it this way
* is to let the PDO layer handle all escaping & value formatting.
*
* @param Criteria $criteria Criteria for the SELECT query.
* @param array &$params Parameters that are to be replaced in prepared statement.
* @return string
* @throws \Propel\Runtime\Exception\RuntimeException Trouble creating the query string.
*/
public static function createSelectSql(Criteria $criteria, &$params)
{
$db = Propel::getServiceContainer()->getAdapter($criteria->getDbName());
$dbMap = Propel::getServiceContainer()->getDatabaseMap($criteria->getDbName());
$fromClause = array();
$joinClause = array();
$joinTables = array();
$whereClause = array();
$orderByClause = array();
$orderBy = $criteria->getOrderByColumns();
$groupBy = $criteria->getGroupByColumns();
// get the first part of the SQL statement, the SELECT part
$selectSql = $db->createSelectSqlPart($criteria, $fromClause);
// Handle joins
// joins with a null join type will be added to the FROM clause and the condition added to the WHERE clause.
// joins of a specified type: the LEFT side will be added to the fromClause and the RIGHT to the joinClause
foreach ($criteria->getJoins() as $join) {
$join->setAdapter($db);
// add 'em to the queues..
if (!$fromClause) {
$fromClause[] = $join->getLeftTableWithAlias();
}
$joinTables[] = $join->getRightTableWithAlias();
$joinClause[] = $join->getClause($params);
}
// add the criteria to WHERE clause
// this will also add the table names to the FROM clause if they are not already
// included via a LEFT JOIN
foreach ($criteria->keys() as $key) {
$criterion = $criteria->getCriterion($key);
$table = null;
foreach ($criterion->getAttachedCriterion() as $attachedCriterion) {
$tableName = $attachedCriterion->getTable();
$table = $criteria->getTableForAlias($tableName);
if ($table !== null) {
$fromClause[] = $table . ' ' . $tableName;
} else {
$fromClause[] = $tableName;
$table = $tableName;
}
if (($criteria->isIgnoreCase() || $attachedCriterion->isIgnoreCase()) && $dbMap->getTable($table)->getColumn($attachedCriterion->getColumn())->isText()) {
$attachedCriterion->setIgnoreCase(true);
}
}
$criterion->setAdapter($db);
$sb = '';
$criterion->appendPsTo($sb, $params);
$whereClause[] = $sb;
}
// Unique from clause elements
$fromClause = array_unique($fromClause);
$fromClause = array_diff($fromClause, array(''));
// tables should not exist in both the from and join clauses
if ($joinTables && $fromClause) {
foreach ($fromClause as $fi => $ftable) {
if (in_array($ftable, $joinTables)) {
unset($fromClause[$fi]);
}
}
}
// Add the GROUP BY columns
$groupByClause = $groupBy;
$having = $criteria->getHaving();
$havingString = null;
if ($having !== null) {
$sb = '';
$having->appendPsTo($sb, $params);
$havingString = $sb;
}
if (!empty($orderBy)) {
foreach ($orderBy as $orderByColumn) {
// Add function expression as-is.
if (strpos($orderByColumn, '(') !== false) {
$orderByClause[] = $orderByColumn;
continue;
}
// Split orderByColumn (i.e. "table.column DESC")
$dotPos = strrpos($orderByColumn, '.');
if ($dotPos !== false) {
$tableName = substr($orderByColumn, 0, $dotPos);
$columnName = substr($orderByColumn, $dotPos + 1);
} else {
$tableName = '';
$columnName = $orderByColumn;
}
$spacePos = strpos($columnName, ' ');
if ($spacePos !== false) {
$direction = substr($columnName, $spacePos);
$columnName = substr($columnName, 0, $spacePos);
} else {
$direction = '';
}
$tableAlias = $tableName;
if ($aliasTableName = $criteria->getTableForAlias($tableName)) {
$tableName = $aliasTableName;
}
$columnAlias = $columnName;
if ($asColumnName = $criteria->getColumnForAs($columnName)) {
$columnName = $asColumnName;
}
$column = $tableName ? $dbMap->getTable($tableName)->getColumn($columnName) : null;
if ($criteria->isIgnoreCase() && $column && $column->isText()) {
$ignoreCaseColumn = $db->ignoreCaseInOrderBy("{$tableAlias}.{$columnAlias}");
$orderByClause[] = $ignoreCaseColumn . $direction;
$selectSql .= ', ' . $ignoreCaseColumn;
} else {
$orderByClause[] = $orderByColumn;
}
}
}
if (empty($fromClause) && $criteria->getPrimaryTableName()) {
$fromClause[] = $criteria->getPrimaryTableName();
}
// tables should not exist as alias of subQuery
if ($criteria->hasSelectQueries()) {
foreach ($fromClause as $key => $ftable) {
if (strpos($ftable, ' ') !== false) {
list($realtable, $tableName) = explode(' ', $ftable);
} else {
$tableName = $ftable;
}
if ($criteria->hasSelectQuery($tableName)) {
unset($fromClause[$key]);
}
}
}
// from / join tables quoted if it is necessary
if ($db->useQuoteIdentifier()) {
$fromClause = array_map(array($db, 'quoteIdentifierTable'), $fromClause);
$joinClause = $joinClause ? $joinClause : array_map(array($db, 'quoteIdentifierTable'), $joinClause);
}
// add subQuery to From after adding quotes
foreach ($criteria->getSelectQueries() as $subQueryAlias => $subQueryCriteria) {
$fromClause[] = '(' . BasePeer::createSelectSql($subQueryCriteria, $params) . ') AS ' . $subQueryAlias;
}
// build from-clause
$from = '';
if (!empty($joinClause) && count($fromClause) > 1) {
$from .= implode(" CROSS JOIN ", $fromClause);
} else {
$from .= implode(", ", $fromClause);
}
$from .= $joinClause ? ' ' . implode(' ', $joinClause) : '';
// Build the SQL from the arrays we compiled
$sql = $selectSql . " FROM " . $from . ($whereClause ? " WHERE " . implode(" AND ", $whereClause) : "") . ($groupByClause ? " GROUP BY " . implode(",", $groupByClause) : "") . ($havingString ? " HAVING " . $havingString : "") . ($orderByClause ? " ORDER BY " . implode(",", $orderByClause) : "");
// APPLY OFFSET & LIMIT to the query.
if ($criteria->getLimit() || $criteria->getOffset()) {
$db->applyLimit($sql, $criteria->getOffset(), $criteria->getLimit(), $criteria);
}
return $sql;
}
public function testUseFkQueryNested()
{
$q = ReviewQuery::create()->useBookQuery()->useAuthorQuery()->filterByFirstName('Leo')->endUse()->endUse();
$q1 = ReviewQuery::create()->join('Review.Book', Criteria::LEFT_JOIN)->join('Book.Author', Criteria::LEFT_JOIN)->add(AuthorPeer::FIRST_NAME, 'Leo', Criteria::EQUAL);
// embedded queries create joins that keep a relation to the parent
// as this is not testable, we need to use another testing technique
$params = array();
$result = BasePeer::createSelectSql($q, $params);
$expectedParams = array();
$expectedResult = BasePeer::createSelectSql($q1, $expectedParams);
$this->assertEquals($expectedParams, $params, 'useFkQuery() called nested creates two joins');
$this->assertEquals($expectedResult, $result, 'useFkQuery() called nested creates two joins');
}
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(array('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(array('cond3', 'cond4'), 'aNd', 'cond34');
$this->c->combine(array('cond12', 'cond34'), 'oR');
$expect = "SELECT FROM INVOICE WHERE ((INVOICE.COST1>=:p1 AND INVOICE.COST2<=:p2) OR (INVOICE.COST3>=:p3 AND INVOICE.COST4<=:p4))";
$expect_params = array(array('table' => 'INVOICE', 'column' => 'COST1', 'value' => '1000'), array('table' => 'INVOICE', 'column' => 'COST2', 'value' => '2000'), array('table' => 'INVOICE', 'column' => 'COST3', 'value' => '8000'), array('table' => 'INVOICE', 'column' => 'COST4', 'value' => '9000'));
$params = array();
$result = BasePeer::createSelectSql($this->c, $params);
$this->assertEquals($expect, $result);
$this->assertEquals($expect_params, $params);
}
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 = BasePeer::createSelectSql($c, $params);
$this->assertEquals('SELECT DISTINCT SQL_CALC_FOUND_ROWS FROM ', $result, 'addSelectModifier() adds a modifier to the final query');
}
public function testClone()
{
$bookQuery1 = BookQuery::create()->filterByPrice(1);
$bookQuery2 = clone $bookQuery1;
$bookQuery2->filterByPrice(2);
$params = array();
$sql = BasePeer::createSelectSql($bookQuery1, $params);
$this->assertEquals('SELECT FROM `book` WHERE book.PRICE=:p1', $sql, 'conditions applied on a cloned query don\'t get applied on the original query');
}
public function testMssqlApplyLimitWithOffsetMultipleOrderBy()
{
$db = Propel::getServiceContainer()->getAdapter(BookPeer::DATABASE_NAME);
if (!$db instanceof MssqlAdapter) {
$this->markTestSkipped('Configured database vendor is not MsSQL');
}
$c = new Criteria(BookPeer::DATABASE_NAME);
$c->addSelectColumn(BookPeer::ID);
$c->addSelectColumn(BookPeer::TITLE);
$c->addSelectColumn(PublisherPeer::NAME);
$c->addAsColumn('PublisherName', '(SELECT MAX(publisher.NAME) FROM publisher WHERE publisher.ID = book.PUBLISHER_ID)');
$c->addJoin(BookPeer::PUBLISHER_ID, PublisherPeer::ID, Criteria::LEFT_JOIN);
$c->addDescendingOrderByColumn('PublisherName');
$c->addAscendingOrderByColumn(BookPeer::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 = BasePeer::createSelectSql($c, $params);
$this->assertEquals($expectedSql, $sql);
}
protected function doCount($con)
{
$dbMap = Propel::getServiceContainer()->getDatabaseMap($this->getDbName());
$db = Propel::getServiceContainer()->getAdapter($this->getDbName());
// check that the columns of the main class are already added (if this is the primary ModelCriteria)
if (!$this->hasSelectClause() && !$this->getPrimaryCriteria()) {
$this->addSelfSelectColumns();
}
$this->configureSelectColumns();
$needsComplexCount = $this->getGroupByColumns() || $this->getOffset() || $this->getLimit() || $this->getHaving() || in_array(Criteria::DISTINCT, $this->getSelectModifiers());
$params = array();
if ($needsComplexCount) {
if (BasePeer::needsSelectAliases($this)) {
if ($this->getHaving()) {
throw new PropelException('Propel cannot create a COUNT query when using HAVING and duplicate column names in the SELECT part');
}
$db->turnSelectColumnsToAliases($this);
}
$selectSql = BasePeer::createSelectSql($this, $params);
$sql = 'SELECT COUNT(*) FROM (' . $selectSql . ') propelmatch4cnt';
} else {
// Replace SELECT columns with COUNT(*)
$this->clearSelectColumns()->addSelectColumn('COUNT(*)');
$sql = BasePeer::createSelectSql($this, $params);
}
try {
$stmt = $con->prepare($sql);
$db->bindValues($stmt, $params, $dbMap);
$stmt->execute();
} catch (Exception $e) {
Propel::log($e->getMessage(), Propel::LOG_ERR);
throw new PropelException(sprintf('Unable to execute COUNT statement [%s]', $sql), $e);
}
return $stmt;
}
