PHP QueryBuilder::expr方法代码示例

 /**
  * @param array        $ids
  * @param QueryBuilder $qbr
  * @return Query\Expr\Comparison|Query\Expr\Orx
  */
 private function buildIdsCondition(array $ids, QueryBuilder $qbr)
 {
     $cond = $qbr->expr()->eq(1, 0);
     foreach ($ids as $row) {
         $cond = $qbr->expr()->orX($cond, sprintf('%s.id=\'%s\'', static::TABLE_ALIAS, (string) $row));
     }
     return $cond;
 }

 /**
  * {@inheritdoc}
  */
 public function expr()
 {
     if ($this->expressionBuilder === null) {
         $this->expressionBuilder = new OrmExpressionBuilder($this->qb->expr());
     }
     return $this->expressionBuilder;
 }

 /**
  * @param QueryBuilder $qb
  * @param string $field
  * @param string $operator
  * @param string $value
  * @param int $i
  *
  * @throws WebServerLogException
  *
  * @return \Doctrine\ORM\Query\Expr\Andx|\Doctrine\ORM\Query\Expr\Comparison|\Doctrine\ORM\Query\Expr\Func
  */
 private function matchOperator(QueryBuilder $qb, $field, $operator, $value, $i = 0)
 {
     // TODO expose operators logic into methods, or use appropriate design pattern
     switch ($operator) {
         case 'eq':
         case 'gt':
         case 'lt':
             $placeholder = ':' . $this->fieldName . '_' . $i;
             $qb->setParameter($placeholder, $value);
             return $qb->expr()->{$operator}($field, $placeholder);
         case 'regex':
             $placeholder = ':regexp_' . $this->fieldName . '_' . $i;
             $qb->setParameter($placeholder, $value);
             return $qb->expr()->andX("REGEXP({$field}, {$placeholder}) = true");
         case 'like':
             $placeholder = ':like_' . $this->fieldName . '_' . $i;
             $qb->setParameter($placeholder, '%' . $value . '%');
             return $qb->expr()->like($field, $placeholder);
         case 'between':
             list($from, $to) = explode(',', $value);
             $qb->setParameter('from_' . $i, $from);
             $qb->setParameter('to_' . $i, $to);
             return $qb->expr()->between($field, ':from_' . $i, ':to_' . $i);
         default:
             throw WebServerLogException::unknownFilterOperator($operator);
     }
 }

 public function filters(QueryBuilder $qb, $key, $val)
 {
     switch ($key) {
         case 'history':
             if ($val) {
                 $orx = $qb->expr()->orX();
                 $orx->add('s.fk = :fk');
                 $orx->add('t.fk = :fk');
                 $qb->andWhere($orx);
                 $qb->setParameter('fk', intval($val));
             }
             break;
         case 'class':
             $orx = $qb->expr()->orX();
             $orx->add('s.class = :class');
             $orx->add('t.class = :class');
             $qb->andWhere($orx);
             $qb->setParameter('class', $val);
             break;
         case 'blamed':
             if ($val === 'null') {
                 $qb->andWhere($qb->expr()->isNull('a.blame'));
             } else {
                 // this allows us to safely ignore empty values
                 // otherwise if $qb is not changed, it would add where the string is empty statement.
                 $qb->andWhere($qb->expr()->eq('b.fk', ':blame'));
                 $qb->setParameter('blame', $val);
             }
             break;
         default:
             // if user attemps to filter by other fields, we restrict it
             throw new \Exception("filter not allowed");
     }
 }

 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();
 }

 /**
  * @param QueryBuilder $qb
  * @param array $criteria
  * @param array $orderBy
  * @param int $limit
  * @param int $offset
  *
  * @return QueryBuilder
  */
 protected function attachCriteriaToQueryBuilder(QueryBuilder $qb, $criteria, $orderBy, $limit, $offset)
 {
     if (array_key_exists('alerts', $criteria)) {
         $ids = is_array($criteria['alerts']) ? $criteria['alerts'] : [$criteria['alerts']];
         $qb->join('x.alerts', 'al');
         $qb->andWhere($qb->expr()->in('al.id', ':alerts'));
         $qb->setParameter(':alerts', $ids);
     }
     //cleanup all the possible relationship filters
     unset($criteria['alerts']);
     if (count($criteria)) {
         foreach ($criteria as $key => $value) {
             $values = is_array($value) ? $value : [$value];
             $qb->andWhere($qb->expr()->in("x.{$key}", ":{$key}"));
             $qb->setParameter(":{$key}", $values);
         }
     }
     if (empty($orderBy)) {
         $orderBy = ['id' => 'ASC'];
     }
     if (is_array($orderBy)) {
         foreach ($orderBy as $sort => $order) {
             $qb->addOrderBy('x.' . $sort, $order);
         }
     }
     if ($offset) {
         $qb->setFirstResult($offset);
     }
     if ($limit) {
         $qb->setMaxResults($limit);
     }
     return $qb;
 }

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

 /**
  * @param string $column
  * @param mixed  $value
  * @return void
  */
 public function whereRootFieldEq($column, $value)
 {
     $qualified = $this->aliases->get($this->getRootId()) . '.' . $column;
     $param = ':' . $this->aliases->get($this->getRootId()) . $column;
     $this->builder->andWhere($this->builder->expr()->eq($qualified, $param));
     $this->builder->setParameter($param, $value);
 }

 /**
  * @param QueryBuilder $qb
  * @param array $criteria
  * @param array $orderBy
  * @param int $limit
  * @param int $offset
  *
  * @return QueryBuilder
  */
 protected function attachCriteriaToQueryBuilder(QueryBuilder $qb, $criteria, $orderBy, $limit, $offset)
 {
     if (array_key_exists('courses', $criteria)) {
         $ids = is_array($criteria['courses']) ? $criteria['courses'] : [$criteria['courses']];
         $qb->join('p.programYears', 'c_programYear');
         $qb->join('c_programYear.cohort', 'c_cohort');
         $qb->join('c_cohort.courses', 'c_course');
         $qb->andWhere($qb->expr()->in('c_course.id', ':courses'));
         $qb->setParameter(':courses', $ids);
     }
     if (array_key_exists('sessions', $criteria)) {
         $ids = is_array($criteria['sessions']) ? $criteria['sessions'] : [$criteria['sessions']];
         $qb->join('p.programYears', 'se_programYear');
         $qb->join('se_programYear.cohort', 'se_cohort');
         $qb->join('se_cohort.courses', 'se_course');
         $qb->join('se_course.sessions', 'se_session');
         $qb->andWhere($qb->expr()->in('se_session.id', ':sessions'));
         $qb->setParameter(':sessions', $ids);
     }
     if (array_key_exists('terms', $criteria)) {
         $ids = is_array($criteria['terms']) ? $criteria['terms'] : [$criteria['terms']];
         $qb->join('p.programYears', 't_programYear');
         $qb->join('t_programYear.terms', 't_term');
         $qb->andWhere($qb->expr()->in('t_term.id', ':terms'));
         $qb->setParameter(':terms', $ids);
     }
     if (array_key_exists('schools', $criteria)) {
         $ids = is_array($criteria['schools']) ? $criteria['schools'] : [$criteria['schools']];
         $qb->join('p.school', 'sc_school');
         $qb->andWhere($qb->expr()->in('sc_school.id', ':schools'));
         $qb->setParameter(':schools', $ids);
     }
     unset($criteria['schools']);
     unset($criteria['courses']);
     unset($criteria['sessions']);
     unset($criteria['terms']);
     if (count($criteria)) {
         foreach ($criteria as $key => $value) {
             $values = is_array($value) ? $value : [$value];
             $qb->andWhere($qb->expr()->in("p.{$key}", ":{$key}"));
             $qb->setParameter(":{$key}", $values);
         }
     }
     if (empty($orderBy)) {
         $orderBy = ['id' => 'ASC'];
     }
     if (is_array($orderBy)) {
         foreach ($orderBy as $sort => $order) {
             $qb->addOrderBy('p.' . $sort, $order);
         }
     }
     if ($offset) {
         $qb->setFirstResult($offset);
     }
     if ($limit) {
         $qb->setMaxResults($limit);
     }
     return $qb;
 }

 /**
  * Convert an array of ID's to Doctrine entities / arrays
  *
  * @param  array $data
  * @return array
  */
 public function convert($data)
 {
     if (empty($data)) {
         return array();
     }
     $this->qb->andWhere($this->qb->expr()->in($this->searchfield, $data));
     return $this->qb->getQuery()->getResult();
 }

 protected function applyCriteria(QueryBuilder $queryBuilder, array $criteria = null)
 {
     if (isset($criteria['groups'])) {
         $queryBuilder->innerJoin($this->getAlias() . '.machine', 'm', 'WITH', $this->getAlias() . '.machine = m.id')->innerJoin('m.groups', 'g', 'WITH', $queryBuilder->expr()->andX($queryBuilder->expr()->in('g.id', $criteria['groups'])));
         unset($criteria['groups']);
     }
     parent::applyCriteria($queryBuilder, $criteria);
 }

 /**
  * @param QueryBuilder $query
  * @param $source
  * @param $locale
  * @param bool $resetJoin
  */
 public function addQueryTranslationSupport(QueryBuilder $query, $source, $locale, $resetJoin = true)
 {
     if ($resetJoin) {
         $query->resetDQLPart('join');
     }
     $query->leftJoin($source->getTableAlias() . '.translations', '_translations', 'WITH', $query->expr()->orX($query->expr()->eq('_translations.locale', ':locale'), $query->expr()->isNull('_translations.id')));
     $query->setParameter('locale', $locale);
 }

 private function addQueryFilter(QueryBuilder $query, $filter)
 {
     $expr = $query->expr();
     /*$query->orWhere($expr->eq('entity.id', ':filter_int'));*/
     $query->andWhere($query->expr()->orX($expr->like('entity.code', ':filter_str'), $expr->like('entity.manufacturer', ':filter_str'), $expr->like('entity.model', ':filter_str')));
     /*$query->setParameter('filter_int', $filter);*/
     $query->setParameter('filter_str', '%' . $filter . '%');
     return $query;
 }

 public function setUp()
 {
     $entityManager = $this->getMockForAbstractClass(EntityManagerInterface::class);
     $expressionBuilder = new Expr();
     $entityManager->expects($this->any())->method('getExpressionBuilder')->willReturn($expressionBuilder);
     $this->queryBuilder = new QueryBuilder($entityManager);
     /** \Doctrine\ORM\Query\Expr */
     $expression = $this->queryBuilder->expr();
     $this->userSpecificationFactory = new UserSpecificationFactory($expression);
 }

 protected function prepareEmailCondition(QueryBuilder $qb, PropertyEmailInterface $condition, $alias)
 {
     if ($condition->getEmail()) {
         $qb->andWhere($qb->expr()->eq($alias . '.email', ':email'))->setParameter('email', $condition->getEmail());
     } else {
         if ($condition->getEmailNotSpecified()) {
             $qb->andWhere($qb->expr()->isNull($alias . '.email'));
         }
     }
 }