/** * {@inheritdoc} */ public function between($value, $x, $y) { return $this->expr->between($value, $x, $y); }
public function testBetweenExpr() { $this->assertEquals('u.id BETWEEN 3 AND 6', (string) $this->_expr->between('u.id', 3, 6)); }
/** * Build where statement and add to the query builder. * * @param \Doctrine\Orm\QueryBuilder $qb * @param mixed $where * @return $this */ protected function addWhere($qb, $where) { // process the $where if (is_string($where)) { // straight DQL string $qb->andWhere($where); } elseif (is_array($where) && count($where)) { // create where expression $whereExp = $qb->expr()->andx(); $params = array(); // index for the parameters $i = 0; // loop through all the clauses supplied foreach ($where as $col => $val) { if (is_array($val) && (!isset($val['value']) || is_string($val['value']) && strlen($val['value']) == 0) || is_string($val) && (!$val || strlen($val) == 0)) { // skip if invalid value; continue; } // check if we've been provided with an operator as well as a value if (!is_array($val)) { $operator = Expr\Comparison::EQ; $val = $val; } elseif (count($val) == 1) { $operator = Expr\Comparison::EQ; $val = end($val); } else { $operator = isset($val['operator']) ? $val['operator'] : Expr\Comparison::EQ; $val = array_key_exists('value', $val) ? $val['value'] : array(); } // set the alias to the default $alias = $this->alias; // if col relates to a relation i.e. Role.id // then perform a join and set up the alias and column names if (strpos($col, '.') !== false) { $parts = explode('.', $col); $col = array_pop($parts); $par = $this->alias; foreach ($parts as $rel) { $alias = strtolower($rel); $jt = new Expr\Join(Expr\Join::LEFT_JOIN, $par . '.' . $rel, $alias); if (!strpos($qb->getDql(), $jt->__toString()) !== false) { $qb->leftJoin($par . '.' . $rel, $alias); } $par = $alias; } } // process sets a little differently if (!is_array($val)) { $val = array($val); } if ($operator == 'regexp') { $whereExp->add("REGEXP(" . $alias . '.' . $col . ",'" . $val[0] . "') = 1"); } else { if ($operator == 'between') { if (count($val) == 2) { // $value should now be an array with 2 values $expr = new Expr(); $from = is_int($val[0]) ? $val[0] : "'" . $val[0] . "'"; $to = is_int($val[1]) ? $val[1] : "'" . $val[1] . "'"; $stmt = $expr->between($alias . '.' . $col, $from, $to); $whereExp->add($stmt); } } else { if ($operator == 'is') { $expr = new Expr(); $method = 'is' . ucfirst($val[0]); if (method_exists($expr, $method)) { $stmt = $expr->{$method}($alias . '.' . $col); $whereExp->add($stmt); } } else { // this holds the subquery for this field, each component being an OR $subWhereExp = $qb->expr()->orX(); foreach ($val as $value) { if ($value == null) { $cmpValue = 'NULL'; } else { $cmpValue = '?' . $i; // wrap LIKE values if ($operator == 'like') { $value = '%' . trim($value, '%') . '%'; } // add the parameter value into the parameters stack $params[$i] = $value; $i++; } $comparison = new Expr\Comparison($alias . '.' . $col, $operator, $cmpValue); $subWhereExp->add($comparison); } // add in the subquery as an AND $whereExp->add($subWhereExp); } } } } // only add where expression if actually has parts if (count($whereExp->getParts())) { $qb->where($whereExp); } // set the params from the where clause above $qb->setParameters($params); } return $this; }
protected function getParts($qb, $where, &$params = array()) { if (array_key_exists('mode', $where) && $where['mode'] == 'or') { $whereExp = $qb->expr()->orX(); } else { $whereExp = $qb->expr()->andX(); } unset($where['mode']); // unset to allow flat iteration for simpler structures // index for the parameters $i = count($params); if (array_key_exists('expressions', $where)) { foreach ($where['expressions'] as $expression) { $part = $this->getParts($qb, $expression, $params); $whereExp->add($part); } unset($where['expressions']); // unset to allow flat iteration for simpler structures } // set fields to iterate over if no fields property assume using remaining elements in $where $fields = array_key_exists('fields', $where) ? $where['fields'] : $where; // loop through all the clauses supplied foreach ($fields as $col => $val) { if (is_array($val) && (!isset($val['value']) || is_string($val['value']) && strlen($val['value']) == 0) || is_string($val) && (!$val || strlen($val) == 0)) { // skip if invalid value; continue; } // check if we've been provided with an operator as well as a value if (!is_array($val)) { $operator = Expr\Comparison::EQ; $val = $val; } elseif (count($val) == 1) { $operator = Expr\Comparison::EQ; $val = end($val); } else { $operator = isset($val['operator']) ? $val['operator'] : Expr\Comparison::EQ; $val = array_key_exists('value', $val) ? $val['value'] : array(); } // set the alias to the default $alias = $this->alias; // if col relates to a relation i.e. Role.id // then perform a join and set up the alias and column names if (strpos($col, '.') !== false) { $parts = explode('.', $col); $col = array_pop($parts); $par = $this->alias; foreach ($parts as $rel) { $alias = strtolower($rel); $jt = new Expr\Join(Expr\Join::LEFT_JOIN, $par . '.' . $rel, $alias); if (!strpos($qb->getDql(), $jt->__toString()) !== false) { $qb->leftJoin($par . '.' . $rel, $alias); } $par = $alias; } } if ($val instanceof Expr\Base) { $whereExp->add($val); } else { // process sets a little differently if (!is_array($val)) { $val = array($val); } switch (strtolower($operator)) { case 'regexp': $whereExp->add("REGEXP(" . $alias . '.' . $col . ",'" . $val[0] . "') = 1"); break; case 'between': if (count($val) == 2) { // $value should now be an array with 2 values $expr = new Expr(); $from = is_int($val[0]) ? $val[0] : "'" . $val[0] . "'"; $to = is_int($val[1]) ? $val[1] : "'" . $val[1] . "'"; $stmt = $expr->between($alias . '.' . $col, $from, $to); $whereExp->add($stmt); } break; case 'is': $expr = new Expr(); $method = 'is' . ucfirst($val[0]); if (method_exists($expr, $method)) { $stmt = $expr->{$method}($alias . '.' . $col); $whereExp->add($stmt); } break; default: // this holds the subquery for this field, each component being an OR $subWhereExp = $qb->expr()->orX(); foreach ($val as $value) { if ($value == null) { $cmpValue = 'NULL'; } else { $cmpValue = '?' . $i; // wrap IN/NOT IN values with parenthesis if ($operator == 'in' || $operator == 'not in') { $cmpValue = '(' . trim($cmpValue, ')') . ')'; } // wrap LIKE values if ($operator == 'like') { $value = '%' . trim($value, '%') . '%'; } // add the parameter value into the parameters stack $params[$i] = $value; $i++; } $comparison = new Expr\Comparison($alias . '.' . $col, $operator, $cmpValue); $subWhereExp->add($comparison); } // add in the subquery as an AND $whereExp->add($subWhereExp); break; } } } return $whereExp; }
/** * @param DatagridFilter $filter * @throws \InvalidArgumentException */ public function applyFilter(DatagridFilter $filter) { $qb = $this->getQueryBuilder(); $expr = new Expr(); $column = $filter->getColumn(); $colString = $column->getSelectPart1(); if ($column->getSelectPart2() != '') { $colString .= '.' . $column->getSelectPart2(); } if ($column instanceof Column\Select && $column->hasFilterSelectExpression()) { $colString = sprintf($column->getFilterSelectExpression(), $colString); } $values = $filter->getValues(); $wheres = []; foreach ($values as $key => $value) { $valueParameterName = ':' . str_replace('.', '', $column->getUniqueId() . $key); switch ($filter->getOperator()) { case DatagridFilter::LIKE: $wheres[] = $expr->like($colString, $valueParameterName); $qb->setParameter($valueParameterName, '%' . $value . '%'); break; case DatagridFilter::LIKE_LEFT: $wheres[] = $expr->like($colString, $valueParameterName); $qb->setParameter($valueParameterName, '%' . $value); break; case DatagridFilter::LIKE_RIGHT: $wheres[] = $expr->like($colString, $valueParameterName); $qb->setParameter($valueParameterName, $value . '%'); break; case DatagridFilter::NOT_LIKE: $wheres[] = $expr->notLike($colString, $valueParameterName); $qb->setParameter($valueParameterName, '%' . $value . '%'); break; case DatagridFilter::NOT_LIKE_LEFT: $wheres[] = $expr->notLike($colString, $valueParameterName); $qb->setParameter($valueParameterName, '%' . $value); break; case DatagridFilter::NOT_LIKE_RIGHT: $wheres[] = $expr->notLike($colString, $valueParameterName); $qb->setParameter($valueParameterName, $value . '%'); break; case DatagridFilter::EQUAL: $wheres[] = $expr->eq($colString, $valueParameterName); $qb->setParameter($valueParameterName, $value); break; case DatagridFilter::NOT_EQUAL: $wheres[] = $expr->neq($colString, $valueParameterName); $qb->setParameter($valueParameterName, $value); break; case DatagridFilter::GREATER_EQUAL: $wheres[] = $expr->gte($colString, $valueParameterName); $qb->setParameter($valueParameterName, $value); break; case DatagridFilter::GREATER: $wheres[] = $expr->gt($colString, $valueParameterName); $qb->setParameter($valueParameterName, $value); break; case DatagridFilter::LESS_EQUAL: $wheres[] = $expr->lte($colString, $valueParameterName); $qb->setParameter($valueParameterName, $value); break; case DatagridFilter::LESS: $wheres[] = $expr->lt($colString, $valueParameterName); $qb->setParameter($valueParameterName, $value); break; case DatagridFilter::BETWEEN: $minParameterName = ':' . str_replace('.', '', $colString . '0'); $maxParameterName = ':' . str_replace('.', '', $colString . '1'); $wheres[] = $expr->between($colString, $minParameterName, $maxParameterName); $qb->setParameter($minParameterName, $values[0]); $qb->setParameter($maxParameterName, $values[1]); break 2; default: throw new \InvalidArgumentException('This operator is currently not supported: ' . $filter->getOperator()); break; } } if (count($wheres) > 0) { $orWhere = $qb->expr()->orX(); $orWhere->addMultiple($wheres); $qb->andWhere($orWhere); } }