PHP QueryBuilder::having方法代码示例

 /**
  * @param QueryBuilder $qb
  * @param string       $dqlAlias
  *
  * @return string
  */
 public function getFilter(QueryBuilder $qb, $dqlAlias)
 {
     if ($this->child instanceof Filter) {
         $qb->having($this->child->getFilter($qb, $dqlAlias));
     } else {
         $qb->having($this->child);
     }
 }

 /**
  * @param QueryBuilder $qb
  * @param Search $search
  * @return QueryBuilder
  */
 public function map(QueryBuilder $qb, Search $search)
 {
     $qb->select('c, s')->from('EtuModuleCovoitBundle:Covoit', 'c')->leftJoin('c.subscriptions', 's');
     if (!$search->olds) {
         $qb->andWhere('c.date > CURRENT_DATE()');
     }
     if ($search->startCity) {
         $qb->andWhere('c.startCity = :startCity')->setParameter('startCity', $search->startCity->getId());
     }
     if ($search->endCity) {
         $qb->andWhere('c.endCity = :endCity')->setParameter('endCity', $search->endCity->getId());
     }
     if ($search->date) {
         if ($search->dateBeforeAfter) {
             $before = clone $search->date;
             $before->add(\DateInterval::createFromDateString('-1 day'));
             $after = clone $search->date;
             $after->add(\DateInterval::createFromDateString('1 day'));
             $qb->andWhere('c.date BETWEEN :before AND :after')->setParameter('before', $before->format('Y-m-d') . ' 00:00:00')->setParameter('after', $after->format('Y-m-d') . ' 00:00:00');
         } else {
             $qb->andWhere('c.date = :date')->setParameter('date', $search->date->format('Y-m-d') . ' 00:00:00');
         }
     }
     if ($search->priceMax) {
         $qb->andWhere('c.price <= :priceMax')->setParameter('priceMax', $search->priceMax);
     }
     if ($search->hourMin) {
         $qb->andWhere('c.startHour >= :hourMin')->setParameter('hourMin', $search->hourMin->format('H:i:s'));
     }
     if ($search->hourMax) {
         $qb->andWhere('c.startHour <= :hourMax')->setParameter('hourMax', $search->hourMax->format('H:i:s'));
     }
     if ($search->keywords) {
         $qb->andWhere('c.notes LIKE :keywords')->setParameter('keywords', '%' . implode('%', explode(' ', $search->keywords)) . '%');
     }
     if ($search->placesLeft) {
         $qb->having('(c.capacity - COUNT(s)) >= :placesLeft')->setParameter('placesLeft', $search->placesLeft);
     }
     return $qb;
 }

 /**
  * @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;
 }

 /**
  * @param \Doctrine\ORM\QueryBuilder $queryBuilder
  * @param $filter
  *
  * @return QueryBuilder
  */
 private function processFilter(QueryBuilder $queryBuilder, $filter)
 {
     if (isset($filter['searchString']) && '' !== $filter['searchString']) {
         $locationExpr = $queryBuilder->expr()->orX();
         foreach (explode(' ', $filter['searchString']) as $keywordIndex => $keyword) {
             $queryParameter = 'searchString' . $keywordIndex;
             $queryBuilder->andWhere('event.title LIKE :' . $queryParameter);
             $queryBuilder->setParameter($queryParameter, '%' . trim($keyword) . '%');
             $locationExpr->add($queryBuilder->expr()->like('event.city', ':' . $queryParameter));
         }
         if (!isset($filter['lat']) && !isset($filter['long'])) {
             $queryBuilder->orWhere($locationExpr);
         }
     }
     if (isset($filter['eventIds'])) {
         $queryBuilder->andWhere('event.id IN (:eventIds)');
         $queryBuilder->setParameter('eventIds', $filter['eventIds']);
     }
     if (isset($filter['isTopEvent'])) {
         $queryBuilder->andWhere('event.isTopEvent = :isTopEvent');
         $queryBuilder->setParameter('isTopEvent', $filter['isTopEvent']);
     }
     if (isset($filter['categories']) && '' !== $filter['categories']) {
         $queryBuilder->leftJoin('event.categories', 'eventCategories');
         $queryBuilder->andWhere('eventCategories.id IN (:categories)');
         $queryBuilder->setParameter('categories', $filter['categories']);
     }
     if (isset($filter['dateFrom']) && '' !== $filter['dateFrom']) {
         /** @var DateTime $dateFrom */
         $dateFrom = $filter['dateFrom'];
         $queryBuilder->andWhere('event.startDate >= :startDate');
         $queryBuilder->setParameter('startDate', $dateFrom->format('Y-m-d'));
     }
     if (isset($filter['dateFrom']) && '' !== $filter['dateFrom'] && (isset($filter['dateTo']) && '' !== $filter['dateTo'])) {
         /** @var DateTime $dateTo */
         $dateTo = $filter['dateTo'];
         $queryBuilder->andWhere('event.startDate <= :startEnd');
         $queryBuilder->setParameter('startEnd', $dateTo->format('Y-m-d'));
     } elseif (isset($filter['dateTo']) && '' !== $filter['dateTo']) {
         /** @var DateTime $dateTo */
         $dateTo = $filter['dateTo'];
         $queryBuilder->andWhere('event.startDate <= :startEnd');
         $queryBuilder->setParameter('startEnd', $dateTo->format('Y-m-d'));
     }
     if (isset($filter['lat']) && isset($filter['long'])) {
         $queryBuilder->addSelect('( 3959 * acos(cos(radians(:latitude)) ' . '* cos(radians(event.latitude)) ' . '* cos(radians(event.longitude) ' . '- radians(:longitude)) ' . '+ sin(radians(:latitude)) ' . '* sin(radians(event.latitude)))) AS distance');
         $queryBuilder->setParameter('latitude', $filter['lat']);
         $queryBuilder->setParameter('longitude', $filter['long']);
         $queryBuilder->having('distance <= :area');
         $queryBuilder->setParameter('area', $filter['area']);
         $queryBuilder->orderBy('distance', 'ASC');
     } else {
         $queryBuilder->orderBy('event.startDate', 'ASC');
     }
     if (isset($filter['country'])) {
         $queryBuilder->andWhere('event.country = :country');
         $queryBuilder->setParameter('country', $filter['country']);
     }
     return $queryBuilder;
 }

 /**
  * {@inheritdoc}
  */
 public function parse($value, QueryBuilder $qb)
 {
     if (!is_array($value)) {
         $value = Yaml::parse($value);
     }
     $processor = new Processor();
     $value = $processor->processConfiguration(new QueryConfiguration(), $value);
     if (!isset($value['from'])) {
         throw new \RuntimeException('Missing mandatory "from" section');
     }
     foreach ((array) $value['from'] as $from) {
         $qb->from($from['table'], $from['alias']);
     }
     if (isset($value['select'])) {
         foreach ($value['select'] as $select) {
             $qb->add('select', new Expr\Select($select), true);
         }
     }
     if (isset($value['distinct'])) {
         $qb->distinct((bool) $value['distinct']);
     }
     if (isset($value['groupBy'])) {
         $qb->groupBy($value['groupBy']);
     }
     if (isset($value['having'])) {
         $qb->having($value['having']);
     }
     $this->addJoin($qb, $value);
     $this->addWhere($qb, $value);
     $this->addOrder($qb, $value);
     return $qb;
 }