PHP QueryBuilder::expr方法代码示例

 /**
  * Apply where filters to the query
  *
  * @param  QueryBuilder $query
  * @param  array        $filters
  */
 public function applyFilters(&$query, $filters)
 {
     if ($filters && is_array($filters)) {
         foreach ($filters as $column => $value) {
             $valId = $column . '_val';
             // Special case: Lead list filter
             if ($column === 'leadlist_id') {
                 $query->join('t', MAUTIC_TABLE_PREFIX . 'lead_lists_leads', 'lll', 'lll.lead_id = ' . $value['list_column_name']);
                 $query->andWhere('lll.leadlist_id = :' . $valId);
                 $query->setParameter($valId, $value['value']);
             } elseif (isset($value['expression']) && method_exists($query->expr(), $value['expression'])) {
                 $query->andWhere($query->expr()->{$value['expression']}($column));
                 if (isset($value['value'])) {
                     $query->setParameter($valId, $value['value']);
                 }
             } else {
                 if (is_array($value)) {
                     $query->andWhere('t.' . $column . ' IN(:' . $valId . ')');
                     $query->setParameter($valId, implode(',', $value));
                 } else {
                     $query->andWhere('t.' . $column . ' = :' . $valId);
                     $query->setParameter($valId, $value);
                 }
             }
         }
     }
 }

 /**
  * Creates a composite expression that adds all the attached
  * filters individual expressions into a combined one.
  *
  * @return CompositeExpression
  */
 public function getWhereExpression()
 {
     if (!count($this->filters)) {
         return null;
     }
     $expr = $this->qb->expr()->andX();
     foreach ($this->filters as $filter) {
         $expr = $expr->add($filter->getExpression());
     }
     return $expr;
 }

 /**
  * Build the query with active filters
  *
  * @param QueryBuilder $queryBuilder
  * @param array        $values
  */
 public function buildQuery(QueryBuilder $queryBuilder, array $values = [])
 {
     if (!array_key_exists($this->getName(), $values) || 0 === count($values[$this->getName()])) {
         return;
     }
     $condition = array_map(function ($value) use($queryBuilder) {
         if ($value === '') {
             return $queryBuilder->expr()->isNull('response.gender');
         }
         return $queryBuilder->expr()->eq('response.gender', $value);
     }, $values[$this->getName()]);
     $queryBuilder->andWhere(new CompositeExpression(CompositeExpression::TYPE_OR, $condition));
 }

 /**
  * @param QueryBuilder $qb
  * @param $fields
  * @param Like $value
  *
  * @return null|array parameters to set to DBAL
  */
 public function applyWhereCriteria(QueryBuilder $qb, $fields, $value)
 {
     $likeExpressions = [];
     $parameters = [];
     $searchValue = $value->getValue();
     // for this criterion we handle multiple fields
     foreach (explode('|', $fields) as $field) {
         $likeExpressions[] = $qb->expr()->like($field, '?');
         $parameters[] = "%{$searchValue}%";
     }
     $whereExpr = call_user_func_array([$qb->expr(), 'orX'], $likeExpressions);
     $qb->andWhere($whereExpr);
     return $parameters;
 }

 /**
  * @param object $builder ticket query builder
  * @param array  $query   key value array of filters
  */
 public function __construct(QueryBuilder $builder, $query)
 {
     $this->builder = $builder;
     $this->expr = $builder->expr();
     $this->query = $query;
     $this->filter();
 }

 /**
  * {@inheritdoc}
  */
 public function buildQuery(QueryBuilder $queryBuilder, array $values = array())
 {
     if (!array_key_exists($this->getName(), $values) || 0 === count($values[$this->getName()])) {
         return;
     }
     $queryBuilder->andWhere($queryBuilder->expr()->in('response.companyType', $values[$this->getName()]));
 }

 /**
  * {@inheritdoc}
  */
 public function buildQuery(QueryBuilder $queryBuilder, array $values = [])
 {
     if (!array_key_exists($this->getName(), $values) || 0 === count($values[$this->getName()])) {
         return;
     }
     $campaigns = array_map(function (Campaign $item) {
         return $item->getId();
     }, $values[$this->getName()]->toArray());
     $queryBuilder->andWhere($queryBuilder->expr()->in('response.campaign_id', $campaigns));
 }

 /**
  * {@inheritdoc}
  */
 public function buildQuery(QueryBuilder $queryBuilder, array $values = [])
 {
     if (!array_key_exists($this->getName(), $values) || 0 === count($values[$this->getName()])) {
         return;
     }
     $certifications = $values[$this->getName()]->toArray();
     $certifications = array_map(function (Certification $item) {
         return $item->getId();
     }, $certifications);
     $queryBuilder->leftJoin('response', 'response_certification', 'response_certification', 'response.id = response_certification.response_id')->andWhere($queryBuilder->expr()->in('response_certification.certification_id', $certifications));
 }

 /**
  * @param QueryBuilder $queryBuilder
  * @param Request $request
  * @return QueryBuilder
  */
 public function beforeGetList(QueryBuilder $queryBuilder, Request $request)
 {
     $from = $request->query->get('from');
     $to = $request->query->get('to');
     $author = $request->query->get('author');
     if (!$from && !$to && !$author) {
         return $queryBuilder;
     } else {
         return $queryBuilder->where($queryBuilder->expr()->like('author', '?'))->andWhere('o.created BETWEEN ? AND ?')->setParameter(0, $author ? $author : self::DEFAULT_AUTHOR)->setParameter(1, $from ? $from : self::DEFAULT_FROM)->setParameter(2, $to ? $to : self::DEFAULT_TO);
     }
 }

 /**
  * {@inheritdoc}
  */
 public function buildQuery(QueryBuilder $queryBuilder, array $values = [])
 {
     if (!array_key_exists($this->getName(), $values) || 0 === count($values[$this->getName()])) {
         return;
     }
     $specialities = $values[$this->getName()]->toArray();
     $specialities = array_map(function (Speciality $item) {
         return $item->getId();
     }, $specialities);
     $queryBuilder->join('response', 'response_speciality', 'response_speciality', 'response.id = response_speciality.response_id')->join('response_speciality', 'speciality', 'speciality', 'response_speciality.speciality_id = speciality.id')->andWhere($queryBuilder->expr()->in('speciality.id', $specialities));
 }

 /**
  * @param QueryBuilder|\Doctrine\ORM\QueryBuilder $q
  * @param $column
  * @param $operator
  * @param $parameter
  * @param $includeIsNull    true/false or null to auto determine based on operator
  *
  * @return mixed
  */
 protected function generateFilterExpression($q, $column, $operator, $parameter, $includeIsNull, CompositeExpression $appendTo = null)
 {
     // in/notIn for dbal will use a raw array
     if (!is_array($parameter) && strpos($parameter, ':') !== 0) {
         $parameter = ":{$parameter}";
     }
     if (null === $includeIsNull) {
         // Auto determine based on negate operators
         $includeIsNull = in_array($operator, ['neq', 'notLike', 'notIn']);
     }
     if ($includeIsNull) {
         $expr = $q->expr()->orX($q->expr()->{$operator}($column, $parameter), $q->expr()->isNull($column));
     } else {
         $expr = $q->expr()->{$operator}($column, $parameter);
     }
     if ($appendTo) {
         $appendTo->add($expr);
         return $appendTo;
     }
     return $expr;
 }

 /**
  * @param QueryBuilder        $qb
  * @param string              $field
  * @param ArrayValuesContains $value
  *
  * @return array
  */
 public function applyWhereCriteria(QueryBuilder $qb, $field, $value)
 {
     $parametersToSet = [];
     // hack to reduce count of fetched records
     // TODO - hack for NotEmpty
     foreach ($value->getValues() as $criterionValue) {
         if (!is_string($criterionValue)) {
             continue;
         }
         $qb->andWhere($qb->expr()->like($field, '?'));
         $parametersToSet[] = '%' . $criterionValue . '%';
     }
     return $parametersToSet;
 }

 /**
  * Set any required WHERE clause on a QueryBuilder.
  *
  * @param QueryBuilder $query
  * @param string       $contenttype
  * @param array        $options
  */
 protected function setWhere(QueryBuilder $query, $contenttype, array $options)
 {
     $where = $query->expr()->andX()->add($query->expr()->eq('contenttype', ':contenttype'));
     // Set any required WHERE
     if (isset($options['contentid']) || isset($options['id'])) {
         if (isset($options['contentid'])) {
             $where->add($query->expr()->eq('contentid', ':contentid'));
         }
         if (isset($options['id'])) {
             $tableName = $this->getTableName();
             $where->add($query->expr()->eq("{$tableName}.id", ':logid'));
         }
     }
     $query->where($where)->setParameters([':contenttype' => $contenttype, ':contentid' => isset($options['contentid']) ? $options['contentid'] : null, ':logid' => isset($options['id']) ? $options['id'] : null]);
 }

 /**
  * Build an expression to be included in a Doctrine QueryBuilder instance.
  *
  * This method will build an expression, adding any parameters to the query,
  * but the caller is responsible for adding the expression to the query, for
  * example as where() parameter. This allows the caller to handle all context,
  * such as parenthetical groupings.
  *
  * @param QueryBuilder $qb query builder instance
  *
  * @return string expression
  */
 public function buildExpressionQb(QueryBuilder $qb)
 {
     $eb = $qb->expr();
     $column = (empty($this->prefix) ? "" : $this->prefix . '.') . $this->column;
     // this should be done using portability functions
     if (!empty($this->function)) {
         $column = sprintf($this->function, $column);
     }
     $value = trim($this->value);
     $operator = strtolower($this->operator);
     $expr = '';
     // handle special case of value
     if (in_array($operator, array('is null', 'is not null', 'in', 'not in'))) {
         switch ($operator) {
             case 'is null':
                 $expr = $eb->isNull($column);
                 break;
             case 'is not null':
                 $expr = $eb->isNotNull($column);
                 break;
             case 'in':
                 if (!empty($value) && $value !== '()') {
                     $expr = $column . ' IN ' . $value;
                 } else {
                     // odd case of a null set - this won't match anything
                     $expr = $eb->neq($column, $column);
                 }
                 break;
             case 'not in':
                 if (!empty($value) && $value !== '()') {
                     $expr = $column . ' NOT IN ' . $value;
                 }
                 break;
         }
     } elseif (!empty($column)) {
         // no value is a nop (bug: this should be a valid value)
         $columnValue = $qb->createNamedParameter($value);
         switch ($operator) {
             case '=':
             case 'eq':
                 $expr = $eb->eq($column, $columnValue);
                 break;
             case '!=':
             case '<>':
             case 'neq':
                 $expr = $eb->neq($column, $columnValue);
                 break;
             case '<':
             case 'lt':
                 $expr = $eb->lt($column, $columnValue);
                 break;
             case '<=':
             case 'lte':
                 $expr = $eb->lte($column, $columnValue);
                 break;
             case '>':
             case 'gt':
                 $expr = $eb->gt($column, $columnValue);
                 break;
             case '>=':
             case 'gte':
                 $expr = $eb->gte($column, $columnValue);
                 break;
             case 'like':
                 $expr = $eb->like($column, $columnValue);
                 break;
             case 'not like':
                 $expr = $eb->notLike($column, $columnValue);
                 break;
             default:
                 $expr = $eb->comparison($column, strtoupper($operator), $columnValue);
                 break;
         }
     } else {
         $expr = '(1)';
     }
     return $expr;
 }

 /**
  * @inheritdoc
  *
  * @SuppressWarnings(PHPMD.CyclomaticComplexity)
  * @SuppressWarnings(PHPMD.ElseExpression)
  * @SuppressWarnings(PHPMD.NPathComplexity)
  * @SuppressWarnings(PHPMD.ExcessiveMethodLength)
  */
 public function applyFilters(ErrorCollection $errors, QueryBuilder $builder, $modelClass, FilterParameterCollection $filterParams)
 {
     if ($filterParams->count() <= 0) {
         return;
     }
     $whereLink = $filterParams->isWithAnd() === true ? $builder->expr()->andX() : $builder->expr()->orX();
     // while joining tables we select distinct rows. this flag used to apply `distinct` no more than once.
     $hasAppliedDistinct = false;
     $table = $this->getTableName($modelClass);
     $quotedTable = $this->buildTableName($table);
     $modelSchemes = $this->getModelSchemes();
     foreach ($filterParams as $filterParam) {
         /** @var FilterParameterInterface $filterParam */
         $filterValue = $filterParam->getValue();
         // if filter value is not array of 'operation' => parameters (string/array) but
         // just parameters we will apply default operation
         // for example instead of `filter[id][in]=1,2,3,8,9,10` we got `filter[id]=1,2,3,8,9,10`
         if (is_array($filterValue) === false) {
             if (empty($filterValue) === true) {
                 $operation = static::DEFAULT_FILTER_OPERATION_EMPTY;
                 $filterIndexes = null;
             } else {
                 $filterIndexes = explode(',', $filterValue);
                 $numIndexes = count($filterIndexes);
                 $operation = $numIndexes === 1 ? static::DEFAULT_FILTER_OPERATION_SINGLE : static::DEFAULT_FILTER_OPERATION;
             }
             $filterValue = [$operation => $filterIndexes];
         }
         foreach ($filterValue as $operation => $params) {
             $filterTable = null;
             $filterColumn = null;
             $lcOp = strtolower((string) $operation);
             if ($filterParam->isForRelationship() === true) {
                 switch ($filterParam->getRelationshipType()) {
                     case RelationshipTypes::BELONGS_TO:
                         if ($filterParam->isForAttributeInRelationship() === true) {
                             $foreignKey = $modelSchemes->getForeignKey($modelClass, $filterParam->getRelationshipName());
                             list($reverseClass) = $modelSchemes->getReverseRelationship($modelClass, $filterParam->getRelationshipName());
                             $reversePk = $modelSchemes->getPrimaryKey($reverseClass);
                             $filterTable = $modelSchemes->getTable($reverseClass);
                             $filterColumn = $filterParam->getAttributeName();
                             $aliased = $filterTable . $this->getNewAliasId();
                             $joinCondition = $this->buildTableColumnName($table, $foreignKey) . '=' . $this->buildTableColumnName($aliased, $reversePk);
                             $builder->innerJoin($quotedTable, $this->buildTableName($filterTable), $aliased, $joinCondition);
                             if ($hasAppliedDistinct === false) {
                                 $this->distinct($builder, $modelClass);
                                 $hasAppliedDistinct = true;
                             }
                             $filterTable = $aliased;
                         } else {
                             $filterTable = $table;
                             $filterColumn = $modelSchemes->getForeignKey($modelClass, $filterParam->getRelationshipName());
                         }
                         break;
                     case RelationshipTypes::HAS_MANY:
                         // here we join hasMany table and apply filter on its primary key
                         $primaryKey = $modelSchemes->getPrimaryKey($modelClass);
                         list($reverseClass, $reverseName) = $modelSchemes->getReverseRelationship($modelClass, $filterParam->getRelationshipName());
                         $filterTable = $modelSchemes->getTable($reverseClass);
                         $reverseFk = $modelSchemes->getForeignKey($reverseClass, $reverseName);
                         $filterColumn = $filterParam->isForAttributeInRelationship() === true ? $filterParam->getAttributeName() : $modelSchemes->getPrimaryKey($reverseClass);
                         $aliased = $filterTable . $this->getNewAliasId();
                         $joinCondition = $this->buildTableColumnName($table, $primaryKey) . '=' . $this->buildTableColumnName($aliased, $reverseFk);
                         $builder->innerJoin($quotedTable, $this->buildTableName($filterTable), $aliased, $joinCondition);
                         if ($hasAppliedDistinct === false) {
                             $this->distinct($builder, $modelClass);
                             $hasAppliedDistinct = true;
                         }
                         $filterTable = $aliased;
                         break;
                     case RelationshipTypes::BELONGS_TO_MANY:
                         // here we join intermediate belongsToMany table and apply filter on its 2nd foreign key
                         list($intermediateTable, $intermediatePk, $intermediateFk) = $modelSchemes->getBelongsToManyRelationship($modelClass, $filterParam->getRelationshipName());
                         $primaryKey = $modelSchemes->getPrimaryKey($modelClass);
                         $aliased = $intermediateTable . $this->getNewAliasId();
                         $joinCondition = $this->buildTableColumnName($table, $primaryKey) . '=' . $this->buildTableColumnName($aliased, $intermediatePk);
                         $builder->innerJoin($quotedTable, $this->buildTableName($intermediateTable), $aliased, $joinCondition);
                         if ($hasAppliedDistinct === false) {
                             $this->distinct($builder, $modelClass);
                             $hasAppliedDistinct = true;
                         }
                         if ($filterParam->isForAttributeInRelationship() === false) {
                             $filterColumn = $intermediateFk;
                             $filterTable = $aliased;
                         } else {
                             // that's a condition on attribute of resources in relationship
                             // so we have to join that table
                             list($reverseClass) = $modelSchemes->getReverseRelationship($modelClass, $filterParam->getRelationshipName());
                             $reverseTable = $modelSchemes->getTable($reverseClass);
                             $reversePk = $modelSchemes->getPrimaryKey($reverseClass);
                             // now join the table with intermediate
                             $aliased2 = $reverseTable . $this->getNewAliasId();
//.........这里部分代码省略.........