PHP QueryBuilder::addSelect方法代码示例

 /**
  * {@inheritDoc}
  */
 public function sortQuery(QueryBuilder $qb, array $query)
 {
     if (is_string($query['sort_by'])) {
         if ('item_count' == $query['sort_by']) {
             $entityAlias = $this->getEntityClass();
             $valuesAlias = $this->createAlias();
             $resourceAlias = $this->createAlias();
             $countAlias = $this->createAlias();
             $qb->addSelect("COUNT({$valuesAlias}.id) HIDDEN {$countAlias}")->leftJoin("{$entityAlias}.values", $valuesAlias)->leftJoin("{$valuesAlias}.resource", $resourceAlias, 'WITH', "{$resourceAlias} INSTANCE OF Omeka\\Entity\\Item")->addGroupBy("{$entityAlias}.id")->addOrderBy($countAlias, $query['sort_order']);
         } else {
             parent::sortQuery($qb, $query);
         }
     }
 }

 /**
  * {@inheritdoc}
  */
 public function addFieldSorter($field, $direction)
 {
     $alias = 'inGroupSorter';
     $inGroupExpr = 'CASE WHEN :currentGroup MEMBER OF p.groups THEN true ELSE false END';
     $this->qb->addSelect(sprintf('%s AS %s', $inGroupExpr, $alias))->addOrderBy($alias, $direction);
     return $this;
 }

 /**
  * {@inheritdoc}
  */
 public function addFieldSorter($field, $direction, $locale = null, $scope = null)
 {
     $alias = 'inGroupSorter';
     $inGroupExpr = 'CASE WHEN :currentGroup MEMBER OF p.groups THEN true ELSE false END';
     $this->qb->addSelect(sprintf('%s AS %s', $inGroupExpr, $alias))->addOrderBy($alias, $direction);
     $idField = $this->qb->getRootAlias() . '.id';
     $this->qb->addOrderBy($idField);
     return $this;
 }

 /**
  * {@inheritdoc}
  */
 public function addFieldSorter($field, $direction)
 {
     $rootAlias = $this->qb->getRootAlias();
     $prefix = 'sorter';
     $field = $prefix . 'familyLabel';
     $family = $prefix . 'family';
     $trans = $prefix . 'familyTranslations';
     $this->qb->leftJoin($rootAlias . '.family', $family)->leftJoin($family . '.translations', $trans, 'WITH', $trans . '.locale = :dataLocale');
     $this->qb->addSelect('COALESCE(' . $trans . '.label, CONCAT(\'[\', ' . $family . '.code, \']\')) as ' . $field);
     $this->qb->addOrderBy($field, $direction);
     return $this;
 }

 /**
  * {@inheritdoc}
  */
 protected function addSelectColumn($entityClassName, $tableAlias, $fieldName, $columnExpr, $columnAlias, $columnLabel, $functionExpr, $functionReturnType, $isDistinct = false)
 {
     if ($isDistinct) {
         $columnExpr = 'DISTINCT ' . $columnExpr;
     }
     if ($functionExpr !== null) {
         $functionExpr = $this->prepareFunctionExpression($functionExpr, $tableAlias, $fieldName, $columnExpr, $columnAlias);
     }
     // @TODO find solution for aliases before generalizing this converter
     // column aliases are not used here, because of parser error
     $select = $functionExpr !== null ? $functionExpr : $columnExpr;
     $this->qb->addSelect($select);
 }

 protected function join(QueryBuilder $qb, $add = false)
 {
     $qb->leftjoin('contact.phones', 'phones');
     $qb->leftjoin('contact.account', 'account');
     $qb->leftjoin('contact.agencies', 'agencies');
     $qb->leftjoin('contact.city', 'city');
     if ($add) {
         $qb->addSelect('phones');
         $qb->addSelect('account');
         $qb->addSelect('agencies');
         $qb->addSelect('city');
     }
     return $qb;
 }

 protected function join(QueryBuilder $qb, $add = false)
 {
     $qb->leftjoin('activity.account', 'account');
     $qb->leftjoin('activity.user', 'user');
     $qb->leftjoin('activity.replanned', 'replanned');
     $qb->leftjoin('activity.replannedBy', 'replannedBy');
     $qb->leftjoin('activity.type', 'type');
     if ($add) {
         $qb->addSelect('account');
         $qb->addSelect('user');
         $qb->addSelect('replanned');
         $qb->addSelect('replannedBy');
         $qb->addSelect('type');
     }
     return $qb;
 }

 /**
  * {@inheritdoc}
  */
 public function execute()
 {
     // emit listbuilder.create event
     $event = new ListBuilderCreateEvent($this);
     $this->eventDispatcher->dispatch(ListBuilderEvents::LISTBUILDER_CREATE, $event);
     $this->expressionFields = $this->getUniqueExpressionFieldDescriptors($this->expressions);
     // first create simplified id query
     // select ids with all necessary filter data
     $ids = $this->findIdsByGivenCriteria();
     // if no results are found - return
     if (count($ids) < 1) {
         return [];
     }
     // now select all data
     $this->queryBuilder = $this->em->createQueryBuilder()->from($this->entityName, $this->entityName);
     $this->assignJoins($this->queryBuilder);
     // Add all select fields
     foreach ($this->selectFields as $field) {
         $this->queryBuilder->addSelect($field->getSelect() . ' AS ' . $field->getName());
     }
     // group by
     $this->assignGroupBy($this->queryBuilder);
     // assign sort-fields
     $this->assignSortFields($this->queryBuilder);
     // use ids previously selected ids for query
     $this->queryBuilder->where($this->entityName . '.id IN (:ids)')->setParameter('ids', $ids);
     return $this->queryBuilder->getQuery()->getArrayResult();
 }

 /**
  * Build and returns Query for select rows
  * @param \Doctrine\ORM\QueryBuilder $queryBuilder
  * @param \EMC\TableBundle\Provider\QueryConfigInterface $queryConfig
  * @param array $mapping
  * @return \Doctrine\ORM\Query
  */
 private function getQueryRows(QueryBuilder $queryBuilder, QueryConfigInterface $queryConfig, array &$mapping)
 {
     $queryBuilder->resetDQLPart('select');
     $limit = $queryConfig->getLimit();
     $page = $queryConfig->getPage();
     $select = $queryConfig->getSelect();
     $orderBy = $queryConfig->getOrderBy();
     if ($limit > 0) {
         $queryBuilder->setMaxResults($limit)->setFirstResult(($page - 1) * $limit);
     }
     $mapping = array_map(function ($i) {
         return 'col' . $i;
     }, array_flip($select));
     foreach ($mapping as $column => $name) {
         $queryBuilder->addSelect($column . ' AS ' . $name);
     }
     if (count($orderBy) === 0) {
         $queryBuilder->orderBy($queryBuilder->getRootAlias() . '.id', 'ASC');
     } else {
         foreach ($orderBy as $column => $isAsc) {
             $queryBuilder->orderBy($column, $isAsc ? 'ASC' : 'DESC');
         }
     }
     return $queryBuilder->getQuery();
 }

 protected function join(QueryBuilder $qb, $add = false)
 {
     $qb->leftjoin('agency_account.turnovers', 'turnovers');
     if ($add) {
         $qb->addSelect('turnovers');
     }
     return $qb;
 }

 /**
  * @param QueryBuilder $qb                  Source query builder
  * @param string       $emailFromTableAlias EmailAddress table alias of joined Email#fromEmailAddress association
  */
 public function prepareQuery(QueryBuilder $qb, $emailFromTableAlias = 'a')
 {
     $qb->addSelect($this->getFromEmailExpression($emailFromTableAlias));
     foreach ($this->emailOwnerProviderStorage->getProviders() as $provider) {
         $fieldName = $this->emailOwnerProviderStorage->getEmailOwnerFieldName($provider);
         $qb->leftJoin(sprintf('%s.%s', $emailFromTableAlias, $fieldName), $fieldName);
     }
 }

 /**
  * Set select from.
  *
  * @return $this
  */
 private function setSelectFrom()
 {
     foreach ($this->selectColumns as $key => $value) {
         $this->qb->addSelect('partial ' . $key . '.{' . implode(',', $this->selectColumns[$key]) . '}');
     }
     $this->qb->from($this->entity, $this->tableName);
     return $this;
 }

 /**
  * {@inheritdoc}
  */
 protected function prepareQuery(QueryBuilder $query, $rootAlias, $alias, $itemsType)
 {
     $entityId = $this->requestParams->get(self::ENTITY_PARAM, 0);
     $query->where($rootAlias . '.mode <> :mode');
     $query->setParameter('mode', ConfigModelManager::MODE_HIDDEN);
     $query->innerJoin($rootAlias . '.entity', 'ce', 'WITH', 'ce.id=' . $entityId);
     $query->addSelect('ce.id as entity_id');
     return parent::prepareQuery($query, $rootAlias, $alias, $itemsType);
 }

 /**
  * @param $columns \Sorien\DataGridBundle\Grid\Column\Column[]
  * @param $page int Page Number
  * @param $limit int Rows Per Page
  * @return \Sorien\DataGridBundle\Grid\Rows
  */
 public function execute($columns, $page = 0, $limit = 0)
 {
     $this->query = $this->manager->createQueryBuilder($this->class);
     $this->query->from($this->class, self::TABLE_ALIAS);
     $where = $this->query->expr()->andx();
     $sorted = false;
     foreach ($columns as $column) {
         $this->query->addSelect($this->getFieldName($column));
         if ($column->isSorted() && !$column->isDefaultSort()) {
             $this->query->orderBy($this->getFieldName($column, false), $column->getOrder());
             $sorted = true;
         } elseif (!$sorted && $column->isSorted() && $column->isDefaultSort()) {
             $this->query->orderBy($this->getFieldName($column, false), $column->getOrder());
         }
         if ($column->isFiltered()) {
             if ($column->getFiltersConnection() == column::DATA_CONJUNCTION) {
                 foreach ($column->getFilters() as $filter) {
                     $operator = $this->normalizeOperator($filter->getOperator());
                     $where->add($this->query->expr()->{$operator}($this->getFieldName($column, false), $this->normalizeValue($filter->getOperator(), $filter->getValue())));
                 }
             } elseif ($column->getFiltersConnection() == column::DATA_DISJUNCTION) {
                 $sub = $this->query->expr()->orx();
                 foreach ($column->getFilters() as $filter) {
                     $operator = $this->normalizeOperator($filter->getOperator());
                     $sub->add($this->query->expr()->{$operator}($this->getFieldName($column, false), $this->normalizeValue($filter->getOperator(), $filter->getValue())));
                 }
                 $where->add($sub);
             }
             $this->query->where($where);
         }
     }
     foreach ($this->joins as $alias => $field) {
         $this->query->leftJoin($field, $alias);
     }
     if ($page > 0) {
         $this->query->setFirstResult($page * $limit);
     }
     if ($limit > 0) {
         $this->query->setMaxResults($limit);
     }
     //call overridden prepareQuery or associated closure
     $query = $this->prepareQuery(clone $this->query);
     $items = $query->getQuery()->getResult();
     // hydrate result
     $result = new Rows();
     foreach ($items as $item) {
         $row = new Row();
         foreach ($item as $key => $value) {
             $row->setField($key, $value);
         }
         //call overridden prepareRow or associated closure
         if (($modifiedRow = $this->prepareRow($row)) != null) {
             $result->addRow($modifiedRow);
         }
     }
     return $result;
 }

 /**
  * Left joins relations to eager load.
  *
  * @param ResourceInterface $resource
  * @param QueryBuilder      $queryBuilder
  */
 private function joinRelations(ResourceInterface $resource, QueryBuilder $queryBuilder)
 {
     $classMetaData = $queryBuilder->getEntityManager()->getClassMetadata($resource->getEntityClass());
     foreach ($classMetaData->getAssociationNames() as $i => $association) {
         $mapping = $classMetaData->associationMappings[$association];
         if (ClassMetadataInfo::FETCH_EAGER === $mapping['fetch']) {
             $queryBuilder->leftJoin('o.' . $association, 'a' . $i);
             $queryBuilder->addSelect('a' . $i);
         }
     }
 }