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