PHP Criteria::createSelectSql方法代码示例

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