PHP QueryBuilder::addGroupBy方法代码示例

 /**
  * @param QueryBuilder $qb
  * @param string       $dqlAlias
  */
 public function modify(QueryBuilder $qb, $dqlAlias)
 {
     if ($this->dqlAlias !== null) {
         $dqlAlias = $this->dqlAlias;
     }
     $qb->addGroupBy(sprintf('%s.%s', $dqlAlias, $this->field));
 }

 /**
  * Prepare query builder to get modules list
  *
  * @param \Doctrine\ORM\QueryBuilder $queryBuilder Query builder to prepare
  *
  * @return void
  */
 protected function addGroupByCondition(\Doctrine\ORM\QueryBuilder $queryBuilder)
 {
     $queryBuilder->addGroupBy('m.name')->addGroupBy('m.author');
 }

 /**
  * 
  * @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();
//.........这里部分代码省略.........

 /**
  * Add 'Group By product_id' expression
  *
  * @param \Doctrine\ORM\QueryBuilder $qb Query builder routine
  *
  * @return void
  */
 protected function addGroupByProductId(\Doctrine\ORM\QueryBuilder $qb)
 {
     $qb->addGroupBy('p.product_id');
 }

 /**
  * @param DateTime     $start
  * @param DateTime     $end
  * @param QueryBuilder $qb
  * @param              $entityField
  */
 public function addDatePartsSelect(DateTime $start, DateTime $end, QueryBuilder $qb, $entityField)
 {
     switch ($this->getFormatStrings($start, $end)['viewType']) {
         case 'year':
             $qb->addSelect(sprintf('%s as yearCreated', $this->getEnforcedTimezoneFunction('YEAR', $entityField)));
             $qb->addGroupBy('yearCreated');
             break;
         case 'month':
             $qb->addSelect(sprintf('%s as yearCreated', $this->getEnforcedTimezoneFunction('YEAR', $entityField)));
             $qb->addSelect(sprintf('%s as monthCreated', $this->getEnforcedTimezoneFunction('MONTH', $entityField)));
             $qb->addGroupBy('yearCreated');
             $qb->addGroupBy('monthCreated');
             break;
         case 'date':
             $qb->addSelect(sprintf("%s as yearCreated", $this->getEnforcedTimezoneFunction('YEAR', $entityField)));
             $qb->addSelect(sprintf('%s as weekCreated', $this->getEnforcedTimezoneFunction('WEEK', $entityField)));
             $qb->addGroupBy('yearCreated');
             $qb->addGroupBy('weekCreated');
             break;
         case 'day':
             $qb->addSelect(sprintf("%s as yearCreated", $this->getEnforcedTimezoneFunction('YEAR', $entityField)));
             $qb->addSelect(sprintf("%s as monthCreated", $this->getEnforcedTimezoneFunction('MONTH', $entityField)));
             $qb->addSelect(sprintf("%s as dayCreated", $this->getEnforcedTimezoneFunction('DAY', $entityField)));
             $qb->addGroupBy('yearCreated');
             $qb->addGroupBy('monthCreated');
             $qb->addGroupBy('dayCreated');
             break;
         case 'time':
             $qb->addSelect(sprintf('%s as dateCreated', $this->getEnforcedTimezoneFunction('DATE', $entityField)));
             $qb->addSelect(sprintf('%s as hourCreated', $this->getEnforcedTimezoneFunction('HOUR', $entityField)));
             $qb->addGroupBy('dateCreated');
             $qb->addGroupBy('hourCreated');
             break;
     }
 }

 /**
  * @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 \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->manager->createQueryBuilder($this->class);
     $this->query->from($this->class, self::TABLE_ALIAS);
     $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) {
         $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) {
                 $operator = $this->normalizeOperator($filter->getOperator());
                 $q = $this->query->expr()->{$operator}($this->getFieldName($column, false, $hasHavingClause), "?{$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->having($sub);
             } elseif ($isDisjunction) {
                 $where->add($sub);
             }
         }
         if ($column->getType() === 'array') {
             $serializeColumns[] = $column->getId();
         }
     }
     if ($where->count() > 0) {
         $this->query->where($where);
     }
     foreach ($this->joins as $alias => $field) {
         $this->query->leftJoin($field, $alias);
         $this->querySelectfromSource->leftJoin($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();
     // hydrate result
     $result = new Rows();
     foreach ($items as $item) {
         $row = new Row();
         foreach ($item as $key => $value) {
             $key = str_replace('::', '.', $key);
             if (in_array($key, $serializeColumns) && is_string($value)) {
                 $value = unserialize($value);
             }
             $row->setField($key, $value);
         }
         //call overridden prepareRow or associated closure
         if (($modifiedRow = $this->prepareRow($row)) != null) {
             $result->addRow($modifiedRow);
         }
     }
     return $result;
 }

 /**
  * Traite les extras pour la requête.
  *
  * @param \Doctrine\ORM\QueryBuilder $queryBuilder QueryBuilder
  * @param array $extras Extras
  * @return \Doctrine\ORM\QueryBuilder QueryBuilder à jour
  */
 private function processQueryBuilderExtras(QueryBuilder $queryBuilder, array $extras)
 {
     if (isset($extras[self::SELECTS])) {
         foreach ($extras[self::SELECTS] as $select => $selectAlias) {
             if (self::SELECT_JOIN == $selectAlias) {
                 $queryBuilder->addSelect($select);
             } else {
                 $queryBuilder->addSelect($this->getCompleteProperty($select) . ' AS ' . $selectAlias);
             }
         }
     }
     if (isset($extras[self::LEFT_JOINS])) {
         foreach ($extras[self::LEFT_JOINS] as $leftJoin => $leftJoinAlias) {
             $queryBuilder->leftJoin($this->getCompleteProperty($leftJoin), $leftJoinAlias);
         }
     }
     if (isset($extras[self::INNER_JOINS])) {
         foreach ($extras[self::INNER_JOINS] as $innerJoin => $innerJoinAlias) {
             $queryBuilder->innerJoin($this->getCompleteProperty($innerJoin), $innerJoinAlias);
         }
     }
     if (isset($extras[self::GROUP_BYS])) {
         foreach ($extras[self::GROUP_BYS] as $groupBy) {
             if (isset($extras[self::SELECTS]) && in_array($groupBy, array_values($extras[self::SELECTS]))) {
                 $queryBuilder->addGroupBy($groupBy);
             } else {
                 $queryBuilder->addGroupBy($this->getCompleteProperty($groupBy));
             }
         }
     }
     return $queryBuilder;
 }