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 | ||
Résultat | Doctrine\DBAL\Query\Expression\ExpressionBuilder |
/** * 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(); $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); }
public function applyWhereCriteria(QueryBuilder $qb, $field, $value) { $qb->andWhere($qb->expr()->isNotNull($field)); $qb->andWhere($qb->expr()->neq($field, '""')); }
/** * @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); }
/** * @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; }
/** * {@inheritdoc} */ public function notLike($field, $pattern) { return $this->queryBuilder->expr()->notLike($field, $this->queryBuilder->expr()->literal($pattern)); }
/** * 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; }
/** * 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); }
/** * @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); } }
/** * Get QueryBuilder expr. * * @return \Doctrine\DBAL\Query\Expression\ExpressionBuilder */ public function getExpr() { return $this->queryBuilder->expr(); }
/** * @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); }
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)); }
/** * 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; }
/** * 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; }