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; }