PHP QueryBuilder::where方法代码示例

 /**
  * @param QueryBuilder $queryBuilder
  * @param \DateTime|null $date
  *
  * @return QueryBuilder
  */
 protected function filterByActive(QueryBuilder $queryBuilder, \DateTime $date = null)
 {
     if (null === $date) {
         $date = new \Datetime();
     }
     return $queryBuilder->where($queryBuilder->expr()->orX($queryBuilder->expr()->isNull($this->getPropertyName('startsAt')), $queryBuilder->expr()->lt($this->getPropertyName('startsAt'), ':date')))->andWhere($queryBuilder->expr()->orX($queryBuilder->expr()->isNull($this->getPropertyName('endsAt')), $queryBuilder->expr()->gt($this->getPropertyName('endsAt'), ':date')))->setParameter('date', $date);
 }

 /**
  * Extend a query with a where constraint on the id of an entity.
  *
  * @param QueryBuilder     $queryBuilder
  * @param mixed            $id
  * @param EntityInterface  $entity
  * @param bool             $standalone
  * @param \ReflectionClass $entityClass
  *
  * @throws \RuntimeException
  */
 public static function extendQueryWhereId(QueryBuilder $queryBuilder, EntityInterface $entity, $standalone = false, \ReflectionClass $entityClass = null)
 {
     /** @var EntityAccessor $entityAccessor */
     $entityAccessor = $GLOBALS['container']['doctrine.orm.entityAccessor'];
     if (!$entityClass) {
         $entityClass = new \ReflectionClass($entity);
     }
     if ($entityClass->isSubclassOf('Contao\\Doctrine\\ORM\\EntityInterface')) {
         $keys = $entityClass->getMethod('entityPrimaryKeyNames')->invoke(null);
     } else {
         $keys = array('id');
     }
     foreach ($keys as $index => $key) {
         $value = $entityAccessor->getProperty($entity, $key);
         if ($value !== null) {
             $where = $queryBuilder->expr()->neq('e.' . $key, ':key' . $index);
             $queryBuilder->setParameter(':key' . $index, $value);
         } else {
             $where = $queryBuilder->expr()->isNotNull('e.' . $key);
         }
         if ($index > 0 || !$standalone) {
             $queryBuilder->andWhere($where);
         } else {
             $queryBuilder->where($where);
         }
     }
 }

 /**
  * Apply the ACL constraints to the specified query builder, using the permission definition
  *
  * @param QueryBuilder         $queryBuilder  The query builder
  * @param PermissionDefinition $permissionDef The permission definition
  *
  * @return Query
  */
 public function apply(QueryBuilder $queryBuilder, PermissionDefinition $permissionDef)
 {
     $whereQueryParts = $queryBuilder->getDQLPart('where');
     if (empty($whereQueryParts)) {
         $queryBuilder->where('1 = 1');
         // this will help in cases where no where query is specified
     }
     $query = $this->cloneQuery($queryBuilder->getQuery());
     $builder = new MaskBuilder();
     foreach ($permissionDef->getPermissions() as $permission) {
         $mask = constant(get_class($builder) . '::MASK_' . strtoupper($permission));
         $builder->add($mask);
     }
     $query->setHint('acl.mask', $builder->get());
     $query->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, 'Kunstmaan\\AdminBundle\\Helper\\Security\\Acl\\AclWalker');
     $rootEntity = $permissionDef->getEntity();
     $rootAlias = $permissionDef->getAlias();
     // If either alias or entity was not specified - use default from QueryBuilder
     if (empty($rootEntity) || empty($rootAlias)) {
         $rootEntities = $queryBuilder->getRootEntities();
         $rootAliases = $queryBuilder->getRootAliases();
         $rootEntity = $rootEntities[0];
         $rootAlias = $rootAliases[0];
     }
     $query->setHint('acl.root.entity', $rootEntity);
     $query->setHint('acl.extra.query', $this->getPermittedAclIdsSQLForUser($query));
     $classMeta = $this->em->getClassMetadata($rootEntity);
     $entityRootTableName = $this->quoteStrategy->getTableName($classMeta, $this->em->getConnection()->getDatabasePlatform());
     $query->setHint('acl.entityRootTableName', $entityRootTableName);
     $query->setHint('acl.entityRootTableDqlAlias', $rootAlias);
     return $query;
 }

 protected function filterByActive(QueryBuilder $qb, \Datetime $date = null)
 {
     if (null === $date) {
         $date = new \Datetime();
     }
     return $qb->where($qb->expr()->orX($qb->expr()->isNull($this->getAlias() . '.startsAt'), $qb->expr()->lt($this->getAlias() . '.startsAt', ':date')))->andWhere($qb->expr()->orX($qb->expr()->isNull($this->getAlias() . '.endsAt'), $qb->expr()->gt($this->getAlias() . '.endsAt', ':date')))->setParameter('date', $date);
 }

 /**
  * @param PersonCriteria $criteria
  */
 private function checkLastname(PersonCriteria $criteria)
 {
     if ($criteria->hasLastname()) {
         $this->qb->where('p.lastname = :lastname');
         $this->qb->setParameter('lastname', $criteria->getLastname());
     }
 }

 /**
  * Builds the query for the given data request object
  * 
  * @access public
  * @param \Zepi\DataSource\Core\Entity\DataRequest $dataRequest
  * @param \Doctrine\ORM\QueryBuilder $queryBuilder
  * @param string $entity
  * @param string $tableCode
  */
 public function buildDataRequestQuery(DataRequest $dataRequest, QueryBuilder $queryBuilder, $entity, $tableCode)
 {
     $queryBuilder->select($tableCode)->from($entity, $tableCode);
     $hasWhere = false;
     $i = 1;
     foreach ($dataRequest->getFilters() as $filter) {
         $whereQuery = $tableCode . '.' . $filter->getFieldName() . ' ' . $filter->getMode() . ' :' . $i;
         if ($hasWhere) {
             $queryBuilder->andWhere($whereQuery);
         } else {
             $queryBuilder->where($whereQuery);
             $hasWhere = true;
         }
         $queryBuilder->setParameter($i, $filter->getNeededValue());
         $i++;
     }
     // Sorting
     if ($dataRequest->hasSorting()) {
         $mode = 'ASC';
         if (in_array($dataRequest->getSortByDirection(), array('ASC', 'DESC'))) {
             $mode = $dataRequest->getSortByDirection();
         }
         $queryBuilder->orderBy($tableCode . '.' . $dataRequest->getSortBy(), $mode);
     }
     // Offset
     if ($dataRequest->hasRange()) {
         $queryBuilder->setFirstResult($dataRequest->getOffset());
         $queryBuilder->setMaxResults($dataRequest->getNumberOfEntries());
     }
 }

 public function buildCount(QueryBuilder $queryBuilder) : Query
 {
     if (!empty($this->name)) {
         $queryBuilder->where($queryBuilder->expr()->like('o.name.name', ':name'))->setParameter('name', '%' . $this->name . '%');
     }
     return $queryBuilder->select($queryBuilder->expr()->count('o.id'))->from(Organization::class, 'o')->leftJoin('o.organizationMembers', 'om')->leftJoin('o.owners', 'ow')->andWhere($queryBuilder->expr()->orX($queryBuilder->expr()->eq('om.userId', ':userId'), $queryBuilder->expr()->eq('ow.userId', ':userId')))->setParameter('userId', $this->userId->id())->getQuery();
 }

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

 public function manipulateQuery(QueryBuilder $query, $prefix = 'hv')
 {
     $query->where("{$prefix}.createdAt >= :startDate");
     $query->andWhere("{$prefix}.createdAt <= :endDate");
     $query->setParameters(['startDate' => $this->period->getStartDate(), 'endDate' => $this->period->getEndDate()]);
     return $query;
 }

 private function whereToPublish(QueryBuilder $builder)
 {
     $builder->where('r.published = :published');
     $builder->setParameter('published', false);
     $builder->orWhere('p.modified = :modified');
     $builder->setParameter('modified', true);
     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;
 }

 protected function addNameConstraint(QueryBuilder $query, $names)
 {
     if ($names) {
         if (is_array($names)) {
             $names = $this->slugify($names);
             $query->where($query->expr()->in('t.slug', $names));
         } else {
             $query->andWhere('t.slug = :slug')->setParameter('slug', Urlizer::urlize($names));
         }
     }
     return $query;
 }

 /**
  * @param QueryBuilder $queryBuilder
  * @param array $criteria
  * @return $this
  */
 public function applyCriteria($queryBuilder, array $criteria = array())
 {
     if (!count($criteria)) {
         return $this;
     }
     $criteria = $this->resolver->resolveCriteria($criteria);
     foreach ($criteria['joins'] as $join => $alias) {
         $queryBuilder->leftJoin($join, $alias);
     }
     $queryBuilder->where($this->createQueryExpression($queryBuilder, $criteria['expressions']));
     return $this;
 }

 /**
  * @param QueryBuilder $qb
  * @return QueryBuilder
  */
 protected function filter(QueryBuilder &$qb)
 {
     if (isset($this->conditions['timeToExpire'])) {
         $currentTimeStamp = time();
         $timeToExpire = $this->conditions['timeToExpire'];
         $qb->where("{$currentTimeStamp} - p.created_date <= {$timeToExpire}");
         unset($this->conditions['timeToExpire']);
     }
     //apply other filter (where field = value)
     if (is_array($this->conditions) && count($this->conditions) > 0) {
         foreach ($this->conditions as $key => $value) {
             if ($value) {
                 $v = is_int($value) ? (int) $value : "'{$value}'";
                 $qb->andWhere("p.{$key} = {$v}");
             }
         }
     }
     return $qb;
 }