PHP BasePeer::createSelectSql方法代码示例

 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 testApplyLimitDuplicateColumnName()
 {
     Propel::setDb('oracle', new DBOracle());
     $c = new Criteria();
     $c->setDbName('oracle');
     BookPeer::addSelectColumns($c);
     AuthorPeer::addSelectColumns($c);
     $c->setLimit(1);
     $params = array();
     $sql = BasePeer::createSelectSql($c, $params);
     $this->assertEquals('SELECT B.* FROM (SELECT A.*, rownum AS PROPEL_ROWNUM FROM (SELECT book.ID AS book_ID, book.TITLE AS book_TITLE, book.ISBN AS book_ISBN, book.PRICE AS book_PRICE, book.PUBLISHER_ID AS book_PUBLISHER_ID, book.AUTHOR_ID AS book_AUTHOR_ID, author.ID AS author_ID, author.FIRST_NAME AS author_FIRST_NAME, author.LAST_NAME AS author_LAST_NAME, author.EMAIL AS author_EMAIL, author.AGE AS author_AGESELECT 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 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');
 }

 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 static function getCriteriaCacheKey(Criteria $c)
 {
     $cloned_c = clone $c;
     $arr = array();
     $str = BasePeer::createSelectSql($c, $arr);
     $param_map = array();
     $keys = $c->keys();
     foreach ($keys as $k) {
         $val = $c->getCriterion($k);
         if ($val) {
             $param_map[$k] = $val->hashCode();
         }
     }
     $str .= print_r($param_map, true);
     $cache_key = md5($str);
     return $cache_key;
 }

 public static function getMiSQL($criteria)
 {
     if (!isset($criteria)) {
         return null;
     }
     if (!$criteria->getSelectColumns()) {
         $criteria = clone $criteria;
         self::addSelectColumns($criteria);
     }
     $criteria->setDbName(self::DATABASE_NAME);
     $con = Propel::getConnection($criteria->getDbName());
     $params = array();
     $sql = BasePeer::createSelectSql($criteria, $params);
     $stmt = null;
     $stmt = $con->prepare($sql);
     $stmt->setLimit($criteria->getLimit());
     $stmt->setOffset($criteria->getOffset());
     return $stmt;
 }

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

 /**
  * Do Explain Plan for query object or query string
  *
  * @param  PropelPDO            $con   propel connection
  * @param  ModelCriteria|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(PropelPDO $con, $query)
 {
     if ($query instanceof ModelCriteria) {
         $params = array();
         $dbMap = Propel::getDatabaseMap($query->getDbName());
         $sql = BasePeer::createSelectSql($query, $params);
         $sql = 'EXPLAIN ' . $sql;
     } else {
         $sql = 'EXPLAIN ' . $query;
     }
     $stmt = $con->prepare($sql);
     if ($query instanceof ModelCriteria) {
         $this->bindValues($stmt, $params, $dbMap);
     }
     $stmt->execute();
     return $stmt;
 }

 public function testMssqlApplyLimitWithOffsetMultipleOrderBy()
 {
     $db = Propel::getDB(BookPeer::DATABASE_NAME);
     if (!$db instanceof DBMSSQL) {
         $this->markTestSkipped();
     }
     $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::getDatabaseMap($this->getDbName());
     $db = Propel::getDB($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()) || count($this->selectQueries) > 0;
     $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;
 }

 /**
  * Do Explain Plan for query object or query string
  *
  * @param PropelPDO $con propel connection
  * @param ModelCriteria|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(PropelPDO $con, $query)
 {
     $con->beginTransaction();
     if ($query instanceof ModelCriteria) {
         $params = array();
         $dbMap = Propel::getDatabaseMap($query->getDbName());
         $sql = BasePeer::createSelectSql($query, $params);
     } else {
         $sql = $query;
     }
     // unique id for the query string
     $uniqueId = uniqid('Propel', true);
     $stmt = $con->prepare($this->getExplainPlanQuery($sql, $uniqueId));
     if ($query instanceof ModelCriteria) {
         $this->bindValues($stmt, $params, $dbMap);
     }
     $stmt->execute();
     // explain plan is save in a table, data must be commit
     $con->commit();
     $stmt = $con->prepare($this->getExplainPlanReadQuery($uniqueId));
     $stmt->execute();
     return $stmt;
 }

 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 testCombineAndFilterBy()
 {
     $params = array();
     $sql = "SELECT  FROM `book` WHERE ((book.title LIKE :p1 OR book.isbn LIKE :p2) AND book.title LIKE :p3)";
     $c = BookQuery::create()->condition('u1', 'book.title LIKE ?', '%test1%')->condition('u2', 'book.isbn LIKE ?', '%test2%')->combine(array('u1', 'u2'), 'or')->filterByTitle('%test3%');
     $result = BasePeer::createSelectSql($c, $params);
     $this->assertEquals($result, $sql);
     $params = array();
     $sql = "SELECT  FROM `book` WHERE (book.title LIKE :p1 AND (book.title LIKE :p2 OR book.isbn LIKE :p3))";
     $c = BookQuery::create()->filterByTitle('%test3%')->condition('u1', 'book.title LIKE ?', '%test1%')->condition('u2', 'book.isbn LIKE ?', '%test2%')->combine(array('u1', 'u2'), 'or');
     $result = BasePeer::createSelectSql($c, $params);
     $this->assertEquals($result, $sql);
 }

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

 public function testDistinct()
 {
     $c = new Criteria();
     $c->addSelectColumn('*');
     $c->add('TABLE.string', 'foo');
     $c->setDistinct();
     $c->add('TABLE.id', 10);
     $c->setDistinct();
     $params = array();
     $result = BasePeer::createSelectSql($c, $params);
     $this->assertEquals('SELECT DISTINCT * FROM TABLE WHERE TABLE.string=:p1 AND TABLE.id=:p2', $result);
 }

 /**
  * 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 PropelException Trouble creating the query string.
  */
 public static function createSelectSql(Criteria $criteria, &$params)
 {
     $db = Propel::getDB($criteria->getDbName());
     $dbMap = Propel::getDatabaseMap($criteria->getDbName());
     $fromClause = array();
     $joinClause = array();
     $joinTables = array();
     $whereClause = array();
     $orderByClause = array();
     $orderBy = $criteria->getOrderByColumns();
     $groupBy = $criteria->getGroupByColumns();
     $ignoreCase = $criteria->isIgnoreCase();
     // 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->setDB($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->setDB($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, ' ');
//.........这里部分代码省略.........