PHP QueryBuilder::getDQL方法代码示例

 /**
  * @test
  */
 public function should_create_complex_query()
 {
     $specification = $this->userSpecificationFactory->status('approved')->orX($this->userSpecificationFactory->status('declined')->andX($this->userSpecificationFactory->name('John')));
     $this->queryBuilder->select('user')->from('User', 'user')->where($specification->expr())->setParameters($specification->parameters());
     $this->assertEquals(['status_1' => 'approved', 'status_2' => 'declined', 'name_3' => 'John'], $specification->parameters());
     $this->assertEquals('SELECT user FROM User user WHERE user.status = :status_1 OR (user.status = :status_2 AND user.name = :name_3)', $this->queryBuilder->getDQL());
 }

 private function getNumRowsSubQuery()
 {
     if (!$this->numRowsSubQuery) {
         $this->numRowsSubQuery = $this->createNumRowsSubQuery();
     }
     return $this->numRowsSubQuery->getDQL();
 }

 /**
  * @param QueryBuilder $query
  * @param QueryBuilder $subQuery
  * @return QueryBuilder $query with query's and subQuery's parameters.
  */
 private function andWherePupilNotIn(QueryBuilder $query, QueryBuilder $subQuery, $EleveQueryAlias)
 {
     $query->andWhere($query->expr()->notIn($EleveQueryAlias . ".id", $subQuery->getDQL()));
     /**
      * @var Parameter $param
      */
     foreach ($subQuery->getParameters() as $param) {
         $query->setParameter($param->getName(), $param->getValue(), $param->getType());
     }
     return $query;
 }

 /**
  * We update the query to count, get ids and fetch data, so, we can lost expected query builder parameters,
  * and we have to remove them
  *
  * @param QueryBuilder $qb
  */
 public static function removeExtraParameters(QueryBuilder $qb)
 {
     $parameters = $qb->getParameters();
     $dql = $qb->getDQL();
     foreach ($parameters as $parameter) {
         if (strpos($dql, ':' . $parameter->getName()) === false) {
             $parameters->removeElement($parameter);
         }
     }
     $qb->setParameters($parameters);
 }

 /**
  * Removes unused parameters from query builder
  *
  * @param QueryBuilder $qb
  */
 public function fixUnusedParameters(QueryBuilder $qb)
 {
     $dql = $qb->getDQL();
     $usedParameters = [];
     /** @var $parameter \Doctrine\ORM\Query\Parameter */
     foreach ($qb->getParameters() as $parameter) {
         if ($this->dqlContainsParameter($dql, $parameter->getName())) {
             $usedParameters[$parameter->getName()] = $parameter->getValue();
         }
     }
     $qb->setParameters($usedParameters);
 }

 /**
  * @param QueryBuilder $qb
  *
  * @return string
  */
 public function getRawSQLFromORMQueryBuilder(QueryBuilder $qb)
 {
     $sql = $qb->getDQL();
     $parameters = $qb->getParameters();
     /** @var Query\Parameter $param */
     foreach ($parameters as $param) {
         if (is_array($param->getValue()) && !empty($param->getValue())) {
             $sql = str_replace(':' . $param->getName(), "'" . implode("', '", $param->getValue()) . "'", $sql);
         } else {
             $sql = str_replace(':' . $param->getName(), "'" . $param->getValue() . "'", $sql);
         }
     }
     return $sql;
 }

 /**
  * Constructor.
  *
  * @access     public
  * @param      QueryBuilder $oQuery
  * @param      string       $sMainTableAlias
  * @param      integer      $iResultsPerPage
  * @throws     \Doctrine\ORM\NoResultException
  * @throws     \Doctrine\ORM\NonUniqueResultException
  * @internal   param string $sGetName
  * @since      1.0.0-alpha
  * @version    1.0.0-alpha
  */
 public function __construct(QueryBuilder &$oQuery, $sMainTableAlias, $iResultsPerPage = 15)
 {
     Log::insert('Pager helper initialized for query: "' . $oQuery->getDQL() . '".');
     $iPageParamValue = filter_input(INPUT_GET, 'page', FILTER_VALIDATE_INT);
     if (!empty($iPageParamValue)) {
         $this->iPage = $iPageParamValue;
     }
     $oQueryAmount = clone $oQuery;
     $aAmount = $oQueryAmount->select('COUNT(' . $sMainTableAlias . ')')->getQuery()->getSingleResult();
     $this->iAmount = (int) $aAmount[1];
     $this->iResultsPerPage = (int) $iResultsPerPage;
     $iFirstResult = $this->iResultsPerPage * ($this->iPage - 1);
     $oQuery->setMaxResults($this->iResultsPerPage)->setFirstResult($iFirstResult);
 }

 /**
  * @dataProvider applyDataProvider
  */
 public function testApply($platform, $value, $comparisonType, $expectedDql, $expectedParams)
 {
     $paramCounter = 0;
     $em = $this->getMock('Doctrine\\ORM\\EntityManagerInterface');
     $qb = new QueryBuilder($em);
     $ds = $this->getMock('Oro\\Bundle\\FilterBundle\\Datasource\\Orm\\OrmFilterDatasourceAdapter', ['generateParameterName', 'getDatabasePlatform'], [$qb]);
     $ds->expects($this->any())->method('generateParameterName')->willReturnCallback(function () use(&$paramCounter) {
         return sprintf('param%s', ++$paramCounter);
     });
     $ds->expects($this->any())->method('getDatabasePlatform')->willReturn($platform);
     $data = ['type' => $comparisonType, 'value' => $value];
     $em->expects($this->any())->method('getExpressionBuilder')->willReturn(new Query\Expr());
     $qb->select('j')->from('TestEntity', 'j');
     $this->filter->apply($ds, $data);
     $this->assertEquals($expectedDql, $qb->getDQL());
     $params = [];
     /** @var Query\Parameter $param */
     foreach ($qb->getParameters() as $param) {
         $params[$param->getName()] = $param->getValue();
     }
     $this->assertEquals($expectedParams, $params);
 }

 /**
  * @expectedException LogicException
  * @expectedExceptionMessage Computed conditions cannot be mixed with uncomputed.
  */
 public function testComputedWithUnComputedRestrictionsTogetherShouldReturnExceptionWhenRestrictionsAreMixed()
 {
     $qb = new QueryBuilder($this->getTestEntityManager());
     $qb->select(['u.status, COUNT(u.id)'])->from('Oro\\Bundle\\QueryDesignerBundle\\Tests\\Unit\\Fixtures\\Models\\CMS\\CmsUser', 'u')->groupBy('u.status');
     $ds = new GroupingOrmFilterDatasourceAdapter($qb);
     $ds->addRestriction($qb->expr()->eq('u.id', '1'), FilterUtility::CONDITION_AND);
     $ds->addRestriction($qb->expr()->eq('COUNT(u.id)', '2'), FilterUtility::CONDITION_AND, true);
     $ds->addRestriction($qb->expr()->eq('COUNT(u.id)', '3'), FilterUtility::CONDITION_OR);
     $ds->applyRestrictions();
     $this->assertEquals('SELECT u.status, COUNT(u.id) FROM Oro\\Bundle\\QueryDesignerBundle\\Tests\\Unit\\Fixtures\\Models\\CMS\\CmsUser u ' . 'WHERE u.id = 1 AND u.id = 2 ' . 'GROUP BY u.status ' . 'HAVING COUNT(u.id) = 3 OR COUNT(u.id) = 4', $qb->getDQL());
 }

 /**
  * Add the given subquery as a new clause in the main query's  
  * WHERE clause using and an AND or OR operator. 
  * <p>
  * Important: The method *APPENDS* the suquery as a new restriction in the 
  * WHERE clause, forming a logical AND/OR conjunction with any 
  * **PREVIOUSLY** specified restrictions in the WHERE clause.
  * 
  * @param QueryBuilder $sQ
  * @param String $operator AND, OR or NOT 
  */
 private function addSubQueryToMainQuery($sQ, $operator)
 {
     //Get the query that was passed at initialization
     $qb = $this->getQB();
     // QueryBuilder orWhere() + addWhere() *APPENDS* the specified restrictions to the
     // query results, forming a logical AND/OR conjunction with any **PREVIOUSLY**
     // specified restrictions! Therefore, the order of AND/OR in URL query string
     // is significant (we iterate subqueries from left to right, each time
     // adding the new conjunction to the previous restrictions.
     switch ($operator) {
         case 'OR':
             //OR
             $qb->orWhere($qb->expr()->in($this->ltype, $sQ->getDQL()));
             break;
         case 'AND':
             //AND
             $qb->andWhere($qb->expr()->in($this->ltype, $sQ->getDQL()));
             break;
         case 'NOT':
             //NOT
             $qb->andWhere($qb->expr()->notIn($this->ltype, $sQ->getDQL()));
             break;
     }
     /*finally replace original query with updated query ready for fetching
      *by the calling class*/
     $this->setQB($qb);
 }

 protected function applySorters(QueryBuilder $qb, array $sorters, callable $handler = null)
 {
     foreach ($sorters as $key => $direction) {
         // custom handling
         if (null !== $handler) {
             $dql = $qb->getDQL();
             // will check for difference
             call_user_func_array($handler, [$qb, $key, $direction]);
             if ($qb->getDQL() !== $dql) {
                 continue;
                 // custom sorter handler has handled the parameter
             }
         }
         $qb->addOrderBy($key, in_array(strtoupper($direction), ['ASC', 'DESC']) ? $direction : 'ASC');
     }
 }

 public function testComplexExpr()
 {
     $qb = new QueryBuilder($this->getTestEntityManager());
     $qb->select(['u.id'])->from('Oro\\Bundle\\QueryDesignerBundle\\Tests\\Unit\\Fixtures\\Models\\CMS\\CmsUser', 'u')->where('u.id = 0');
     $ds = new GroupingOrmFilterDatasourceAdapter($qb);
     //  src: (1 AND ((2 AND (3 OR 4)) OR (5) OR (6 AND 7)) AND 8)
     // dest: (1 AND ((2 AND (3 OR 4)) OR 5 OR (6 AND 7)) AND 8)
     $ds->addRestriction($qb->expr()->eq('u.name', '1'), FilterUtility::CONDITION_AND);
     $ds->beginRestrictionGroup(FilterUtility::CONDITION_AND);
     $ds->beginRestrictionGroup(FilterUtility::CONDITION_AND);
     $ds->addRestriction($qb->expr()->eq('u.name', '2'), FilterUtility::CONDITION_AND);
     $ds->beginRestrictionGroup(FilterUtility::CONDITION_AND);
     $ds->addRestriction($qb->expr()->eq('u.name', '3'), FilterUtility::CONDITION_AND);
     $ds->addRestriction($qb->expr()->eq('u.name', '4'), FilterUtility::CONDITION_OR);
     $ds->endRestrictionGroup();
     $ds->endRestrictionGroup();
     $ds->beginRestrictionGroup(FilterUtility::CONDITION_OR);
     $ds->addRestriction($qb->expr()->eq('u.name', '5'), FilterUtility::CONDITION_AND);
     $ds->endRestrictionGroup();
     $ds->beginRestrictionGroup(FilterUtility::CONDITION_OR);
     $ds->addRestriction($qb->expr()->eq('u.name', '6'), FilterUtility::CONDITION_AND);
     $ds->addRestriction($qb->expr()->eq('u.name', '7'), FilterUtility::CONDITION_AND);
     $ds->endRestrictionGroup();
     $ds->endRestrictionGroup();
     $ds->addRestriction($qb->expr()->eq('u.name', '8'), FilterUtility::CONDITION_AND);
     $ds->applyRestrictions();
     $this->assertEquals('SELECT u.id FROM Oro\\Bundle\\QueryDesignerBundle\\Tests\\Unit\\Fixtures\\Models\\CMS\\CmsUser u ' . 'WHERE u.id = 0 AND ' . '(u.name = 1 AND ' . '((u.name = 2 AND (u.name = 3 OR u.name = 4)) OR u.name = 5 OR (u.name = 6 AND u.name = 7)) AND ' . 'u.name = 8)', $qb->getDQL());
 }

 /**
  * @param QueryBuilder $pqb
  * @param QueryBuilder $aqb
  */
 private function getPeopleNotInTable($pqb, $aqb)
 {
     $pqb->andWhere($pqb->expr()->notIn('p.id', $aqb->getDQL()));
 }