public function testBetween()
{
$filter = new Filter();
$filter->setFromColumn($this->column, '2<>3');
$this->assertEquals(Filter::BETWEEN, $filter->getOperator());
$this->assertEquals('2 <> 3', $filter->getDisplayColumnValue());
$this->assertEquals(array('2', '3'), $filter->getValues());
$filter = new Filter();
$filter->setFromColumn($this->column, '2<>3 <>4');
$this->assertEquals(Filter::BETWEEN, $filter->getOperator());
$this->assertEquals('2 <> 4', $filter->getDisplayColumnValue());
$this->assertEquals(array('2', '4'), $filter->getValues());
}
/**
* @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 = array();
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);
}
}
public static function applyStaticFilter(DatagridFilter $filter, $colString, $adapter)
{
if (!isset(self::$where)) {
self::$where = new Where();
}
$qi = function ($name) use($adapter) {
return $adapter->getPlatform()->quoteIdentifier($name);
};
$values = $filter->getValues();
$wheres = array();
foreach ($values as $value) {
switch ($filter->getOperator()) {
case DatagridFilter::LIKE:
$wheres[] = self::$where->like($colString, '%' . $value . '%');
break;
case DatagridFilter::LIKE_LEFT:
$wheres[] = self::$where->like($colString, '%' . $value);
break;
case DatagridFilter::LIKE_RIGHT:
$wheres[] = self::$where->like($colString, $value . '%');
break;
case DatagridFilter::NOT_LIKE:
$wheres[] = self::$where->literal($qi($colString) . 'NOT LIKE ?', array('%' . $value . '%'));
break;
case DatagridFilter::NOT_LIKE_LEFT:
$wheres[] = self::$where->literal($qi($colString) . 'NOT LIKE ?', array('%' . $value));
break;
case DatagridFilter::NOT_LIKE_RIGHT:
$wheres[] = self::$where->literal($qi($colString) . 'NOT LIKE ?', array($value . '%'));
break;
case DatagridFilter::EQUAL:
$wheres[] = self::$where->equalTo($colString, $value);
break;
case DatagridFilter::NOT_EQUAL:
$wheres[] = self::$where->notEqualTo($colString, $value);
break;
case DatagridFilter::GREATER_EQUAL:
$wheres[] = self::$where->greaterThanOrEqualTo($colString, $value);
break;
case DatagridFilter::GREATER:
$wheres[] = self::$where->greaterThan($colString, $value);
break;
case DatagridFilter::LESS_EQUAL:
$wheres[] = self::$where->lessThanOrEqualTo($colString, $value);
break;
case DatagridFilter::LESS:
$wheres[] = self::$where->lessThan($colString, $value);
break;
case DatagridFilter::BETWEEN:
$wheres[] = self::$where->between($colString, $values[0], $values[1]);
break;
case DatagridFilter::IN:
$wheres[] = self::$where->in($colString, (array) $value);
break;
case DatagridFilter::NOT_IN:
$wheres[] = self::$where->notin($colString, (array) $value);
break;
default:
throw new \InvalidArgumentException('This operator is currently not supported: ' . $filter->getOperator());
break;
}
}
return self::$where;
}