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