PHP QueryBuilder::andHaving方法代码示例

 /**
  * Adds a new WHERE or HAVING restriction depends on the given parameters.
  *
  * @param mixed  $restriction The restriction to add.
  * @param string $condition   The condition.
  *                            Can be FilterUtility::CONDITION_OR or FilterUtility::CONDITION_AND.
  * @param bool   $isComputed  Specifies whether the restriction should be added to the HAVING part of a query.
  */
 public function addRestriction($restriction, $condition, $isComputed = false)
 {
     $restriction = $this->qbTools->replaceAliasesWithFields($restriction);
     if ($condition === FilterUtility::CONDITION_OR) {
         if ($isComputed) {
             $this->qb->orHaving($restriction);
         } else {
             $this->qb->orWhere($restriction);
         }
     } else {
         if ($isComputed) {
             $this->qb->andHaving($restriction);
         } else {
             $this->qb->andWhere($restriction);
         }
     }
 }

 /**
  * Adds a new WHERE or HAVING restriction depends on the given parameters.
  *
  * @param mixed  $restriction The restriction to add.
  * @param string $condition   The condition.
  *                            Can be FilterUtility::CONDITION_OR or FilterUtility::CONDITION_AND.
  * @param bool   $isComputed  Specifies whether the restriction should be added to the HAVING part of a query.
  */
 public function addRestriction($restriction, $condition, $isComputed = false)
 {
     if ($this->fixComparison($restriction, $condition)) {
         return;
     }
     if ($condition === FilterUtility::CONDITION_OR) {
         if ($isComputed) {
             $this->qb->orHaving($restriction);
         } else {
             $this->qb->orWhere($restriction);
         }
     } else {
         if ($isComputed) {
             $this->qb->andHaving($restriction);
         } else {
             $this->qb->andWhere($restriction);
         }
     }
 }

 /**
  * Add labels.
  *
  * @param Search $entity
  *
  * @return Builder
  */
 public function addLabels(Search $entity)
 {
     if ($entity->getLabels()->count()) {
         $this->add(function (QueryBuilder $query) use($entity) {
             $ids = [];
             foreach ($entity->getLabels() as $label) {
                 $ids[] = (int) $label->getId();
             }
             $query->innerJoin('i.labels', 'l')->andWhere('l.id IN (' . implode(',', $ids) . ')');
         });
         $this->select->andHaving('COUNT(i.id) = ' . $entity->getLabels()->count());
     }
     return $this;
 }

 /**
  * @param QueryBuilder $queryBuilder
  *
  * @return void
  */
 public function apply($queryBuilder)
 {
     $compiler = new SqlFilterCompiler();
     // @codingStandardsIgnoreStart
     // $compiler->setSkipMissingProperties(true);
     // @codingStandardsIgnoreEnd
     $compiler->compile($this->properties, $this->filter);
     $select = $compiler->getSelect(false) and $queryBuilder->addSelect($select);
     $where = $compiler->getWhere(false) and $queryBuilder->andWhere($where);
     $having = $compiler->getHaving(false) and $queryBuilder->andHaving($having);
     $order = $compiler->getOrder(false) and $queryBuilder->addOrderBy($order);
     foreach ($compiler->getParameters() as $key => $value) {
         $queryBuilder->setParameter($key, $value);
     }
 }

 /**
  * @param QueryBuilder $qb
  */
 public function applyRestrictions(QueryBuilder $qb)
 {
     if (!$this->groupNode) {
         return;
     }
     $violationList = $this->validator->validate($this->groupNode, new GroupNodeConstraint());
     foreach ($violationList as $violation) {
         throw new LogicException($violation->getMessage());
     }
     list($uncomputedExpr, $computedExpr) = $this->resolveGroupNode($this->groupNode);
     if ($computedExpr) {
         $qb->andHaving($computedExpr);
     }
     if ($uncomputedExpr) {
         $qb->andWhere($uncomputedExpr);
     }
 }

 /**
  * 
  * @param type $columns
  * @param type $page
  * @param type $limit
  * @param type $maxResults
  * @param type $gridDataJunction
  * @return \APY\DataGridBundle\Grid\Rows
  */
 public function execute($columns, $page = 0, $limit = 0, $maxResults = null, $gridDataJunction = Column::DATA_CONJUNCTION)
 {
     $this->query = $this->getQueryBuilder();
     $this->querySelectfromSource = clone $this->query;
     $bindIndex = 123;
     $serializeColumns = array();
     $where = $gridDataJunction === Column::DATA_CONJUNCTION ? $this->query->expr()->andx() : $this->query->expr()->orx();
     foreach ($columns as $column) {
         if (!in_array($column->getId(), $this->excludedColumns)) {
             $fieldName = $this->getFieldName($column, true);
             $this->query->addSelect($fieldName);
             $this->querySelectfromSource->addSelect($fieldName);
             if ($column->isSorted()) {
                 $this->query->orderBy($this->getFieldName($column), $column->getOrder());
             }
             if ($column->isFiltered()) {
                 // Some attributes of the column can be changed in this function
                 $filters = $column->getFilters('entity');
                 $isDisjunction = $column->getDataJunction() === Column::DATA_DISJUNCTION;
                 $hasHavingClause = $column->hasDQLFunction();
                 $sub = $isDisjunction ? $this->query->expr()->orx() : ($hasHavingClause ? $this->query->expr()->andx() : $where);
                 foreach ($filters as $filter) {
                     // \Doctrine\Common\Util\Debug::dump($column);
                     $operator = $this->normalizeOperator($filter->getOperator());
                     $q = $this->query->expr()->{$operator}($this->getFieldName($column, false), "?{$bindIndex}");
                     if ($filter->getOperator() == Column::OPERATOR_NLIKE) {
                         $q = $this->query->expr()->not($q);
                     }
                     $sub->add($q);
                     if ($filter->getValue() !== null) {
                         $this->query->setParameter($bindIndex++, $this->normalizeValue($filter->getOperator(), $filter->getValue()));
                     }
                 }
                 if ($hasHavingClause) {
                     $this->query->andHaving($sub);
                 } elseif ($isDisjunction) {
                     $where->add($sub);
                 }
             }
             if ($column->getType() === 'array') {
                 $serializeColumns[] = $column->getId();
             }
         }
     }
     if ($where->count() > 0) {
         //Using ->andWhere here to make sure we preserve any other where clauses present in the query builder
         //the other where clauses may have come from an external builder
         $this->query->andWhere($where);
     }
     foreach ($this->joins as $alias => $field) {
         if (null !== $field['type'] && strtolower($field['type']) === 'inner') {
             $join = 'join';
         } else {
             $join = 'leftJoin';
         }
         $this->query->{$join}($field['field'], $alias);
         $this->querySelectfromSource->{$join}($field['field'], $alias);
     }
     if ($page > 0) {
         $this->query->setFirstResult($page * $limit);
     }
     if ($limit > 0) {
         if ($maxResults !== null && $maxResults - $page * $limit < $limit) {
             $limit = $maxResults - $page * $limit;
         }
         $this->query->setMaxResults($limit);
     } elseif ($maxResults !== null) {
         $this->query->setMaxResults($maxResults);
     }
     if (!empty($this->groupBy)) {
         $this->query->resetDQLPart('groupBy');
         $this->querySelectfromSource->resetDQLPart('groupBy');
         foreach ($this->groupBy as $field) {
             $this->query->addGroupBy($this->getGroupByFieldName($field));
             $this->querySelectfromSource->addGroupBy($this->getGroupByFieldName($field));
         }
     }
     if ($this->groupById === true) {
         $this->query->addGroupBy($this->getGroupByFieldName('id'));
     }
     //call overridden prepareQuery or associated closure
     $this->prepareQuery($this->query);
     $query = $this->query->getQuery();
     foreach ($this->hints as $hintKey => $hintValue) {
         $query->setHint($hintKey, $hintValue);
     }
     $query->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, 'TMSolution\\DataGridBundle\\Walker\\MysqlWalker');
     $query->setHint("mysqlWalker.count", true);
     $items = $query->getResult(\Doctrine\ORM\Query::HYDRATE_ARRAY);
     $this->prepareTotalCount();
     //        $oids = array();
//.........这里部分代码省略.........

 /**
  * Adds additional where/having clauses for given dataset's column
  *
  * @param QueryBuilder       $queryBuilder
  * @param ColumnInterface    $column
  * @param ConditionInterface $condition
  */
 protected function addColumnConditionToQueryBuilder(QueryBuilder $queryBuilder, ColumnInterface $column, ConditionInterface $condition)
 {
     $source = $column->getSource();
     $alias = $column->getAlias();
     $operator = $condition->getOperator();
     $identifier = sprintf('%s_%s', $condition->getIdentifier(), $this->paramIteration++);
     if ($column->isAggregated()) {
         $expression = $queryBuilder->expr()->{$operator}($alias, ':' . $identifier);
         $queryBuilder->andHaving($expression);
     } else {
         $expression = $queryBuilder->expr()->{$operator}($source, ':' . $identifier);
         $queryBuilder->andWhere($expression);
     }
     $queryBuilder->setParameter($identifier, $condition->getValue());
 }

 /**
  * @param \APY\DataGridBundle\Grid\Column\Column[] $columns
  * @param int $page Page Number
  * @param int $limit Rows Per Page
  * @param int $gridDataJunction  Grid data junction
  * @return \APY\DataGridBundle\Grid\Rows
  */
 public function execute($columns, $page = 0, $limit = 0, $maxResults = null, $gridDataJunction = Column::DATA_CONJUNCTION)
 {
     $this->query = $this->getQueryBuilder();
     $this->querySelectfromSource = clone $this->query;
     $bindIndex = 123;
     $serializeColumns = array();
     $where = $gridDataJunction === Column::DATA_CONJUNCTION ? $this->query->expr()->andx() : $this->query->expr()->orx();
     foreach ($columns as $column) {
         // If a column is a manual field, ie a.col*b.col as myfield, it is added to select from user.
         if ($column->getIsManualField() === false) {
             $fieldName = $this->getFieldName($column, true);
             $this->query->addSelect($fieldName);
             $this->querySelectfromSource->addSelect($fieldName);
         }
         if ($column->isSorted()) {
             $this->query->orderBy($this->getFieldName($column), $column->getOrder());
         }
         if ($column->isFiltered()) {
             // Some attributes of the column can be changed in this function
             $filters = $column->getFilters('entity');
             $isDisjunction = $column->getDataJunction() === Column::DATA_DISJUNCTION;
             $hasHavingClause = $column->hasDQLFunction() || $column->getIsAggregate();
             $sub = $isDisjunction ? $this->query->expr()->orx() : ($hasHavingClause ? $this->query->expr()->andx() : $where);
             foreach ($filters as $filter) {
                 $operator = $this->normalizeOperator($filter->getOperator());
                 $q = $this->query->expr()->{$operator}($this->getFieldName($column, false), "?{$bindIndex}");
                 if ($filter->getOperator() == Column::OPERATOR_NLIKE) {
                     $q = $this->query->expr()->not($q);
                 }
                 $sub->add($q);
                 if ($filter->getValue() !== null) {
                     $this->query->setParameter($bindIndex++, $this->normalizeValue($filter->getOperator(), $filter->getValue()));
                 }
             }
             if ($hasHavingClause) {
                 $this->query->andHaving($sub);
             } elseif ($isDisjunction) {
                 $where->add($sub);
             }
         }
         if ($column->getType() === 'array') {
             $serializeColumns[] = $column->getId();
         }
     }
     if ($where->count() > 0) {
         //Using ->andWhere here to make sure we preserve any other where clauses present in the query builder
         //the other where clauses may have come from an external builder
         $this->query->andWhere($where);
     }
     foreach ($this->joins as $alias => $field) {
         if (null !== $field['type'] && strtolower($field['type']) === 'inner') {
             $join = 'join';
         } else {
             $join = 'leftJoin';
         }
         $this->query->{$join}($field['field'], $alias);
         $this->querySelectfromSource->{$join}($field['field'], $alias);
     }
     if ($page > 0) {
         $this->query->setFirstResult($page * $limit);
     }
     if ($limit > 0) {
         if ($maxResults !== null && $maxResults - $page * $limit < $limit) {
             $limit = $maxResults - $page * $limit;
         }
         $this->query->setMaxResults($limit);
     } elseif ($maxResults !== null) {
         $this->query->setMaxResults($maxResults);
     }
     if (!empty($this->groupBy)) {
         $this->query->resetDQLPart('groupBy');
         $this->querySelectfromSource->resetDQLPart('groupBy');
         foreach ($this->groupBy as $field) {
             $this->query->addGroupBy($this->getGroupByFieldName($field));
             $this->querySelectfromSource->addGroupBy($this->getGroupByFieldName($field));
         }
     }
     //call overridden prepareQuery or associated closure
     $this->prepareQuery($this->query);
     $query = $this->query->getQuery();
     foreach ($this->hints as $hintKey => $hintValue) {
         $query->setHint($hintKey, $hintValue);
     }
     $items = $query->getResult();
     $repository = $this->manager->getRepository($this->entityName);
     // Force the primary field to get the entity in the manipulatorRow
     $primaryColumnId = null;
     foreach ($columns as $column) {
         if ($column->isPrimary()) {
             $primaryColumnId = $column->getId();
             break;
         }
     }
//.........这里部分代码省略.........

 /**
  * @param Table                      $table
  * @param Request                    $request
  * @param \Doctrine\ORM\QueryBuilder $queryBuilder
  */
 private function addSearch(Table $table, Request $request, \Doctrine\ORM\QueryBuilder $queryBuilder)
 {
     $queryParams = $request->get($table->getFormId());
     // @todo handle all kind of filters
     foreach ($table->getAllFilters() as $filter) {
         if (isset($queryParams[$filter->getName()])) {
             $searchParamRaw = trim($queryParams[$filter->getName()]);
             list($operator, $searchParam) = $filter->getOperatorAndValue($searchParamRaw);
             if ((string) $searchParam != '') {
                 list($searchOperator, $formattedSearch) = $filter->getFormattedInput($operator, $searchParam);
                 $sql = false;
                 // depending on operator
                 switch ($searchOperator) {
                     case Filter::TYPE_GREATER:
                     case Filter::TYPE_GREATER_OR_EQUAL:
                     case Filter::TYPE_LESS:
                     case Filter::TYPE_LESS_OR_EQUAL:
                     case Filter::TYPE_NOT_EQUAL:
                         $sql = $filter->getField() . " {$searchOperator} :filter_" . $filter->getName();
                         $queryBuilder->setParameter('filter_' . $filter->getName(), $formattedSearch);
                         break;
                     case Filter::TYPE_EQUAL_STRICT:
                         $sql = $filter->getField() . ' = :filter_' . $filter->getName();
                         $queryBuilder->setParameter('filter_' . $filter->getName(), $formattedSearch);
                         break;
                     case Filter::TYPE_EQUAL:
                         $sql = $filter->getField() . ' like :filter_' . $filter->getName();
                         $queryBuilder->setParameter('filter_' . $filter->getName(), $formattedSearch);
                         break;
                     case Filter::TYPE_NOT_LIKE:
                         $sql = $filter->getField() . ' not like :filter_' . $filter->getName();
                         $queryBuilder->setParameter('filter_' . $filter->getName(), '%' . $formattedSearch . '%');
                         break;
                     case Filter::TYPE_NULL:
                         $sql = $filter->getField() . ' IS NULL';
                         break;
                     case Filter::TYPE_NOT_NULL:
                         $sql = $filter->getField() . ' IS NOT NULL';
                         break;
                     case Filter::TYPE_IN:
                         $sql = $filter->getField() . ' IN (:filter_' . $filter->getName() . ')';
                         $queryBuilder->setParameter('filter_' . $filter->getName(), '%' . $formattedSearch . '%');
                         break;
                     case Filter::TYPE_NOT_IN:
                         $sql = $filter->getField() . ' NOT IN (:filter_' . $filter->getName() . ')';
                         $queryBuilder->setParameter('filter_' . $filter->getName(), '%' . $formattedSearch . '%');
                         break;
                     default:
                     case Filter::TYPE_LIKE:
                         $sql = $filter->getField() . ' like :filter_' . $filter->getName();
                         $queryBuilder->setParameter('filter_' . $filter->getName(), '%' . $formattedSearch . '%');
                         break;
                 }
                 if (!is_null($sql)) {
                     if ($filter->getHaving()) {
                         $queryBuilder->andHaving($sql);
                     } else {
                         $queryBuilder->andWhere($sql);
                     }
                 }
             }
         }
     }
 }

 /**
  * Traite les conditions de type HAVING de la requête.
  *
  * @param \Doctrine\ORM\QueryBuilder $queryBuilder QueryBuilder
  * @param array $conditions Conditions de la recherche
  * @return \Doctrine\ORM\QueryBuilder QueryBuilder à jour
  */
 private function processQueryBuilderHavings(QueryBuilder $queryBuilder, array $conditions)
 {
     foreach ($conditions as $conditionPropriete => $conditionValeur) {
         if ($this->conditionIsHaving($conditionPropriete)) {
             $queryBuilder->andHaving($this->processQueryBuilderHaving($queryBuilder, $conditionPropriete, $conditionValeur));
         }
     }
     return $queryBuilder;
 }