expr() public method

This producer method is intended for convenient inline usage. Example: $qb = $conn->createQueryBuilder() ->select('u') ->from('users', 'u') ->where($qb->expr()->eq('u.id', 1)); For more complex expression construction, consider storing the expression builder object in a local variable.
public expr ( ) : Doctrine\DBAL\Query\Expression\ExpressionBuilder
return Doctrine\DBAL\Query\Expression\ExpressionBuilder
Exemplo n.º 1
0
 /**
  * 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);
                 }
             }
         }
     }
 }
Exemplo n.º 2
0
 /**
  * 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;
 }
Exemplo n.º 3
0
 /**
  * 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));
 }
Exemplo n.º 4
0
 /**
  * @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;
 }
Exemplo n.º 5
0
 /**
  * @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();
 }
Exemplo n.º 6
0
 /**
  * {@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()]));
 }
Exemplo n.º 7
0
 /**
  * {@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));
 }
Exemplo n.º 8
0
 /**
  * {@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));
 }
Exemplo n.º 9
0
 /**
  * @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);
     }
 }
Exemplo n.º 10
0
 /**
  * {@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));
 }
Exemplo n.º 11
0
 /**
  * @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;
 }
Exemplo n.º 13
0
 /**
  * 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]);
 }
Exemplo n.º 14
0
 /**
  * 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;
 }
Exemplo n.º 15
0
 /**
  * @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();
                             $joinCondition = $this->buildTableColumnName($aliased, $intermediateFk) . '=' . $this->buildTableColumnName($aliased2, $reversePk);
                             $builder->innerJoin($aliased, $this->buildTableName($reverseTable), $aliased2, $joinCondition);
                             $filterColumn = $filterParam->getAttributeName();
                             $filterTable = $aliased2;
                         }
                         break;
                 }
             } else {
                 // param for attribute
                 $filterTable = $table;
                 $filterColumn = $filterParam->getAttributeName();
             }
             // here $filterTable and $filterColumn should always be not null (if not it's a bug in logic)
             $this->applyFilterToQuery($errors, $builder, $whereLink, $filterParam, $filterTable, $filterColumn, $lcOp, $params);
         }
     }
     $builder->andWhere($whereLink);
 }
Exemplo n.º 16
0
 public function applyWhereCriteria(QueryBuilder $qb, $field, $value)
 {
     $qb->andWhere($qb->expr()->isNotNull($field));
     $qb->andWhere($qb->expr()->neq($field, '""'));
 }
Exemplo n.º 17
0
 /**
  * @param array $request
  * @return array
  */
 public function make(array $request)
 {
     $output = ['data' => [], 'draw' => $request['draw'], 'recordsFiltered' => 0, 'recordsTotal' => 0];
     /**
      * Order By
      */
     if (isset($request['order'])) {
         for ($i = 0; $i < count($request['order']); ++$i) {
             $j = intval($request['order'][$i]['column']);
             if ($request['columns'][$j]['orderable'] != 'true') {
                 continue;
             }
             $column = $request['columns'][$j]['data'];
             $sort = $request['order'][$i]['dir'];
             $this->query->addOrderBy($column, $sort);
         }
     }
     /**
      * Count All
      */
     $temp = clone $this->query;
     $temp->resetQueryPart('select');
     $temp->resetQueryPart('orderBy');
     $temp->select("COUNT(*)");
     $output['recordsTotal'] = $temp->execute()->fetchColumn(0);
     /**
      * Filter
      */
     for ($i = 0; $i < count($request['columns']); ++$i) {
         if ($request['columns'][$i]['searchable'] != 'true') {
             continue;
         }
         $value = $request['columns'][$i]['search']['value'];
         if (strlen($value) > 0) {
             $column = $request['columns'][$i]['data'];
             $value = $this->query->getConnection()->quote("{$value}%");
             $this->query->andHaving($this->query->expr()->like($column, $value));
         }
     }
     /**
      * Search
      */
     if (isset($request['search'])) {
         $value = $request['search']['value'];
         if (strlen($value) > 0) {
             for ($i = 0; $i < count($request['columns']); ++$i) {
                 if ($request['columns'][$i]['searchable'] != 'true') {
                     continue;
                 }
                 $column = $request['columns'][$i]['data'];
                 $this->query->orHaving($this->query->expr()->like($column, ':search'));
             }
             $this->query->setParameter('search', "%{$value}%");
         }
     }
     /**
      * Count Filtered
      */
     $temp = clone $this->query;
     $temp->resetQueryPart('orderBy');
     $output['recordsFiltered'] = $temp->execute()->rowCount();
     /**
      * Limit
      */
     if (isset($request['start'])) {
         $this->query->setFirstResult($request['start']);
     }
     if (isset($request['length'])) {
         $this->query->setMaxResults($request['length']);
     }
     /**
      * Fetch Results
      */
     $output['data'] = $this->query->execute()->fetchAll(\PDO::FETCH_ASSOC);
     /**
      * Add Filter
      */
     return $output;
 }
 protected function prepareQuery(QueryBuilder $queryBuilder)
 {
     $expr = $queryBuilder->expr();
     if (count($this->filteredGroups)) {
         foreach ($this->filteredGroups as $index => $filteredGroup) {
             $condition = $filteredGroup['membersGroupFilter_condition'];
             $group = $filteredGroup['membersGroupFilter_group'];
             switch ($condition) {
                 case 'in':
                     $where = $expr->orX();
                     // HHFIX
                     // $where->add($expr->like('m.groups', ':groupPattern1_' . $index));
                     // HHENDFIX
                     $where->add($expr->like('m.groups', ':groupPattern2_' . $index));
                     break;
                 case 'not in':
                     $where = $expr->andX();
                     // HHFIX
                     // $where->add($expr->notLike('m.groups', ':groupPattern1_' . $index));
                     // ENDFIX
                     $where->add($expr->notLike('m.groups', ':groupPattern2_' . $index));
                     break;
                 default:
                     continue 2;
             }
             $queryBuilder->andWhere($where)->setParameter('groupPattern2_' . $index, sprintf('%%s:%d:"%d";%%', strlen($group), $group));
         }
     }
     if (count($this->filteredMailingLists)) {
         $queryBuilder->innerJoin('m', 'orm_avisota_subscription', 's', 's.recipientType = :recipientType AND s.recipientId = m.id')->setParameter('recipientType', 'member');
         $or = $expr->orX();
         foreach ($this->filteredMailingLists as $index => $mailingList) {
             $or->add($expr->eq('s.mailingList', ':mailingList' . $index));
             $queryBuilder->setParameter('mailingList' . $index, $mailingList->getId());
         }
         $queryBuilder->andWhere($or);
     }
     if (count($this->filteredProperties)) {
         foreach ($this->filteredProperties as $index => $filteredProperty) {
             $property = 'm.' . $filteredProperty['membersPropertyFilter_property'];
             $comparator = $filteredProperty['membersPropertyFilter_comparator'];
             $value = $filteredProperty['membersPropertyFilter_value'];
             switch ($comparator) {
                 case 'empty':
                     $queryBuilder->andWhere($expr->orX($expr->eq($property, ':property' . $index), $expr->isNull($property)));
                     $value = '';
                     break;
                 case 'not empty':
                     $queryBuilder->andWhere($expr->gt($property, ':property' . $index));
                     $value = '';
                     break;
                 case 'eq':
                     $queryBuilder->andWhere($expr->eq($property, ':property' . $index));
                     break;
                 case 'neq':
                     $queryBuilder->andWhere($expr->neq($property, ':property' . $index));
                     break;
                 case 'gt':
                     $queryBuilder->andWhere($expr->gt($property, ':property' . $index));
                     break;
                 case 'gte':
                     $queryBuilder->andWhere($expr->gte($property, ':property' . $index));
                     break;
                 case 'lt':
                     $queryBuilder->andWhere($expr->lt($property, ':property' . $index));
                     break;
                 case 'lte':
                     $queryBuilder->andWhere($expr->lte($property, ':property' . $index));
                     break;
             }
             $queryBuilder->setParameter(':property' . $index, $value);
         }
     }
 }
 private function addBetweenFilter(QueryBuilder $query)
 {
     $query->andWhere($query->expr()->gte('main.age', ':age_min'))->andWhere($query->expr()->lt('main.age', ':age_max'))->setParameter('age_min', 30)->setParameter('age_max', 50);
 }
Exemplo n.º 20
0
 /**
  * @param array        $filters
  * @param QueryBuilder $queryBuilder
  * @param array        $filterDefinitions
  *
  * @return bool
  */
 private function applyFilters(array $filters, QueryBuilder $queryBuilder, array $filterDefinitions)
 {
     $expr = $queryBuilder->expr();
     $filterExpr = $expr->andX();
     if (count($filters)) {
         foreach ($filters as $filter) {
             $exprFunction = isset($filter['expr']) ? $filter['expr'] : $filter['condition'];
             $paramName = InputHelper::alphanum($filter['column']);
             switch ($exprFunction) {
                 case 'notEmpty':
                     $filterExpr->add($expr->isNotNull($filter['column']));
                     $filterExpr->add($expr->neq($filter['column'], $expr->literal('')));
                     break;
                 case 'empty':
                     $filterExpr->add($expr->isNull($filter['column']));
                     $filterExpr->add($expr->eq($filter['column'], $expr->literal('')));
                     break;
                 default:
                     if (trim($filter['value']) == '') {
                         // Ignore empty
                         break;
                     }
                     $columnValue = ":{$paramName}";
                     switch ($filterDefinitions[$filter['column']]['type']) {
                         case 'bool':
                         case 'boolean':
                             if ((int) $filter['value'] > 1) {
                                 // Ignore the "reset" value of "2"
                                 break;
                             }
                             $queryBuilder->setParameter($paramName, $filter['value'], 'boolean');
                             break;
                         case 'float':
                             $columnValue = (double) $filter['value'];
                             break;
                         case 'int':
                         case 'integer':
                             $columnValue = (int) $filter['value'];
                             break;
                         default:
                             $queryBuilder->setParameter($paramName, $filter['value']);
                     }
                     $filterExpr->add($expr->{$exprFunction}($filter['column'], $columnValue));
             }
         }
     }
     if ($filterExpr->count()) {
         $queryBuilder->andWhere($filterExpr);
         return true;
     }
     return false;
 }
Exemplo n.º 21
0
 /**
  * {@inheritdoc}
  */
 public function notLike($field, $pattern)
 {
     return $this->queryBuilder->expr()->notLike($field, $this->queryBuilder->expr()->literal($pattern));
 }
Exemplo n.º 22
0
 /**
  * Set any required WHERE clause on a QueryBuilder.
  *
  * @param QueryBuilder $query
  * @param string       $contenttype
  * @param array        $options
  *
  * @return QueryBuilder
  */
 private 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'])) {
             $where->add($query->expr()->eq('log.id', ':logid'));
         }
     }
     $query->where($where)->setParameters([':contenttype' => $contenttype, ':contentid' => isset($options['contentid']) ? $options['contentid'] : null, ':logid' => isset($options['id']) ? $options['id'] : null]);
     return $query;
 }
Exemplo n.º 23
0
 /**
  * Creates a NOT IN expression for the given query builder.
  *
  * @param QueryBuilder $queryBuilder Query builder to be configured.
  * @param string       $property     Property name.
  * @param array        $value        Value for the right side of the operator.
  *
  * @return string
  */
 private function notIn(QueryBuilder $queryBuilder, $property, array $value)
 {
     // if empty NOT IN just return 1 (serving as TRUE) to always match
     if (empty($value)) {
         return 'TRUE';
     }
     return $queryBuilder->expr()->notIn($property, $this->createInValue($value));
 }
 /**
  * @param QueryBuilder $query
  * @param string $column
  * @param string $operand
  * @param mixed $value
  * @param int $filterCount
  */
 private function addFilter(QueryBuilder $query, $column, $operand, $value, $filterCount)
 {
     switch ($operand) {
         case self::OPERAND_EQ:
             $query->andWhere($query->expr()->eq($column, ':__filter' . $filterCount));
             break;
         case self::OPERAND_LT:
             $query->andWhere($query->expr()->lt($column, ':__filter' . $filterCount));
             break;
         case self::OPERAND_LTE:
             $query->andWhere($query->expr()->lte($column, ':__filter' . $filterCount));
             break;
         case self::OPERAND_GT:
             $query->andWhere($query->expr()->gt($column, ':__filter' . $filterCount));
             break;
         case self::OPERAND_GTE:
             $query->andWhere($query->expr()->gte($column, ':__filter' . $filterCount));
             break;
         case self::OPERAND_LIKE:
             $query->andWhere($query->expr()->like($column, ':__filter' . $filterCount));
             break;
         case self::OPERAND_LIKE_CI:
             $query->andWhere($query->expr()->like('LOWER(' . $column . ')', ':__filter' . $filterCount));
             $value = strtolower($value);
             break;
     }
     $query->setParameter('__filter' . $filterCount, $value);
 }
Exemplo n.º 25
0
 /**
  * @param QueryBuilder        $builder
  * @param CompositeExpression $link
  * @param ErrorCollection     $errors
  * @param string              $table
  * @param string              $column
  * @param string              $method
  * @param string|array        $params
  *
  * @return void
  *
  * @SuppressWarnings(PHPMD.ElseExpression)
  */
 protected function applyComparisonMethod(QueryBuilder $builder, CompositeExpression $link, ErrorCollection $errors, $table, $column, $method, $params)
 {
     // params could be in form of 1 value or array of values
     if (is_array($params) === true) {
         foreach ($params as $param) {
             if (is_scalar($param) === true) {
                 $param = (string) $builder->createNamedParameter($param);
                 $link->add($builder->expr()->{$method}($this->getTableColumn($table, $column), $param));
             } else {
                 $this->addInvalidQueryParameterError($errors, $column);
             }
         }
     } elseif (is_scalar($params) === true) {
         $param = $builder->createNamedParameter((string) $params);
         $link->add($builder->expr()->{$method}($this->getTableColumn($table, $column), $param));
     } else {
         // parameter is neither array nor string/scalar
         $this->addInvalidQueryParameterError($errors, $column);
     }
 }
Exemplo n.º 26
0
 /**
  * Get QueryBuilder expr.
  *
  * @return \Doctrine\DBAL\Query\Expression\ExpressionBuilder
  */
 public function getExpr()
 {
     return $this->queryBuilder->expr();
 }
Exemplo n.º 27
0
 /**
  * @param \Doctrine\DBAL\Query\QueryBuilder|\Doctrine\ORM\QueryBuilder $q
  * @param                                                              $filter
  *
  * @return array
  */
 protected function addSearchCommandWhereClause(&$q, $filter)
 {
     $command = $filter->command;
     $unique = $this->generateRandomParameterName();
     $returnParameter = true;
     //returning a parameter that is not used will lead to a Doctrine error
     $expr = false;
     switch ($command) {
         case $this->translator->trans('mautic.core.searchcommand.ismine'):
             $expr = $q->expr()->eq("l.createdBy", $this->currentUser->getId());
             $returnParameter = false;
             break;
         case $this->translator->trans('mautic.lead.list.searchcommand.isglobal'):
             $expr = $q->expr()->eq("l.isGlobal", ":{$unique}");
             $forceParameters = array($unique => true);
             break;
         case $this->translator->trans('mautic.core.searchcommand.ispublished'):
             $expr = $q->expr()->eq("l.isPublished", ":{$unique}");
             $forceParameters = array($unique => true);
             break;
         case $this->translator->trans('mautic.core.searchcommand.isunpublished'):
             $expr = $q->expr()->eq("l.isPublished", ":{$unique}");
             $forceParameters = array($unique => false);
             break;
         case $this->translator->trans('mautic.core.searchcommand.name'):
             $expr = $q->expr()->like('l.name', ':' . $unique);
             break;
     }
     $parameters = array();
     if (!empty($forceParameters)) {
         $parameters = $forceParameters;
     } elseif ($returnParameter) {
         $string = $filter->strict ? $filter->string : "%{$filter->string}%";
         $parameters = array("{$unique}" => $string);
     }
     return array($expr, $parameters);
 }
Exemplo n.º 28
0
 public function testCreatePositionalParameter()
 {
     $qb = new QueryBuilder($this->conn);
     $qb->select('u.*')->from('users', 'u')->where($qb->expr()->eq('u.name', $qb->createPositionalParameter(10, \PDO::PARAM_INT)));
     $this->assertEquals('SELECT u.* FROM users u WHERE u.name = ?', (string) $qb);
     $this->assertEquals(10, $qb->getParameter(1));
 }
Exemplo n.º 29
0
 /**
  * Build an OR group that is added to the AND.
  *
  * @param QueryBuilder $qb
  * @param string       $parentColumnName
  * @param array        $options
  *
  * @return CompositeExpression
  */
 protected function buildWhereOr(QueryBuilder $qb, $parentColumnName, array $options)
 {
     $orX = $qb->expr()->orX();
     foreach ($options as $columnName => $option) {
         if (empty($options[$columnName])) {
             continue;
         } elseif (Arr::isIndexedArray($options)) {
             $key = $parentColumnName . '_' . $columnName;
             $orX->add("{$parentColumnName} = :{$key}");
             $qb->setParameter($key, $option);
         } else {
             $orX->add("{$columnName} = :{$columnName}");
             $qb->setParameter($columnName, $option);
         }
     }
     return $orX;
 }
Exemplo n.º 30
0
 /**
  * Set any required WHERE clause on a QueryBuilder.
  *
  * @param QueryBuilder $query
  * @param integer      $level
  * @param string       $context
  *
  * @return QueryBuilder
  */
 private function setWhere(QueryBuilder $query, $level = null, $context = null)
 {
     if ($level || $context) {
         $where = $query->expr()->andX();
         if ($level) {
             $where->add($query->expr()->eq('level', ':level'));
         }
         if ($context) {
             $where->add($query->expr()->eq('context', ':context'));
         }
         $query->where($where)->setParameters(array(':level' => $level, ':context' => $context));
     }
     return $query;
 }